Database Lock

ELI5 — The Vibe Check

A database lock prevents multiple transactions from messing with the same data at the same time. It's like a bathroom door lock — one person at a time. Row locks let others read but not write. Table locks block everything. Too many locks and you get deadlocks. Fun times.

Real Talk

Database locks are concurrency control mechanisms that restrict access to data during transactions. Types include row-level locks (most granular, least blocking), table-level locks (broadest), advisory locks (application-controlled), and shared vs exclusive locks. Lock granularity trades concurrency for overhead. Excessive locking causes contention and deadlocks.

Show Me The Code

-- Explicit row lock (SELECT FOR UPDATE)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Row is now locked until COMMIT/ROLLBACK
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Advisory lock for application-level mutex
SELECT pg_advisory_lock(12345);

When You'll Hear This

"Use SELECT FOR UPDATE to lock the row before modifying the balance." / "Two transactions deadlocked because they locked rows in opposite order."