postgresql locks concurrency mvcc deadlocks transactions

Advanced database locking and concurrency control

Maria Garcia Feb 2026

2 tabs

-- 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;

2 files · sql Explain with highlit

Database locks ensure data consistency in concurrent access. I understand lock types—row, table, advisory. Shared locks allow concurrent reads. Exclusive locks prevent all access. Understanding lock granularity prevents contention. Deadlocks occur when transactions wait circularly—detection and retry needed. Lock timeouts prevent hung transactions. FOR UPDATE locks rows for modification. SKIP LOCKED enables job queue patterns. Advisory locks provide application-level coordination. Proper locking balances consistency and concurrency. Essential for high-traffic applications, background jobs. PostgreSQL's MVCC minimizes locking overhead.