PostgreSQL uses MVCC (multi-version concurrency control): writers create new row versions instead of overwriting, so readers never block writers and writers never block readers. Each transaction sees a consistent snapshot.
The classic bug is the lost update: two sessions read a balance, both subtract a payment in application code, and one write overwrites the other. Fix it by updating atomically (SET balance = balance - x), or by locking the row first with SELECT ... FOR UPDATE when you need to read before writing.
FOR UPDATE SKIP LOCKED lets many workers pull jobs from the same table without blocking each other — a reliable job queue with no extra infrastructure. Advisory locks coordinate application-level tasks, such as making sure only one server runs the nightly report.
-- Safe read-then-write: lock the row until COMMIT
BEGIN;
SELECT fee_balance FROM students WHERE id = 5 FOR UPDATE; -- other writers to row 5 now wait
UPDATE students SET fee_balance = fee_balance - 100000 WHERE id = 5;
INSERT INTO payments (student_id, amount, method) VALUES (5, 100000, 'bank');
COMMIT;
-- A job queue that many workers can share
CREATE TABLE sms_jobs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
phone text NOT NULL,
message text NOT NULL,
status text NOT NULL DEFAULT 'pending',
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_sms_jobs_pending ON sms_jobs (created_at) WHERE status = 'pending';
INSERT INTO sms_jobs (phone, message)
SELECT '07' || lpad(g::text, 8, '0'), 'Results for term 2 are out'
FROM generate_series(1, 10) AS g;
-- Each worker runs this; concurrent workers get different jobs, never the same one
BEGIN;
WITH next_jobs AS (
SELECT id FROM sms_jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 3
FOR UPDATE SKIP LOCKED
)
UPDATE sms_jobs j SET status = 'sending'
FROM next_jobs
WHERE j.id = next_jobs.id
RETURNING j.id, j.phone;
-- ... send the SMS messages, then:
COMMIT;
SELECT status, count(*) FROM sms_jobs GROUP BY status ORDER BY status;
-- Advisory lock: only one process runs the nightly job
SELECT pg_try_advisory_lock(42) AS got_lock; -- true for the first caller, false for others
SELECT pg_advisory_unlock(42);
-- See who is waiting on whom
SELECT pid, state, wait_event_type, left(query, 60) AS query
FROM pg_stat_activity
WHERE datname = current_database();Key points
- MVCC: readers and writers don't block each other.
- Prevent lost updates with atomic
SET x = x - norSELECT ... FOR UPDATE. FOR UPDATE SKIP LOCKEDturns a table into a safe multi-worker job queue.
Exercise
Open two psql sessions. In both, BEGIN and SELECT ... FOR UPDATE the same student; observe the second one wait until the first commits. Then repeat with SKIP LOCKED on sms_jobs and confirm each session gets different jobs.
Show solution
Try the exercise yourself first — then compare your approach with this one.
Open two psql windows and run the steps in the order shown. With FOR UPDATE, session B's identical lock waits until session A commits. With FOR UPDATE SKIP LOCKED, session B doesn't wait — it simply takes the next free jobs, so the two sessions never get the same job.
-- A1: lock student 5
BEGIN;
SELECT full_name, fee_balance FROM students WHERE id = 5 FOR UPDATE;
-- A2 (after starting B1): finish — session B continues immediately
COMMIT;
-- A3: take three jobs and keep the transaction open
BEGIN;
SELECT id FROM sms_jobs WHERE status = 'pending'
ORDER BY created_at LIMIT 3
FOR UPDATE SKIP LOCKED; -- e.g. ids 4, 5, 6-- B1: same lock — this waits (it looks frozen) until A runs COMMIT
BEGIN;
SELECT full_name, fee_balance FROM students WHERE id = 5 FOR UPDATE;
COMMIT;
-- B2 (while A3 is still open): different jobs, no waiting
BEGIN;
SELECT id FROM sms_jobs WHERE status = 'pending'
ORDER BY created_at LIMIT 3
FOR UPDATE SKIP LOCKED; -- e.g. ids 7, 8, 9
COMMIT;