Locking Before You Act
When you cannot use Serializable isolation (due to performance requirements) but need to prevent write skew or lost updates, you can lock the data explicitly at read time. This is Pessimistic Locking — you assume conflict will happen and prevent it proactively.
The SQL mechanism is SELECT ... FOR UPDATE: wrapping the same read that used to run as a plain SELECT in FOR UPDATE locks every matching row for the rest of the transaction, and the subsequent UPDATE and COMMIT proceed as normal.
When Transaction A runs SELECT ... FOR UPDATE, the database places an exclusive lock on the matching rows. When Transaction B tries to run the same query, it blocks — it waits until Transaction A commits or rolls back. When Transaction B finally proceeds, it sees the updated count (1 doctor remaining) and correctly decides it cannot go off call.
Lock Variants
| Lock Type | SQL | Behavior |
|---|---|---|
FOR UPDATE |
SELECT ... FOR UPDATE |
Exclusive lock. Blocks other readers using FOR UPDATE or FOR SHARE. |
FOR SHARE |
SELECT ... FOR SHARE |
Shared lock. Multiple readers can hold it simultaneously, but writers are blocked. |
FOR NO KEY UPDATE |
SELECT ... FOR NO KEY UPDATE |
Like FOR UPDATE but allows foreign key checks to proceed. |
SKIP LOCKED |
SELECT ... FOR UPDATE SKIP LOCKED |
Skip rows that are already locked. Useful for job queues. |
The Downside: Contention and Deadlocks
Pessimistic locking has real costs:
Contention: If Transaction A holds a lock and then does something slow (a network call, a user interaction, a complex calculation), every other transaction waiting on that lock is frozen. This can cascade into a system-wide slowdown.
Deadlocks: Transaction A locks Row 1 and waits for Row 2. Transaction B locks Row 2 and waits for Row 1. Neither can proceed. Databases detect deadlocks and abort one transaction automatically, but the application must handle the error and retry.
Transaction A: LOCK Row 1 → waiting for Row 2
Transaction B: LOCK Row 2 → waiting for Row 1
Database: Detects cycle → aborts Transaction B with "deadlock detected"
Pessimistic locking is the right choice when: conflicts are frequent, the cost of a failed transaction is high (e.g., a user already entered payment info), or you need to hold a reservation for a duration (e.g., a seat held for 5 minutes during checkout).