# Notes: PostgreSQL Concurrency Handling for Developers

One of the most common issues I see regularly while reviewing code is database queries that can cause concurrency issues. They look clean and innocent but can cause wrong data during race conditions. In this blog, I’ll try to cover some important battle-tested patterns that we should keep in mind as a developer to avoid these.

### Transactions - Group Operations

Whenever you’re updating multiple things that must happen together (like transferring money between accounts), wrap them in a transaction. This makes sure that either **everything happens**, or **nothing does**, no in-between states.

```pgsql
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;
```

### Pessimistic Locking - Lock Rows When You Know There’ll Be Contention

If multiple processes might update the same row at the same time, don’t take chances. Lock it.

```pgsql
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
```

This makes the database wait until the lock is released, ensuring no two transactions change the same data simultaneously.

### Optimistic Locking - Let Users Compete, But Detect Conflicts

Add a `version` or `updated_at` field, and check it when updating.

```pgsql
-- Read
SELECT balance, version FROM accounts WHERE id = 1;

-- Update (only if version hasn’t changed)
UPDATE accounts
SET balance = 200, version = version + 1
WHERE id = 1 AND version = 3;
```

If another update sneaked in and changed the version, your update won’t go through.

### Atomic Updates - Don’t Read-Then-Write

Let’s say you want to add ₹100 to someone’s account. I have seen code like this many times.

```pgsql
SELECT balance as current_balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = current_balance + 100 WHERE id = 1;
```

This is wrong, **NEVER** do this. That’s a classic race condition waiting to happen.

Correct way to query this.

```pgsql
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
```

### Lock Specific Rows - Not the Whole Table

If you're locking rows (e.g. with `FOR UPDATE`), make sure your WHERE clause uses indexed columns like primary keys.

Right approach.

```pgsql
SELECT * FROM orders WHERE id = 123 FOR UPDATE;
```

Wrong approach.

```pgsql
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;
```

Without an index on `status`, PostgreSQL will **scan the entire table**, evaluating each row. Every matching row (`'pending'`) gets locked.

### Insert-if-not-exist with Lock or Conflict Clause

Avoid race conditions during unique inserts.

```pgsql
-- Postgresql
INSERT INTO users (email) VALUES ('x@example.com') ON CONFLICT DO NOTHING;

-- MySQL
INSERT IGNORE INTO users (email) VALUES ('x@example.com');
```

### Use Queue Tables with SKIP LOCKED for Background Jobs

When building job queues or background workers, you want to make sure no two workers grab the same job.

```pgsql
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
```

Here `SKIP LOCKED` lets other workers skip rows already locked by someone else. No waiting, no duplicate processing.

### Advisory Locks

When we are running concurrent operations like processing data from database directly, it’s good to have advisory locks in place.

PostgreSQL provides **Session Level** - *pg\_try\_advisory\_lock (non-blocking)* and *pg\_advisory\_lock (blocking)* and **Transaction Level** - *pg\_try\_advisory\_xact\_lock (non-blocking)* and *pg\_advisory\_xact\_lock (blocking)* locks.

```pgsql
SELECT pg_advisory_lock(hashtext('daily_report'));

-- run report logic...

SELECT pg_advisory_unlock(hashtext('daily_report'));
```

### Serializable Isolation Level

This is the highest isolation level which we can use in places where correctness is extremely important and we can compromise with performance as well for that.

```pgsql
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;

SELECT * FROM seats WHERE available = true LIMIT 1;
-- Do some checks or process payment
UPDATE seats SET available = false WHERE id = $seat_id;

COMMIT;
```

In this scenario, two users might both read the same seat and attempt to book it.  
With SERIALIZABLE, PostgreSQL ensures only one transaction will succeed, the other fails and must be retried.

Concurrency issues are sneaky and often hard to debug. Just follow the right patterns, and your database logic will scale cleanly, even under load.