Databases (3): Transactions and Concurrency — ACID, Isolation Levels, and Locking

Every application that handles money, inventory, or any state that matters eventually hits a concurrency bug. Two users buy the last item in stock. A bank transfer debits one account but crashes before crediting the other. A report reads half-updated data and produces nonsense numbers. Transactions exist to prevent these failures, and understanding how they work is non-negotiable for anyone building production systems.

What Is a Transaction?#

A transaction is a group of operations that the database treats as a single unit. Either all operations succeed, or none of them do.

1
2
3
4
BEGIN;
    UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;

If the server crashes between the two UPDATE statements, the transaction is rolled back. Account 1 does not lose $500 without Account 2 gaining it. This is the fundamental guarantee.

ACID: The Four Guarantees#

ACID is not just an acronym you memorize for interviews. Each letter represents a specific guarantee, and understanding what breaks without each one is more important than the definition itself.

Atomicity — All or Nothing#

Definition: A transaction either completes fully or has no effect at all.

What breaks without it:

1
2
3
4
5
-- Without atomicity: server crashes between these two statements
UPDATE inventory SET stock = stock - 1 WHERE product_id = 42;
-- CRASH HERE
INSERT INTO order_items (order_id, product_id, quantity) VALUES (101, 42, 1);
-- Stock decreased but order item never created. Inventory leak.

Atomicity means the database uses a write-ahead log (WAL) to record changes before applying them. On crash recovery, incomplete transactions are rolled back.

Consistency — Valid State to Valid State#

Definition: A transaction moves the database from one valid state to another. All constraints (foreign keys, CHECK, UNIQUE, NOT NULL) are enforced.

What breaks without it:

1
2
3
4
-- Without consistency enforcement:
INSERT INTO orders (order_id, user_id) VALUES (999, 12345);
-- user_id 12345 does not exist in the users table
-- Now we have an orphaned order with no associated user

The foreign key constraint REFERENCES users(user_id) prevents this. Consistency also includes application-level invariants — for example, “the sum of all account balances must remain constant during a transfer.”

Isolation — Concurrent Transactions Don’t Interfere#

Definition: Concurrently executing transactions produce the same result as if they ran sequentially.

What breaks without it:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- Transaction A: reads account balance
SELECT balance FROM accounts WHERE account_id = 1;  -- Returns 1000

-- Transaction B: withdraws 500 and commits
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
COMMIT;

-- Transaction A: reads again (same transaction)
SELECT balance FROM accounts WHERE account_id = 1;  -- Returns 500!
-- The balance changed mid-transaction. A's view of the world is inconsistent.

This is a non-repeatable read. Isolation levels control which anomalies are allowed.

Durability — Committed Means Permanent#

Definition: Once a transaction commits, its changes survive any subsequent crash (power failure, OS crash, hardware failure).

What breaks without it: You commit a bank transfer, see “Success” on screen, and then the server reboots. The transfer is gone. The database reverted to a state before the commit because changes were only in memory.

Durability is enforced by flushing the WAL to persistent storage before reporting a commit as successful. The actual data pages may still be in memory (dirty pages), but the WAL contains enough information to reconstruct them after a crash.

Transaction Lifecycle#

Mvcc timeline visualization parallel universes of database s

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Start a transaction
BEGIN;
-- or: START TRANSACTION;

-- Do work
INSERT INTO orders (user_id, status) VALUES (1, 'pending');
UPDATE inventory SET stock = stock - 1 WHERE product_id = 42;

-- Create a savepoint (partial rollback target)
SAVEPOINT after_inventory;

-- More work
INSERT INTO shipping (order_id, address) VALUES (currval('orders_order_id_seq'), '123 Main St');

-- Oops, wrong address. Roll back to savepoint.
ROLLBACK TO SAVEPOINT after_inventory;

-- Fix and retry
INSERT INTO shipping (order_id, address) VALUES (currval('orders_order_id_seq'), '456 Oak Ave');

-- Commit everything
COMMIT;

Command	Effect
`BEGIN`	Start a new transaction
`COMMIT`	Make all changes permanent
`ROLLBACK`	Undo all changes since `BEGIN`
`SAVEPOINT name`	Create a named checkpoint within the transaction
`ROLLBACK TO SAVEPOINT name`	Undo changes back to the savepoint (transaction continues)
`RELEASE SAVEPOINT name`	Remove the savepoint (changes kept)

In autocommit mode (the default in most databases), every statement is its own transaction. An explicit BEGIN starts a multi-statement transaction.

Isolation Levels#

Database transaction locks as golden padlocks on digital vau

The SQL standard defines four isolation levels, each allowing different concurrency anomalies. Stronger isolation = fewer anomalies but lower throughput.

The Three Anomalies#

Before we look at isolation levels, let us define the anomalies precisely.

Dirty Read: Transaction A reads data written by Transaction B before B commits. If B rolls back, A has read data that never officially existed.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
-- Session 1                          -- Session 2
BEGIN;
UPDATE products SET price = 0.01
WHERE product_id = 1;
                                      BEGIN;
                                      -- Dirty read: sees price = 0.01
                                      SELECT price FROM products
                                      WHERE product_id = 1;
ROLLBACK;  -- price reverts to 49.99
                                      -- Session 2 used price 0.01 for
                                      -- a decision, but that price
                                      -- never existed.
                                      COMMIT;

Non-Repeatable Read: Transaction A reads a row, Transaction B modifies and commits that row, then A reads the same row again and gets different data.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
-- Session 1                          -- Session 2
BEGIN;
SELECT balance FROM accounts
WHERE id = 1;  -- Returns 1000
                                      BEGIN;
                                      UPDATE accounts SET balance = 500
                                      WHERE id = 1;
                                      COMMIT;
SELECT balance FROM accounts
WHERE id = 1;  -- Returns 500!
-- Same query, different result within
-- the same transaction.
COMMIT;

Phantom Read: Transaction A runs a query with a range condition, Transaction B inserts a new row that matches, then A re-runs the query and sees a new “phantom” row.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- Session 1                          -- Session 2
BEGIN;
SELECT COUNT(*) FROM orders
WHERE status = 'pending';  -- Returns 5
                                      BEGIN;
                                      INSERT INTO orders (user_id, status)
                                      VALUES (99, 'pending');
                                      COMMIT;
SELECT COUNT(*) FROM orders
WHERE status = 'pending';  -- Returns 6!
-- A new row appeared (phantom).
COMMIT;

Isolation Level Matrix#

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read	Performance
READ UNCOMMITTED	Possible	Possible	Possible	Fastest
READ COMMITTED	Prevented	Possible	Possible	Fast
REPEATABLE READ	Prevented	Prevented	Possible*	Moderate
SERIALIZABLE	Prevented	Prevented	Prevented	Slowest

*In PostgreSQL, REPEATABLE READ also prevents phantom reads (it uses snapshot isolation, which is stronger than the SQL standard requires).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- Set isolation level for a transaction
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... your queries ...
COMMIT;

-- Set default isolation level for the session (PostgreSQL)
SET default_transaction_isolation = 'read committed';

-- Check current isolation level (PostgreSQL)
SHOW default_transaction_isolation;

-- MySQL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@transaction_isolation;

What Should You Use?#

Use Case	Recommended Level
Most web applications	READ COMMITTED (PostgreSQL default)
Financial transactions	SERIALIZABLE or REPEATABLE READ
Reporting / analytics	REPEATABLE READ (consistent snapshot)
Best effort / monitoring	READ UNCOMMITTED (only if you really need it)

PostgreSQL defaults to READ COMMITTED. MySQL (InnoDB) defaults to REPEATABLE READ. Both are reasonable defaults for most applications.

MVCC: How Databases Implement Isolation Efficiently#

Multi-Version Concurrency Control (MVCC) is the mechanism that makes isolation levels practical. Instead of blocking readers when writers are active (which kills performance), MVCC keeps multiple versions of each row.

PostgreSQL MVCC#

In PostgreSQL, each row has hidden system columns:

xmin — the transaction ID that created (inserted) this row version
xmax — the transaction ID that deleted/updated this row version (0 if still live)

When you UPDATE a row, PostgreSQL does not modify it in place. Instead:

Marks the old row version as expired (xmax = current transaction ID)
Creates a new row version (xmin = current transaction ID)

Both versions coexist. Each transaction sees only the version that was “alive” at its snapshot time.

1
2
3
4
5
6
7
8
-- Transaction 100 inserts a row
INSERT INTO accounts (id, balance) VALUES (1, 1000);
-- Row: xmin=100, xmax=0, balance=1000

-- Transaction 200 updates the row
UPDATE accounts SET balance = 500 WHERE id = 1;
-- Old row: xmin=100, xmax=200, balance=1000  (still visible to old snapshots)
-- New row: xmin=200, xmax=0,   balance=500   (visible to new snapshots)

This is why PostgreSQL needs VACUUM — dead row versions accumulate and must be cleaned up.

MySQL InnoDB MVCC#

InnoDB uses a different approach:

Each row has a hidden 6-byte transaction ID and a 7-byte roll pointer
The roll pointer points to an undo log entry containing the previous version
Multiple undo log entries form a chain for each row

To reconstruct an old version, InnoDB walks the undo log chain backwards. This means old versions do not consume extra space in the main table, but long-running transactions force InnoDB to keep long undo log chains.

MVCC Implications#

Behavior	PostgreSQL	MySQL InnoDB
Readers block writers	No	No
Writers block readers	No	No
Writers block writers	Yes (same row)	Yes (same row)
Dead version cleanup	VACUUM (manual/auto)	Purge thread (automatic)
Long transaction cost	Table bloat	Long undo log chains

The key insight: reads never block writes, and writes never block reads. This is why modern databases can handle thousands of concurrent connections without everything grinding to a halt.

Locking#

Despite MVCC, databases still need locks when multiple transactions write to the same data.

Row-Level Locks#

1
2
3
4
5
6
7
-- SELECT FOR UPDATE acquires a row lock
-- Other transactions trying to update the same row will wait
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Row is now locked. Other transactions wait if they try to modify it.
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- Lock released

1
2
3
4
5
6
-- SELECT FOR SHARE (read lock)
-- Other transactions can read but not modify
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- Row is read-locked. Others can read it but not UPDATE/DELETE.
COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
-- SKIP LOCKED: non-blocking queue pattern
-- Great for job queues — workers grab unlocked tasks
BEGIN;
SELECT * FROM tasks
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- If a row is locked by another worker, skip it and get the next one

UPDATE tasks SET status = 'processing', worker_id = 'worker-3'
WHERE task_id = ...;  -- the ID from the SELECT above
COMMIT;

1
2
3
4
-- NOWAIT: fail immediately instead of waiting
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- If locked, immediately raises: ERROR: could not obtain lock on row

Table-Level Locks#

Table locks are rare in OLTP but appear in DDL operations:

1
2
3
4
5
-- Explicit table lock (PostgreSQL)
LOCK TABLE accounts IN EXCLUSIVE MODE;

-- ACCESS EXCLUSIVE: blocks everything, required for DROP TABLE, ALTER TABLE
-- ACCESS SHARE: compatible with everything except ACCESS EXCLUSIVE

PostgreSQL lock modes (from weakest to strongest):

Lock Mode	Conflicts With
ACCESS SHARE	ACCESS EXCLUSIVE
ROW SHARE	EXCLUSIVE, ACCESS EXCLUSIVE
ROW EXCLUSIVE	SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARE UPDATE EXCLUSIVE	SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARE	ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARE ROW EXCLUSIVE	ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
EXCLUSIVE	ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
ACCESS EXCLUSIVE	All lock modes

Advisory Locks#

Application-level locks using the database as a coordination point:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- Acquire an advisory lock (PostgreSQL)
-- The lock number is arbitrary — your application defines the meaning
SELECT pg_advisory_lock(12345);
-- ... do work that needs exclusive access ...
SELECT pg_advisory_unlock(12345);

-- Try to acquire without blocking
SELECT pg_try_advisory_lock(12345);  -- Returns true/false

-- Session-level advisory locks (released when session ends)
SELECT pg_advisory_lock(hashtext('process_daily_report'));

Use cases: cron job coordination (only one instance runs), rate limiting, distributed locking without Redis.

Deadlocks#

A deadlock occurs when two transactions each hold a lock that the other needs.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
-- Transaction A                      -- Transaction B
BEGIN;                                BEGIN;
UPDATE accounts SET balance = 900
WHERE id = 1;  -- Locks row 1
                                      UPDATE accounts SET balance = 1100
                                      WHERE id = 2;  -- Locks row 2

UPDATE accounts SET balance = 1100
WHERE id = 2;  -- WAITS for B's lock
                                      UPDATE accounts SET balance = 900
                                      WHERE id = 1;  -- WAITS for A's lock

-- DEADLOCK! Both transactions are waiting for each other.

How Databases Handle Deadlocks#

Databases detect deadlocks using a wait-for graph. When a cycle is detected, one transaction is chosen as the victim and rolled back:

1
2
3
4
5
6
ERROR:  deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890;
        blocked by process 67891.
        Process 67891 waits for ShareLock on transaction 12345;
        blocked by process 12345.
HINT:   See server log for query details.

Deadlock Prevention Strategies#

Always lock resources in the same order: If every transaction that needs accounts 1 and 2 always locks account 1 first, deadlocks cannot occur.

1
2
3
4
5
6
7
-- Always lock the lower account_id first
BEGIN;
SELECT * FROM accounts WHERE id = LEAST(1, 2) FOR UPDATE;
SELECT * FROM accounts WHERE id = GREATEST(1, 2) FOR UPDATE;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

Keep transactions short: The longer a transaction holds locks, the higher the chance of deadlock.
Use NOWAIT or lock timeouts:

1
2
3
4
5
-- PostgreSQL: fail fast on lock contention
SET lock_timeout = '5s';
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- If locked for > 5 seconds, abort instead of waiting forever

Reduce lock scope: Lock only what you need, when you need it.

Optimistic vs Pessimistic Concurrency#

Two fundamentally different approaches to handling concurrent modifications:

Pessimistic Locking#

Lock the data before modifying it. This is what SELECT FOR UPDATE does.

1
2
3
4
5
6
-- Pessimistic: lock the row first
BEGIN;
SELECT * FROM products WHERE product_id = 42 FOR UPDATE;
-- Check stock, calculate, etc.
UPDATE products SET stock = stock - 1 WHERE product_id = 42;
COMMIT;

Pros: Simple, guaranteed correctness. Cons: Reduces throughput, risk of deadlocks, does not work across distributed systems.

Optimistic Locking#

Do not lock anything. Instead, detect conflicts at commit time using a version number or timestamp.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- Add a version column
ALTER TABLE products ADD COLUMN version INT NOT NULL DEFAULT 1;

-- Read the current state (no lock)
SELECT product_id, stock, version FROM products WHERE product_id = 42;
-- Returns: stock = 10, version = 5

-- Application does its computation...

-- Update with version check
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE product_id = 42 AND version = 5;
-- If affected rows = 0, someone else modified the row. Retry.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# Application-side optimistic locking pattern
def purchase_product(product_id: int, quantity: int):
    max_retries = 3
    for attempt in range(max_retries):
        # Read current state
        product = db.query(
            "SELECT stock, version FROM products WHERE product_id = %s",
            [product_id]
        )

        if product.stock < quantity:
            raise InsufficientStockError()

        # Try to update with version check
        rows_affected = db.execute(
            """UPDATE products
               SET stock = stock - %s, version = version + 1
               WHERE product_id = %s AND version = %s""",
            [quantity, product_id, product.version]
        )

        if rows_affected == 1:
            return  # Success
        # Version mismatch — retry
    raise ConflictError("Too many retries")

Pros: Higher throughput when conflicts are rare, works across distributed systems. Cons: Must handle retries, more complex application logic.

Criterion	Pessimistic	Optimistic
Conflict frequency	High — locks prevent conflicts	Low — detects conflicts after the fact
Throughput	Lower (waiting for locks)	Higher (no waiting)
Complexity	Simple SQL	Application must handle retries
Deadlock risk	Yes	No
Best for	High-contention data (inventory, balances)	Low-contention data (user profiles, settings)

Savepoints: Partial Rollback#

Savepoints let you undo part of a transaction without aborting the whole thing. Essential for complex workflows where one step might fail but others should persist.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
BEGIN;

INSERT INTO orders (user_id, total) VALUES (1, 99.99);
-- order_id = 1001 assigned

SAVEPOINT before_items;

INSERT INTO order_items (order_id, product_id, qty) VALUES (1001, 42, 1);
INSERT INTO order_items (order_id, product_id, qty) VALUES (1001, 99, 1);
-- Oops, product 99 is out of stock (foreign key fails or business logic check)

ROLLBACK TO SAVEPOINT before_items;
-- order still exists, items are gone

-- Try again with just the available product
INSERT INTO order_items (order_id, product_id, qty) VALUES (1001, 42, 1);

COMMIT;  -- order with 1 item committed successfully

Savepoints in Application Code#

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
async def create_order_with_fallback(conn, user_id: int, items: list[dict]):
    async with conn.transaction():
        order_id = await conn.fetchval(
            "INSERT INTO orders (user_id) VALUES ($1) RETURNING order_id", user_id
        )

        for item in items:
            try:
                async with conn.transaction():  # creates savepoint
                    await conn.execute(
                        "INSERT INTO order_items (order_id, product_id, qty) "
                        "VALUES ($1, $2, $3)",
                        order_id, item["product_id"], item["qty"]
                    )
            except Exception:
                # This item failed, but the outer transaction continues
                pass

        # Commit whatever items succeeded

In asyncpg/psycopg3, nested async with conn.transaction() automatically creates savepoints.

Advisory Locks: Application-Level Coordination#

Advisory locks are database-managed locks that don’t lock any table or row — they lock an arbitrary application-defined resource. The database just provides the coordination mechanism.

1
2
3
4
5
6
7
8
-- Acquire an exclusive advisory lock (blocks if already held)
SELECT pg_advisory_lock(12345);

-- Try without blocking (returns true/false)
SELECT pg_try_advisory_lock(12345);

-- Release
SELECT pg_advisory_unlock(12345);

Use Cases#

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# Prevent duplicate cron job execution
async def run_scheduled_task(conn, task_id: int):
    acquired = await conn.fetchval(
        "SELECT pg_try_advisory_lock($1)", task_id
    )
    if not acquired:
        return  # another instance is already running

    try:
        await do_expensive_work()
    finally:
        await conn.execute("SELECT pg_advisory_unlock($1)", task_id)

# Prevent race condition in "get or create" pattern
async def get_or_create_user(conn, email: str):
    lock_key = hash(email) % (2**31)  # advisory lock takes bigint
    await conn.execute("SELECT pg_advisory_lock($1)", lock_key)
    try:
        user = await conn.fetchrow("SELECT * FROM users WHERE email = $1", email)
        if user is None:
            user = await conn.fetchrow(
                "INSERT INTO users (email) VALUES ($1) RETURNING *", email
            )
        return user
    finally:
        await conn.execute("SELECT pg_advisory_unlock($1)", lock_key)

Advisory Locks vs Row Locks vs Application Locks#

Feature	Row locks (SELECT FOR UPDATE)	Advisory locks	Redis/external locks
Scope	Single row	Arbitrary resource	Arbitrary resource
Auto-release	On COMMIT/ROLLBACK	On session end (or explicit)	On TTL expiry
Cross-table	No	Yes	Yes
Cross-database	No	No	Yes
Deadlock detection	Yes (database handles)	Yes (database handles)	No (need TTL)
Performance	Fast	Fast	Network round-trip

Serializable Snapshot Isolation (SSI)#

The SERIALIZABLE isolation level in PostgreSQL uses SSI — a clever algorithm that detects potential serialization anomalies without blocking reads.

How SSI Works#

1
2
3
4
5
6
7
8
9
Normal MVCC (Read Committed / Repeatable Read):
  - Readers never block writers
  - Writers never block readers
  - But anomalies are possible (write skew, phantom reads)

SSI adds:
  - Track read dependencies between transactions
  - If a cycle is detected → abort one transaction
  - Still non-blocking! (optimistic approach)

Write Skew Example#

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- Doctor on-call scheduling: at least one doctor must be on call
-- Two doctors try to go off-call simultaneously

-- Transaction A (Dr. Smith):
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call = true;  -- sees 2
UPDATE doctors SET on_call = false WHERE name = 'Smith';
COMMIT;  -- succeeds

-- Transaction B (Dr. Jones), running concurrently:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call = true;  -- sees 2
UPDATE doctors SET on_call = false WHERE name = 'Jones';
COMMIT;  -- SERIALIZATION FAILURE! Rolled back.

Under Read Committed, both transactions would succeed — leaving zero doctors on call. SSI detects the dependency cycle and aborts one transaction.

When to Use SERIALIZABLE#

Use case	Recommended level
Financial transfers	SERIALIZABLE (prevents double-spend)
Inventory management	SERIALIZABLE or explicit locking
Read-heavy analytics	READ COMMITTED (performance)
General CRUD	READ COMMITTED (default, sufficient)
Booking/reservation	SERIALIZABLE (prevents overbooking)

The cost: ~5-10% more aborted transactions that your application must retry. In exchange, you get correctness without explicit locking.

Real Example: Concurrent Bank Transfer#

Let us put it all together with a realistic bank transfer scenario.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- The safe transfer function (PostgreSQL)
CREATE OR REPLACE FUNCTION transfer(
    from_account INT,
    to_account INT,
    amount DECIMAL(12, 2)
) RETURNS VOID AS $$
DECLARE
    from_balance DECIMAL(12, 2);
BEGIN
    -- Lock both accounts in consistent order (lowest ID first)
    -- This prevents deadlocks
    IF from_account < to_account THEN
        PERFORM 1 FROM accounts WHERE account_id = from_account FOR UPDATE;
        PERFORM 1 FROM accounts WHERE account_id = to_account FOR UPDATE;
    ELSE
        PERFORM 1 FROM accounts WHERE account_id = to_account FOR UPDATE;
        PERFORM 1 FROM accounts WHERE account_id = from_account FOR UPDATE;
    END IF;

    -- Check sufficient funds
    SELECT balance INTO from_balance
    FROM accounts WHERE account_id = from_account;

    IF from_balance < amount THEN
        RAISE EXCEPTION 'Insufficient funds: balance=%, amount=%',
            from_balance, amount;
    END IF;

    -- Perform transfer
    UPDATE accounts SET balance = balance - amount
    WHERE account_id = from_account;

    UPDATE accounts SET balance = balance + amount
    WHERE account_id = to_account;

    -- Log the transfer
    INSERT INTO transfer_log (from_account, to_account, amount, transferred_at)
    VALUES (from_account, to_account, amount, NOW());
END;
$$ LANGUAGE plpgsql;

1
2
3
4
-- Usage
BEGIN;
SELECT transfer(1, 2, 500.00);
COMMIT;

This function:

Locks accounts in consistent order (prevents deadlocks)
Checks sufficient funds after locking (prevents race condition)
Performs both updates in a single transaction (atomicity)
Logs the transfer for audit trail
Runs at the default isolation level (READ COMMITTED is sufficient here because we hold explicit row locks)

Monitoring Lock Contention#

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- PostgreSQL: view current locks
SELECT
    l.pid,
    a.usename,
    l.locktype,
    l.relation::regclass AS table_name,
    l.mode,
    l.granted,
    a.query,
    age(now(), a.query_start) AS query_age
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
ORDER BY a.query_start;

-- Find blocking queries
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    age(now(), blocked.query_start) AS waiting_time
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.relation = blocking_locks.relation
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- MySQL: view InnoDB lock waits
SELECT
    r.trx_id AS waiting_trx,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

What’s Next#

Transactions guarantee correctness at the logical level. But how does the database actually store data on disk? How does a COMMIT survive a power failure? In the next article, we will go a level deeper and explore storage engines — the machinery that turns SQL into bytes on disk.

Databases (3): Transactions and Concurrency — ACID, Isolation Levels, and Locking

What Is a Transaction?#

ACID: The Four Guarantees#

Atomicity — All or Nothing#

Consistency — Valid State to Valid State#

Isolation — Concurrent Transactions Don’t Interfere#

Durability — Committed Means Permanent#

Transaction Lifecycle#

Isolation Levels#

The Three Anomalies#

Isolation Level Matrix#

What Should You Use?#

MVCC: How Databases Implement Isolation Efficiently#

PostgreSQL MVCC#

MySQL InnoDB MVCC#

MVCC Implications#

Locking#

Row-Level Locks#

Table-Level Locks#

Advisory Locks#

Deadlocks#

How Databases Handle Deadlocks#

Deadlock Prevention Strategies#

Optimistic vs Pessimistic Concurrency#

Pessimistic Locking#

Optimistic Locking#

Savepoints: Partial Rollback#

Savepoints in Application Code#

Advisory Locks: Application-Level Coordination#

Use Cases#

Advisory Locks vs Row Locks vs Application Locks#

Serializable Snapshot Isolation (SSI)#

How SSI Works#

Write Skew Example#

When to Use SERIALIZABLE#

Real Example: Concurrent Bank Transfer#

Monitoring Lock Contention#

What’s Next#

Databases 8 parts

Liked this piece?