SQL locking and transactions — OSDC notes

Outline

1. Why locks exist — isolation vs throughput
2. What your WHERE really locks (row/gap)
3. Isolation levels (READ COMMITTED vs REPEATABLE READ defaults)
4. Deadlock detection and victim selection
5. Design: short txns, consistent lock order, idempotent retries

Demo that lands

Two sessions updating rows in opposite order → deadlock. Same work with ordered updates + retries → boredom (the goal).

Invariant: know what you lock, hold it briefly, retry transient failures. See also application error handling.