LockRows

A LockRows node acquires row-level locks (FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE) on the rows passing through it, so they can't be changed by other transactions until yours ends.

For explicit SELECT ... FOR UPDATE / FOR SHARE locking clauses.

Correct and necessary for pessimistic locking, but it's a contention point: locking many rows, or holding the transaction open, can block other writers. Lock as few rows as late as possible.

What the lock modes mean

A LockRows node takes one of PostgreSQL's row-level locks on every row that flows through it, on behalf of the surrounding query:

• FOR UPDATE — strongest. Blocks other UPDATEs/DELETEs and other FOR UPDATE/FOR NO KEY UPDATE/FOR SHARE on the same row.
• FOR NO KEY UPDATE — like FOR UPDATE but doesn't conflict with key-only locks (taken by foreign-key checks).
• FOR SHARE — blocks FOR UPDATE and writers; allows other readers.
• FOR KEY SHARE — weakest. Used internally for FK checks.

The choice changes how much contention you create with other transactions.

SKIP LOCKED and NOWAIT

Two clauses that turn LockRows into a polite citizen:

SELECT id, payload
FROM jobs
WHERE status = 'queued'
ORDER BY created_at
FOR UPDATE SKIP LOCKED        -- skip rows others have locked
LIMIT 1;

SKIP LOCKED makes the query simply not return rows another transaction is already handling — perfect for work queues. NOWAIT raises an error immediately instead of waiting for a conflicting lock to release.

Lock as few rows as late as possible

LockRows is held for the rest of the transaction; long-running transactions that lock many rows are exactly what causes the contention everyone complains about. Practical advice:

• Filter aggressively before locking — combine the predicate and the locking clause so you only lock the rows you'll act on.
• Keep the transaction short; release locks by committing as soon as the write that needs them is done.
• For background work, prefer FOR UPDATE SKIP LOCKED and short batches over one giant locking pass.

• Insert / Update / Delete / Merge (ModifyTable)