Concurrency Control: Isolation Levels and Locking

Introduction

When multiple users access a database simultaneously, concurrent transactions can interfere with each other, producing incorrect results. Concurrency control is the set of mechanisms in a Database Management System that ensures concurrent transactions produce correct, consistent results. Understanding isolation levels and locking is essential for building performant, correct multi-user applications.

Explanation

Three concurrency problems can occur when isolation is insufficient:

• Dirty Read: Transaction A reads data modified by Transaction B, which then rolls back. A has read data that never officially existed.

• Non-Repeatable Read: Transaction A reads a row. Transaction B updates that row and commits. Transaction A reads the same row again and gets a different value.

• Phantom Read: Transaction A queries a range. Transaction B inserts a new row in that range and commits. Transaction A re-queries and sees an extra "phantom" row.

Real World Example

In an airline booking system, two users simultaneously try to book the last seat on a flight. Without proper isolation, both could read "1 seat available," both proceed to book, and you end up with two bookings for one seat (a phantom read or lost update problem). Proper isolation levels or explicit locking prevent this overbooking scenario.

Technical Breakdown

SQL defines four isolation levels, each preventing more concurrency problems at the cost of reduced concurrency:

Most production DBMS use Read Committed (PostgreSQL, Oracle default) or Repeatable Read (MySQL InnoDB default) as defaults. Serializable provides full isolation but drastically reduces throughput.

Modern DBMS implement isolation through MVCC (Multi-Version Concurrency Control) rather than pure locking. MVCC keeps multiple versions of rows — readers see a snapshot of data at transaction start time, so reads never block writes and writes never block reads.

Example

-- Set isolation level for a session (PostgreSQL)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
    SELECT balance FROM accounts WHERE account_id = 'ACC001';
    -- Other transactions can read but not modify this row
    SELECT balance FROM accounts WHERE account_id = 'ACC001';
    -- Returns same value as first read (repeatable)
COMMIT;

-- Explicit row locking (SELECT FOR UPDATE)
BEGIN;
    -- Lock the row to prevent concurrent modifications
    SELECT seat_id, status
    FROM flight_seats
    WHERE flight_id = 'FL123' AND status = 'available'
    LIMIT 1
    FOR UPDATE;  -- Exclusive lock on this row

    -- Only we can update this seat now
    UPDATE flight_seats SET status = 'booked', passenger_id = 999
    WHERE seat_id = [returned_seat_id];
COMMIT;

Common Mistakes

• Using Serializable isolation by default — it dramatically reduces concurrency and throughput; use it only for operations that require absolute consistency (financial calculations).

• Deadlocks from inconsistent lock acquisition order — if Transaction A locks Table1 then Table2, and Transaction B locks Table2 then Table1, they deadlock. Always acquire locks in the same order across all transactions.

• Holding locks for too long — long transactions holding locks block other transactions. Keep transactions short and commit quickly.

Interview Tips

Be ready to explain the four isolation levels and what each prevents. A practical interview question is "How would you prevent two users from booking the same seat simultaneously?" — describe using SELECT FOR UPDATE to acquire an exclusive lock before checking availability, then inserting the booking and committing. Also explain MVCC conceptually: instead of blocking readers when writers are active, the DBMS keeps old versions of rows visible to existing readers while new versions are written for new transactions.