Concurrency Control and Locking in DBMS Interview Questions

• Dirty read: reading an uncommitted write that then rolls back.
• Lost update: two transactions read and write back one value; one write is lost.
• Non-repeatable read: a row read twice changes in between.
• Phantom read: a repeated query returns newly inserted rows.
• Write skew: two transactions read overlapping data and update different rows, together breaking a rule (two doctors both go off call).

The standard's isolation levels cover only dirty, non-repeatable and phantom reads. PostgreSQL's snapshot isolation stops lost updates, MySQL's repeatable read does not, and write skew survives snapshot isolation everywhere.

The standard's four levels, by the anomalies they allow:

Serializable also rules out write skew. Defaults: read committed in PostgreSQL, Oracle and SQL Server; repeatable read in MySQL InnoDB. PostgreSQL's repeatable read and Oracle's serializable are snapshot isolation (no phantoms, but write skew), and PostgreSQL never allows dirty reads (see the PostgreSQL isolation docs). SQLite has one writer at a time, so it is serializable.

A shared (S) lock is for reading and many transactions can hold it at once; an exclusive (X) lock is for writing and conflicts with every other lock. Two transactions that each hold S and upgrade to X deadlock, which is why SELECT ... FOR UPDATE takes X up front. Under MVCC (PostgreSQL, InnoDB, Oracle), plain reads take no S locks and read a snapshot, except in InnoDB at serializable with autocommit off. SQLite locks the whole file; see WAL mode and concurrency.

Under 2PL a transaction takes all its locks before releasing any (a growing, then a shrinking phase), which makes every schedule conflict serializable. Basic 2PL may release an X lock before commit, so others read a value that can still roll back (cascading aborts). Strict 2PL holds X locks until the end; lock-based databases use it or rigorous 2PL, which holds all locks. Conservative 2PL takes every lock up front and cannot deadlock; the others can. It is not two-phase commit, which commits across nodes.

A deadlock is transactions each holding a lock another needs: T1 holds A and waits for B, T2 holds B and waits for A. It needs mutual exclusion, hold and wait, no preemption and circular wait. Databases find the cycle in the wait-for graph and roll back one victim. The search is a depth-first search:

waits_for = {"T1": ["T2"], "T2": ["T3"], "T3": ["T1"], "T4": ["T1"]}

def find_cycle(graph):
    state = {}  # 1 = on the current path, 2 = done
    path = []

    def dfs(node):
        state[node] = 1
        path.append(node)
        for nxt in graph.get(node, []):
            if state.get(nxt) == 1:
                return path[path.index(nxt):] + [nxt]
            if nxt not in state:
                found = dfs(nxt)
                if found:
                    return found
        state[node] = 2
        path.pop()
        return None

    for node in graph:
        if node not in state:
            found = dfs(node)
            if found:
                return found
    return None

print("deadlock:", " -> ".join(find_cycle(waits_for)))

T4 waits but is outside the cycle. Lock rows in a consistent order, keep transactions short, and retry victims.

Both give each transaction a start timestamp and let waits go in only one direction of age, so no cycle forms. In wait-die, an older requester waits and a younger one dies (rolls back). In wound-wait, an older requester wounds (rolls back) the younger holder, and a younger requester waits. Either way the younger one is rolled back; it restarts with its original timestamp, so it ages into a winner and never starves. Wound-wait usually causes fewer rollbacks.

Conflicting operations must run in the order of the transactions' start timestamps; there are no locks, so no deadlocks. Each item keeps R-ts(X) and W-ts(X), the largest timestamps that read and wrote it. T may read X only if ts(T) >= W-ts(X), and write it only if ts(T) is at least both; otherwise T rolls back and restarts with a new timestamp. Thomas's write rule ignores an obsolete write (ts(T) < W-ts(X) but ts(T) >= R-ts(X)) instead of rolling back, which allows schedules that are view serializable but not conflict serializable.

It prints 0 | 7 | 2. A's update matches version = 1, sets qty to 7 and bumps the version to 2; B's still asks for version 1, matches nothing, and changes() (the affected row count) is 0. That is optimistic locking: no lock is held while the user edits, and a zero count tells the app to reload and retry. ORMs build it in (JPA @Version). When conflicts are frequent, lock instead with SELECT ... FOR UPDATE.

Read and write in one conditional statement, so there is no gap for another request to slip into:

CREATE TABLE stock (item TEXT PRIMARY KEY, qty INTEGER NOT NULL);
INSERT INTO stock VALUES ('ticket', 1);

-- request 1
UPDATE stock SET qty = qty - 1 WHERE item = 'ticket' AND qty >= 1;
-- request 2 (same statement)
UPDATE stock SET qty = qty - 1 WHERE item = 'ticket' AND qty >= 1;
SELECT changes() AS rows_updated_by_request_2, qty FROM stock;

The second update finds qty >= 1 false and changes 0 rows, so the app knows the ticket is gone. In PostgreSQL, MySQL and SQL Server the second update waits for the first's row lock and re-checks the committed value, so this is safe at read committed. Otherwise use SELECT ... FOR UPDATE or a version column.

MVCC keeps several versions of each row. An update writes a new version tagged with its transaction id, and a read takes the newest version committed before its snapshot (taken per statement at read committed, per transaction at repeatable read). Readers and writers never block each other; two writers of one row still do. PostgreSQL keeps old versions in the table for VACUUM to remove; InnoDB and Oracle rebuild them from the undo log. The cost is bloat, worst with long transactions that pin old versions.

Granularity is what a lock covers: a row, page, table or database. Fine locks allow more concurrency but cost memory; coarse ones are cheap but make transactions wait. An intention lock (IS, IX) on the table before row locks lets a table lock request see at once that rows are in use. Lock escalation swaps many row locks for one table lock: SQL Server does it when a statement holds many, so a big UPDATE can block the whole table. PostgreSQL and InnoDB do not escalate.