PostgreSQL Locking Mechanism

Overview

Locks are mechanisms used by databases to control concurrent access, ensuring consistency and integrity when multiple transactions operate on data simultaneously. PostgreSQL provides various types of locks to meet different scenario requirements.

Lock Types

Table-Level Locks

Lock Mode	Description	Conflicts With
ACCESS SHARE	Acquired by SELECT	ACCESS EXCLUSIVE
ROW SHARE	SELECT FOR UPDATE/SHARE	EXCLUSIVE, ACCESS EXCLUSIVE
ROW EXCLUSIVE	UPDATE/DELETE/INSERT	SHARE and above
SHARE UPDATE EXCLUSIVE	VACUUM, ANALYZE	Multiple
SHARE	CREATE INDEX	ROW EXCLUSIVE and above
SHARE ROW EXCLUSIVE	CREATE TRIGGER etc.	Multiple
EXCLUSIVE	Refresh materialized view	All except ACCESS SHARE
ACCESS EXCLUSIVE	ALTER TABLE, DROP TABLE	All

Row-Level Locks

Lock Mode	Description
FOR UPDATE	Strongest lock, blocks other transactions from modifying or locking
FOR NO KEY UPDATE	Allows others to acquire FOR KEY SHARE
FOR SHARE	Blocks UPDATE/DELETE, allows SELECT
FOR KEY SHARE	Weakest lock, only blocks DELETE and primary key updates

Explicit Locking

Table-Level Locks

-- Lock entire table
LOCK TABLE users IN ACCESS EXCLUSIVE MODE;

-- Different lock modes
LOCK TABLE users IN SHARE MODE;
LOCK TABLE users IN EXCLUSIVE MODE;
LOCK TABLE users IN ROW EXCLUSIVE MODE;

-- With NOWAIT option (error immediately if lock unavailable)
LOCK TABLE users IN ACCESS EXCLUSIVE MODE NOWAIT;

Row-Level Locks

-- SELECT FOR UPDATE (exclusive lock)
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- SELECT FOR SHARE (shared lock)
SELECT * FROM users WHERE id = 1 FOR SHARE;

-- NOWAIT option
SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;

-- SKIP LOCKED (skip locked rows)
SELECT * FROM users WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 10;

Advisory Locks

Advisory locks are application-level locks; PostgreSQL doesn't enforce their semantics.

Session-Level Advisory Locks

-- Acquire lock
SELECT pg_advisory_lock(12345);

-- Try to acquire lock (non-blocking)
SELECT pg_try_advisory_lock(12345);

-- Release lock
SELECT pg_advisory_unlock(12345);

-- Release all session locks
SELECT pg_advisory_unlock_all();

Transaction-Level Advisory Locks

-- Acquire transaction-level lock (auto-released at transaction end)
SELECT pg_advisory_xact_lock(12345);

-- Try to acquire transaction-level lock
SELECT pg_try_advisory_xact_lock(12345);

Advisory Lock Example

-- Use advisory lock to prevent concurrent task processing
CREATE OR REPLACE FUNCTION process_task(task_id BIGINT)
RETURNS BOOLEAN AS $$
BEGIN
    -- Try to acquire lock
    IF NOT pg_try_advisory_xact_lock(task_id) THEN
        RETURN FALSE;  -- Task is being processed by another process
    END IF;

    -- Process task
    UPDATE tasks SET status = 'processing' WHERE id = task_id;
    -- ... other processing logic

    UPDATE tasks SET status = 'completed' WHERE id = task_id;
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

Deadlocks

Deadlock Example

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Waiting for Transaction 2 to release lock on id=2
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction 2 (simultaneously)
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- Waiting for Transaction 1 to release lock on id=1
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- Deadlock!

Avoiding Deadlocks

-- Method 1: Access resources in fixed order
BEGIN;
-- Always lock lower ID account first
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Method 2: Use SELECT FOR UPDATE to pre-lock
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Deadlock Detection Configuration

-- View deadlock timeout setting
SHOW deadlock_timeout;

-- Set deadlock detection timeout (default 1 second)
SET deadlock_timeout = '2s';

Viewing Lock Information

View Current Locks

-- View all locks
SELECT * FROM pg_locks;

-- View locked tables
SELECT
    l.locktype,
    l.mode,
    l.granted,
    c.relname as table_name,
    a.usename,
    a.query,
    a.state
FROM pg_locks l
JOIN pg_class c ON l.relation = c.oid
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE c.relkind = 'r';

-- View processes waiting for locks
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked
    ON blocked_locks.pid = blocked.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.relation = blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking
    ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

Terminate Blocking Processes

-- Cancel query
SELECT pg_cancel_backend(pid);

-- Terminate connection
SELECT pg_terminate_backend(pid);

Lock Timeout Settings

-- Set lock wait timeout
SET lock_timeout = '10s';

-- Set statement timeout
SET statement_timeout = '30s';

-- View current settings
SHOW lock_timeout;

Optimistic vs Pessimistic Locking

Pessimistic Locking (SELECT FOR UPDATE)

BEGIN;
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- Other transactions cannot modify this row
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

Optimistic Locking (Version Number)

-- Use version number to implement optimistic locking
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;

-- Check if update succeeded
-- If affected rows = 0, version conflict, need to retry

Best Practices

1. Minimize transaction duration: Reduce lock holding time
2. Avoid long transactions: Long transactions block other operations
3. Use appropriate isolation level: Don't overuse SERIALIZABLE
4. Access resources in fixed order: Prevent deadlocks
5. Use NOWAIT or timeout: Avoid infinite waiting
6. Monitor lock waits: Detect lock issues promptly
7. Consider optimistic locking: More efficient for high-concurrency read scenarios