Locking — Shared & Exclusive

While MVCC handles read isolation elegantly, write conflicts still need coordination. Locks prevent two transactions from making incompatible changes simultaneously.

Lock Types

The key rule: shared locks are compatible with each other, but exclusive locks are not compatible with anything.

Compatibility Matrix

                 Requesting:
                 SHARED    EXCLUSIVE
Held: NONE       ✓ grant    ✓ grant
Held: SHARED     ✓ grant    ✗ wait
Held: EXCLUSIVE  ✗ wait     ✗ wait

Multiple readers can hold shared locks simultaneously. A writer must wait for all readers (and other writers) to release their locks.

Lock Granularity

Locks can be at different levels:

SQLite uses database-level locking (simple but limits concurrency). PostgreSQL uses row-level locking (complex but highly concurrent).

Lock Upgrade

A transaction holding a shared lock might need to upgrade to exclusive:

Transaction A: LOCK users SHARED     → granted
Transaction A: wants EXCLUSIVE       → can upgrade IF no one else holds shared

If Transaction B also holds a shared lock on users, A cannot upgrade — this could lead to deadlock if B also tries to upgrade.

Deadlock

Deadlock occurs when two transactions wait for each other:

Txn A: LOCK table1 EXCLUSIVE → granted
Txn B: LOCK table2 EXCLUSIVE → granted
Txn A: LOCK table2 EXCLUSIVE → waits for B
Txn B: LOCK table1 EXCLUSIVE → waits for A → DEADLOCK!

Solutions:

• Detection: Build a "waits-for" graph, check for cycles, abort one transaction
• Prevention: Always acquire locks in a consistent order
• Timeout: If waiting too long, assume deadlock and abort

Two-Phase Locking (2PL)

The standard protocol for correct locking:

1. Growing phase: Acquire locks, never release
2. Shrinking phase: Release locks, never acquire

This guarantees serializability — the result is equivalent to running transactions one at a time.

How PostgreSQL Handles Locks

PostgreSQL uses a sophisticated lock manager (lock.c) with row-level locks stored in shared memory. For high-concurrency workloads, it also uses "fast path" locking that avoids the main lock table for simple cases.

Your Task

Implement table-level shared and exclusive locks with a lock status command. Handle the upgrade case where a sole shared lock holder can upgrade to exclusive.