Understanding PostgreSQL Row-Level Locking: A Practical Guide

When multiple users try to update the same data simultaneously, chaos could ensue without proper coordination. PostgreSQL’s row-level locking mechanism is the invisible guardian that keeps your data consistent while allowing maximum concurrency. Let’s dive deep into how it works and see it in action.

Why Row-Level Locks Matter

Imagine two bank transactions processing on the same account simultaneously. Without proper locking, both might read a balance of Rs1000, each subtract Rs100, and both write back Rs900—losing one withdrawal in the process. Row-level locks prevent this by ensuring that when one transaction is modifying a row, others must wait their turn.

The Main Row-Level Lock Modes

PostgreSQL implements several row-level lock modes, but the two most important ones are:

FOR UPDATE: The strongest lock, used when you intend to modify a row. It blocks other transactions from acquiring FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, or FOR KEY SHARE locks on the same row.

FOR SHARE: A weaker lock for reading data you plan to reference. It allows other transactions to read the row but prevents modifications.

Seeing Locks in Action

Let’s create a practical scenario with a simple accounts table:

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    account_number VARCHAR(20) UNIQUE,
    balance DECIMAL(10,2),
    last_updated TIMESTAMP DEFAULT NOW()
);

INSERT INTO accounts (account_number, balance) VALUES
    ('ACC001', 1000.00),
    ('ACC002', 2500.00),
    ('ACC003', 500.00);

Example 1: Basic Row Locking with FOR UPDATE

Open two separate database sessions (two terminal windows or psql connections).

Session 1:

BEGIN;

-- Lock the row for update
SELECT * FROM accounts 
WHERE account_number = 'ACC001' 
FOR UPDATE;

-- Take your time processing...
-- The lock is held until COMMIT or ROLLBACK

Session 2:

BEGIN;

-- This will block and wait
SELECT * FROM accounts 
WHERE account_number = 'ACC001' 
FOR UPDATE;

Session 2 will hang, waiting for Session 1 to complete. You can verify this by checking the locks:

Session 3 (monitoring):

SELECT 
    l.pid,
    l.mode,
    l.granted,
    a.query,
    a.state,
    a.wait_event_type,
    a.wait_event
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation = 'accounts'::regclass;

You’ll see something like:

 pid  |   mode          | granted | query                          | state  | wait_event_type | wait_event
------+-----------------+---------+--------------------------------+--------+-----------------+------------
 1234 | RowShareLock    | t       | SELECT * FROM accounts FOR...  | idle   | Client          | ClientRead
 5678 | RowShareLock    | t       | SELECT * FROM accounts FOR...  | active | Lock            | transactionid

Example 2: Lost Update Without Proper Locking

This demonstrates why explicit locking matters:

Session 1:

BEGIN;
SELECT balance FROM accounts WHERE account_number = 'ACC001';
-- Returns 1000.00

-- Simulate some processing time
SELECT pg_sleep(5);

-- Update based on the value we read
UPDATE accounts 
SET balance = 900.00, last_updated = NOW() 
WHERE account_number = 'ACC001';

Session 2 (start immediately after Session 1’s SELECT):

BEGIN;
SELECT balance FROM accounts WHERE account_number = 'ACC001';
-- Also returns 1000.00!

-- Both sessions think the balance is 1000
UPDATE accounts 
SET balance = 800.00, last_updated = NOW() 
WHERE account_number = 'ACC001';
-- This will block until Session 1 commits

After both commit, the final balance will be 800.00, but logically it should be 700.00 if we intended two separate $100 withdrawals.

Example 3: Correct Pattern with SELECT FOR UPDATE

Here’s the right way to handle concurrent updates:

Session 1:

BEGIN;

-- Lock the row first
SELECT balance FROM accounts 
WHERE account_number = 'ACC001' 
FOR UPDATE;
-- Returns 1000.00 and acquires lock

-- Process the withdrawal
UPDATE accounts 
SET balance = balance - 100.00, last_updated = NOW() 
WHERE account_number = 'ACC001';

COMMIT;

Session 2:

BEGIN;

-- This waits for Session 1's lock
SELECT balance FROM accounts 
WHERE account_number = 'ACC001' 
FOR UPDATE;
-- Returns 900.00 (after Session 1 commits)

-- Process the withdrawal with correct starting balance
UPDATE accounts 
SET balance = balance - 100.00, last_updated = NOW() 
WHERE account_number = 'ACC001';

COMMIT;

Now the final balance correctly reflects both withdrawals: 800.00.

Understanding Lock Wait Events

When a transaction is waiting for a lock, you can diagnose it with this query:

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_statement,
    blocking_activity.query AS blocking_statement,
    blocked_activity.wait_event_type || ':' || 
        blocked_activity.wait_event AS blocked_wait_event
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;

Practical Tips for Minimizing Lock Contention

1. Keep transactions short: The longer you hold locks, the more likely you’ll block others.

-- Bad: Long-running transaction
BEGIN;
SELECT * FROM accounts WHERE account_number = 'ACC001' FOR UPDATE;
-- ... complex business logic taking 10 seconds ...
UPDATE accounts SET balance = balance - 100 WHERE account_number = 'ACC001';
COMMIT;

-- Better: Minimize locked time
-- Do business logic first
-- ... complex calculations ...
BEGIN;
SELECT * FROM accounts WHERE account_number = 'ACC001' FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE account_number = 'ACC001';
COMMIT;

2. Lock in consistent order: Always acquire locks in the same sequence to avoid deadlocks.

-- If transferring between accounts, always lock lower ID first
SELECT * FROM accounts 
WHERE account_number IN ('ACC001', 'ACC002')
ORDER BY id  -- Consistent ordering
FOR UPDATE;

3. Use appropriate lock strength: Don’t use FOR UPDATE if FOR SHARE suffices.

-- If just reading to validate existence
SELECT * FROM accounts 
WHERE account_number = 'ACC001' 
FOR SHARE;  -- Allows concurrent reads

Conclusion

Row-level locking is PostgreSQL’s mechanism for maintaining data integrity under concurrent access. Understanding when and how locks are acquired—and being able to monitor them with pg_locks and pg_stat_activity—is essential for building robust, high-performance applications. The key is balancing consistency needs with concurrency: use explicit locking when necessary, but keep transactions brief and lock in predictable patterns to maximize throughput.

See this in action at PGConf India 2026 – Understanding Locking and Concurrency in PostgreSQL by Pavan Deolasee.