PostgreSQL provides fine-grained control over concurrent access to rows through row-level locks. These are triggered via locking clauses in SELECT statements, such as FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, and FOR KEY SHARE. Under the hood, PostgreSQL uses internal identifiers called row marks to manage the locking behavior for each selected row. Understanding these row mark types is essential for building reliable, concurrent systems and avoiding anomalies like lost updates or inconsistent reads.
What Are Row Marks?
A row mark in PostgreSQL represents a locking directive applied to tuples during the execution of a SELECT query with a locking clause. These marks determine what other operations (like updates or deletes) can be performed on the row by concurrent transactions. Although the locking clauses are simple to use in SQL, PostgreSQL distinguishes several internal row mark types, each with distinct semantics and behaviors.
- ROW_MARK_EXCLUSIVE – FOR UPDATE
This is the strictest and most commonly used row mark type. It acquires an exclusive lock on the row, blocking any concurrent transaction from acquiring a conflicting lock. This lock type prevents other transactions from updating, deleting, or acquiring FOR UPDATE or FOR NO KEY UPDATE locks on the row. However, it does not prevent other transactions from performing regular SELECT operations (reads) on the row.
SELECT * FROM accounts WHERE id = 101 FOR UPDATE;
Such locks are typically used in scenarios where the transaction intends to update or delete the selected rows, and consistency must be preserved in highly concurrent environments.
- ROW_MARK_NOKEYEXCLUSIVE – FOR NO KEY UPDATE
This lock type prevents other transactions from updating, deleting, or acquiring FOR UPDATE or FOR NO KEY UPDATE locks on the row. The key difference from FOR UPDATE is that it allows concurrent transactions to acquire FOR KEY SHARE locks. This makes it suitable when you need to update a row but don't want to block foreign key references from other transactions.
SELECT * FROM accounts WHERE id = 101 FOR NO KEY UPDATE;
FOR NO KEY UPDATE is useful when you need to update a row but want to allow other transactions to reference it via foreign keys without blocking.
- ROW_MARK_SHARE – FOR SHARE
This row mark allows multiple transactions to read a row concurrently while preventing modifications. It blocks other transactions from performing UPDATE, DELETE, SELECT FOR UPDATE, or SELECT FOR NO KEY UPDATE operations on the row. However, it allows regular SELECT operations and is compatible with other FOR SHARE and FOR KEY SHARE locks.
SELECT * FROM products WHERE category = 'Books' FOR SHARE;
FOR SHARE is appropriate for transactions that need to ensure the row remains unchanged during their processing window while still allowing other readers.
- ROW_MARK_KEYSHARE – FOR KEY SHARE
This is the least restrictive of the lock types. It prevents only two operations: deleting the row and updating its key columns (primary key or unique index columns). Other updates to non-key fields are allowed. Multiple transactions can hold KEY SHARE locks on the same row simultaneously, and this lock type is compatible with FOR NO KEY UPDATE.
SELECT * FROM orders WHERE customer_id = 123 FOR KEY SHARE;
This type of lock is typically used for foreign key enforcement, where the referencing rows must ensure the continued existence and key stability of the referenced rows.
Inspecting Row-Level Locks
PostgreSQL provides system views to monitor current locks. You can use the pg_locks view to inspect active row-level locks:
SELECT
locktype,
database,
relation::regclass,
page,
tuple,
pid,
mode,
granted
FROM pg_locks
WHERE locktype = 'relation';
Practical Example: Bank Account Transfer
Here's a practical example showing how different lock types work in a bank transfer scenario:
-- Transaction 1: Transfer money (needs exclusive access)
BEGIN;
SELECT balance FROM accounts WHERE id = 101 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 101;
-- This blocks other FOR UPDATE attempts on account 101
-- Transaction 2: Check account balance (read-only)
SELECT balance FROM accounts WHERE id = 101;
-- This succeeds even while Transaction 1 holds FOR UPDATE
-- Transaction 3: Verify account exists for foreign key
SELECT id FROM accounts WHERE id = 101 FOR KEY SHARE;
-- This would block if Transaction 1 used FOR UPDATE
-- But would succeed if Transaction 1 used FOR NO KEY UPDATE
PostgreSQL's row-level locking via row marks provides developers with granular control over how concurrent transactions interact with rows in a table. By choosing the right type of row mark—FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, or FOR KEY SHARE—you can achieve the right balance between consistency and concurrency.
Understanding these mechanisms is essential for building robust, high-performance applications where data integrity is critical under concurrent workloads. Knowing how PostgreSQL enforces these locks helps developers and DBAs design safer and more efficient systems.