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.
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.
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.
SELECT balance, version FROM accounts WHERE id = 1;
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.
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.
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.
SELECT * FROM orders WHERE id = 123 FOR UPDATE;
Wrong approach.
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.
INSERT INTO users (email) VALUES ('x@example.com') ON CONFLICT DO NOTHING;
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.
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.
SELECT pg_advisory_lock(hashtext('daily_report'));
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.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT * FROM seats WHERE available = true LIMIT 1;
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.
