Database Transactions and ACID: Isolation Levels, Locking and Concurrency Explained

Transactions are the foundation of data integrity in relational databases. Every serious backend developer needs to understand not just what ACID means, but what happens at each isolation level and how to reason about concurrency problems. These concepts appear in every senior backend interview.

What Is a Transaction?

A transaction is a sequence of operations treated as a single unit of work. Either all operations succeed, or none of them take effect.

sql
-- Classic bank transfer: must be atomic
BEGIN;

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

-- If anything fails between these two, we need to roll back
COMMIT;
-- or ROLLBACK if something went wrong

Without a transaction, a server crash between the two updates would leave one account debited and the other not credited — money disappears.

ACID Properties

Atomicity

All operations in a transaction succeed, or none of them do. There is no partial state.

sql
BEGIN;
  INSERT INTO orders (user_id, total) VALUES (42, 99.99);
  UPDATE inventory SET stock = stock - 1 WHERE product_id = 7;
  -- If inventory update fails, the order insert is rolled back too
COMMIT;

Consistency

A transaction takes the database from one valid state to another. All defined rules (constraints, cascades, triggers) are enforced.

sql
-- This would violate a NOT NULL constraint -- entire transaction fails
BEGIN;
  INSERT INTO users (name, email) VALUES (NULL, 'alice@example.com');
COMMIT;
-- ERROR: null value in column "name" -- transaction rolled back

Isolation

Concurrent transactions behave as if they ran serially — changes made within a transaction are not visible to others until committed (at most isolation levels).

Durability

Once a transaction commits, it persists even if the system crashes immediately after. Achieved via write-ahead logging (WAL).

Concurrency Problems

These are the problems that isolation levels protect against:

Dirty Read

Reading uncommitted data from another transaction.

code
Transaction A                    Transaction B
BEGIN;
UPDATE accounts
  SET balance = 1000
  WHERE id = 1;
                                 BEGIN;
                                 SELECT balance FROM accounts
                                   WHERE id = 1;
                                 -- reads 1000 (uncommitted!)
                                 -- Transaction A might roll back
ROLLBACK;                        COMMIT; -- worked with phantom data

Non-Repeatable Read

Reading the same row twice within a transaction and getting different values because another transaction committed a change in between.

code
Transaction A                    Transaction B
BEGIN;
SELECT balance FROM accounts
  WHERE id = 1;  -- returns 500

                                 BEGIN;
                                 UPDATE accounts SET balance = 200
                                   WHERE id = 1;
                                 COMMIT;

SELECT balance FROM accounts
  WHERE id = 1;  -- returns 200 (changed!)
COMMIT;

Phantom Read

A query returns different rows on repeated execution within a transaction because another transaction inserted or deleted matching rows.

code
Transaction A                    Transaction B
BEGIN;
SELECT COUNT(*) FROM orders
  WHERE status = 'pending';  -- returns 5

                                 BEGIN;
                                 INSERT INTO orders (status)
                                   VALUES ('pending');
                                 COMMIT;

SELECT COUNT(*) FROM orders
  WHERE status = 'pending';  -- returns 6 (phantom row!)
COMMIT;

Isolation Levels

SQL defines four isolation levels, each preventing a different set of anomalies:

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible*
SERIALIZABLE	Prevented	Prevented	Prevented

*PostgreSQL's REPEATABLE READ also prevents phantom reads due to its MVCC implementation.

Setting isolation level in PostgreSQL

sql
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- or
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- or
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

READ COMMITTED (PostgreSQL default)

Each statement within the transaction sees a snapshot of all data committed before that statement began. Different statements in the same transaction can see different data.

sql
-- PostgreSQL default
BEGIN; -- READ COMMITTED
SELECT balance FROM accounts WHERE id = 1; -- 500
-- (another transaction commits balance = 200 here)
SELECT balance FROM accounts WHERE id = 1; -- 200 (sees new data)
COMMIT;

REPEATABLE READ

The transaction sees a snapshot of all data as it was when the transaction began. Same query always returns same result within the transaction.

sql
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- 500
-- (another transaction commits balance = 200 here)
SELECT balance FROM accounts WHERE id = 1; -- still 500 (snapshot)
COMMIT;

SERIALIZABLE

The strongest isolation. Transactions execute as if serialized one after another. PostgreSQL uses SSI (Serializable Snapshot Isolation) — it detects when concurrent transactions would have conflicting results and aborts one.

sql
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Complex read-modify-write operations are safe here
-- But: be prepared to retry on serialization failures
-- SQLSTATE 40001: could not serialize access due to concurrent update

Locking

Pessimistic Locking

Acquire a lock before reading data you plan to modify — prevent others from changing it:

sql
BEGIN;

-- Lock the row for update (other transactions block on this row)
SELECT * FROM accounts
WHERE id = 1
FOR UPDATE;

-- Now safe to read and modify -- no other transaction can touch this row
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

COMMIT;

FOR UPDATE acquires a row-level exclusive lock. FOR SHARE acquires a shared lock (others can read, none can modify).

sql
-- Skip locked rows (useful for job queues)
SELECT * FROM tasks
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Grabs the next available task without blocking on locked ones

Optimistic Locking

Assume conflicts are rare. Read data, make changes, then verify nothing changed before committing — using a version column:

sql
-- Schema: add version column
ALTER TABLE products ADD COLUMN version INTEGER DEFAULT 0;

-- Application reads product
SELECT id, name, stock, version FROM products WHERE id = 7;
-- Returns: id=7, stock=10, version=5

-- Application tries to update
UPDATE products
SET stock = 9, version = version + 1
WHERE id = 7 AND version = 5;
-- Returns: 1 row updated (success) or 0 rows updated (conflict -- someone else changed it)

-- In application code:
IF affected_rows == 0 THEN
  -- Conflict: retry or show error
END IF;

Optimistic locking works well when conflicts are rare and the cost of retrying is low. Use pessimistic locking when conflicts are common or the cost of retrying is high.

Deadlocks

A deadlock occurs when two transactions each wait for the other to release a lock:

code
Transaction A                    Transaction B
BEGIN;                           BEGIN;
UPDATE accounts                  UPDATE accounts
  SET balance = balance - 100      SET balance = balance - 200
  WHERE id = 1; -- locks row 1    WHERE id = 2; -- locks row 2

UPDATE accounts                  UPDATE accounts
  SET balance = balance + 100      SET balance = balance + 200
  WHERE id = 2; -- waits for B     WHERE id = 1; -- waits for A
  -- DEADLOCK

PostgreSQL detects deadlocks and automatically aborts one transaction with: ERROR: deadlock detected

Prevention strategies:

• Always acquire locks in the same order (always lock lower ID first)
• Keep transactions short — hold locks for the minimum time
• Use SELECT ... FOR UPDATE NOWAIT to fail immediately if lock is unavailable

Savepoints

Partial rollback within a transaction:

sql
BEGIN;
  INSERT INTO orders (user_id, total) VALUES (42, 99.99);

  SAVEPOINT before_inventory;

  UPDATE inventory SET stock = stock - 1 WHERE product_id = 7;

  -- If inventory update fails:
  ROLLBACK TO SAVEPOINT before_inventory;

  -- Order is still inserted, inventory update is rolled back
  -- Can try a different approach or log the failure

COMMIT;

Common Interview Questions

Q: What does ACID stand for and which property is hardest to achieve in distributed systems?

Atomicity (all or nothing), Consistency (valid state transitions), Isolation (concurrent transactions appear serial), Durability (committed data persists). Consistency in distributed systems (as in CAP theorem) is the hardest — maintaining it across multiple nodes during network partitions requires coordination that impacts availability.

Q: What is the difference between REPEATABLE READ and SERIALIZABLE?

REPEATABLE READ guarantees that the same row read twice within a transaction returns the same value. It does not prevent phantom reads (new rows matching a query appearing). SERIALIZABLE prevents all anomalies including phantoms — concurrent transactions produce the same results as if they ran sequentially. Serializable has higher overhead and may produce serialization failures that require retries.

Q: When would you choose optimistic over pessimistic locking?

Use optimistic locking when conflicts are rare (most reads/writes target different rows), the data is mostly read, and retry cost is low. Use pessimistic locking when conflicts are common (many users editing the same rows), the operation is complex and expensive to retry, or you need to guarantee a specific outcome without retries.

Practice SQL on Froquiz

Transactions and concurrency are tested in senior backend and database interviews. Test your SQL knowledge on Froquiz — from basic queries to advanced concurrency topics.

Summary

• Transactions ensure Atomicity, Consistency, Isolation, and Durability (ACID)
• Concurrency anomalies: dirty reads, non-repeatable reads, phantom reads — each prevented at higher isolation levels
• READ COMMITTED (default in PostgreSQL) prevents dirty reads; REPEATABLE READ prevents non-repeatable reads; SERIALIZABLE prevents all anomalies
• SELECT ... FOR UPDATE acquires pessimistic locks; version columns implement optimistic locking
• FOR UPDATE SKIP LOCKED enables high-throughput job queues without blocking
• Deadlocks are detected automatically — prevent them by always acquiring locks in consistent order
• Savepoints allow partial rollback within a transaction