Row Locks and Table Locks

~12 min · transactions, locks

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Locks PostgreSQL takes for you

An UPDATE on a row takes a FOR UPDATE-style row lock until the transaction ends. A second UPDATE on the same row from another transaction waits. SELECTs don't take row locks (MVCC handles them) unless you explicitly ask with SELECT ... FOR UPDATE.

Explicit row locking

When you read a row with the intent of updating it, lock it explicitly: SELECT ... FOR UPDATE. This prevents a "lost update" where two concurrent transactions both read the same value and overwrite each other's change.

Table-level locks

DDL (ALTER TABLE, DROP TABLE) takes an exclusive lock on the table — nobody can read or write while it runs. The cure for "schema migration locked production for 30 seconds" is the CONCURRENTLY variants of DDL where they exist (CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, etc.).

Code

SELECT FOR UPDATE — explicit row lock·sql

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Now any other transaction trying to UPDATE row 1 waits.
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

FOR UPDATE SKIP LOCKED — work-queue pattern·sql

-- Pull the next pending job, skipping any that other workers have locked
BEGIN;
SELECT id, payload FROM jobs
WHERE  status = 'pending'
ORDER  BY created_at
LIMIT  1
FOR UPDATE SKIP LOCKED;
-- ... process ...
UPDATE jobs SET status = 'done' WHERE id = ?;
COMMIT;

Build indexes without blocking writes·sql

-- Locks the table briefly, then reads without an exclusive lock
CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id);

External links

PostgreSQL Explicit Locking

Exercise

Implement a tiny job queue: a jobs table with status, a worker that does SELECT ... FOR UPDATE SKIP LOCKED LIMIT 1, processes the job, UPDATEs the status, COMMITs. Run two workers in parallel; verify they don't grab the same job.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousMVCC: How PostgreSQL Handles Concurrency Next →Deadlocks: How They Happen

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.