-- Row-level locks with SELECT FOR UPDATE
BEGIN;
SELECT * FROM inventory
WHERE product_id = 123
FOR UPDATE; -- Locks selected rows
-- Other transactions wait here
-- Process and update
UPDATE inventory SET quantity = quantity - 1
WHERE product_id = 123;
COMMIT;
-- NOWAIT: Don't wait for lock
BEGIN;
SELECT * FROM inventory
WHERE product_id = 123
FOR UPDATE NOWAIT;
-- Raises error if locked
-- SKIP LOCKED: Skip locked rows (job queue pattern)
BEGIN;
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Multiple workers can process different jobs concurrently
UPDATE job_queue SET status = 'processing' WHERE id = ?;
COMMIT;
-- FOR SHARE: Allows concurrent readers, blocks writers
SELECT * FROM products
WHERE id = 123
FOR SHARE;
-- Other SELECT FOR SHARE allowed
-- UPDATE blocks
-- Table-level locks
BEGIN;
LOCK TABLE products IN ACCESS EXCLUSIVE MODE;
-- No other access allowed
TRUNCATE TABLE products;
COMMIT;
-- Lock modes (least to most restrictive):
-- ACCESS SHARE: SELECT
-- ROW SHARE: SELECT FOR UPDATE
-- ROW EXCLUSIVE: INSERT, UPDATE, DELETE
-- SHARE UPDATE EXCLUSIVE: VACUUM, CREATE INDEX CONCURRENTLY
-- SHARE: CREATE INDEX
-- SHARE ROW EXCLUSIVE: (rare)
-- EXCLUSIVE: (rare)
-- ACCESS EXCLUSIVE: DROP TABLE, TRUNCATE, VACUUM FULL
-- Advisory locks (application-level)
-- Take advisory lock
SELECT pg_advisory_lock(123);
-- Do work exclusively
-- ...
-- Release lock
SELECT pg_advisory_unlock(123);
-- Try lock (non-blocking)
SELECT pg_try_advisory_lock(123);
-- Returns true if acquired, false if already locked
-- Session-level advisory lock
SELECT pg_advisory_lock(12345);
-- Released when session ends
-- Transaction-level advisory lock
BEGIN;
SELECT pg_advisory_xact_lock(12345);
-- Released at transaction end
COMMIT;
-- Use advisory locks for distributed coordination
/*
// Node.js example
const lockId = 12345;
// Try to acquire lock
const result = await client.query(
'SELECT pg_try_advisory_lock($1)',
[lockId]
);
if (result.rows[0].pg_try_advisory_lock) {
try {
// Do exclusive work
await processJob();
} finally {
// Release lock
await client.query(
'SELECT pg_advisory_unlock($1)',
[lockId]
);
}
} else {
console.log('Job already being processed');
}
*/
-- Deadlock detection
SET deadlock_timeout = '1s';
-- Session 1:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Waits for lock on id = 2
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Session 2 (simultaneously):
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- Waits for lock on id = 1 -> DEADLOCK!
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- One transaction aborted automatically
-- Prevent deadlocks: Acquire locks in consistent order
BEGIN;
-- Always lock lower ID first
UPDATE accounts SET balance = balance - 100
WHERE id = LEAST(1, 2);
UPDATE accounts SET balance = balance + 100
WHERE id = GREATEST(1, 2);
COMMIT;
-- Monitor locks
SELECT
locktype,
database,
relation::regclass,
page,
tuple,
virtualxid,
transactionid,
mode,
granted,
pid
FROM pg_locks
WHERE NOT granted
ORDER BY pid;
-- Find blocking queries
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_query,
blocking_activity.query AS blocking_query,
blocked_activity.application_name AS blocked_app
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
-- Lock timeout
SET lock_timeout = '5s';
SELECT * FROM products WHERE id = 123 FOR UPDATE;
-- Fails after 5 seconds if can't acquire lock
-- Statement timeout (overall query time)
SET statement_timeout = '30s';
-- Idle in transaction timeout
SET idle_in_transaction_session_timeout = '10min';
-- MVCC (Multi-Version Concurrency Control)
-- Readers don't block writers, writers don't block readers
-- Transaction 1:
BEGIN;
SELECT * FROM products WHERE id = 123;
-- Sees snapshot at transaction start
-- Transaction 2 (concurrent):
UPDATE products SET price = 99.99 WHERE id = 123;
COMMIT;
-- Transaction 1 (continued):
SELECT * FROM products WHERE id = 123;
-- Still sees old price! (snapshot isolation)
COMMIT;
-- Serializable isolation (strictest)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE id = 1;
-- Do calculations
UPDATE accounts SET balance = 100 WHERE id = 1;
COMMIT;
-- Aborts if concurrent transaction modified same data
-- Optimistic locking pattern
ALTER TABLE products ADD COLUMN version INT DEFAULT 1;
-- Application:
-- 1. Read with version
SELECT id, name, price, version FROM products WHERE id = 123;
-- version = 5
-- 2. Update only if version matches
UPDATE products
SET price = 99.99, version = version + 1
WHERE id = 123 AND version = 5;
-- If 0 rows updated, another transaction modified it
-- Application retries
-- Pessimistic locking pattern
BEGIN;
SELECT * FROM products WHERE id = 123 FOR UPDATE;
-- Lock immediately, no retry needed
UPDATE products SET price = 99.99 WHERE id = 123;
COMMIT;
-- Queue processing with SKIP LOCKED
CREATE TABLE tasks (
id SERIAL PRIMARY KEY,
status VARCHAR(20) DEFAULT 'pending',
data JSONB,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Worker picks up task
WITH next_task AS (
SELECT id
FROM tasks
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED
)
UPDATE tasks
SET status = 'processing'
WHERE id = (SELECT id FROM next_task)
RETURNING *;
-- Multiple workers process different tasks concurrently
-- Advisory lock for singleton jobs
CREATE OR REPLACE FUNCTION run_daily_job()
RETURNS VOID AS $$
BEGIN
-- Try to acquire lock
IF pg_try_advisory_lock(12345) THEN
BEGIN
-- Do work
PERFORM expensive_operation();
EXCEPTION
WHEN OTHERS THEN
-- Ensure lock is released
PERFORM pg_advisory_unlock(12345);
RAISE;
END;
PERFORM pg_advisory_unlock(12345);
ELSE
RAISE NOTICE 'Job already running';
END IF;
END;
$$ LANGUAGE plpgsql;
-- Lock monitoring dashboard
SELECT
l.locktype,
l.mode,
l.granted,
a.pid,
a.usename,
a.application_name,
a.client_addr,
a.state,
a.query,
NOW() - a.query_start AS duration
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.locktype IN ('relation', 'transactionid')
ORDER BY duration DESC NULLS LAST;
