Row-level locking (FOR UPDATE, FOR SHARE) in PostgreSQL - Step-by-Step Execution

Concept Flow - Row-level locking (FOR UPDATE, FOR SHARE)

Start Transaction

↓

Execute SELECT ... FOR UPDATE/SHARE

↓

Lock rows matching condition

↓

Other transactions wait if they try to lock same rows

↓

Commit or Rollback transaction

↓

Release locks

Begin a transaction, select rows with locking clause, lock those rows, block other conflicting locks, then commit or rollback to release locks.

Execution Sample

PostgreSQL

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- do some updates
COMMIT;

Starts a transaction, locks the row with id=1 for update, then commits to release the lock.

Execution Table

Step	Action	Query/Condition	Lock Acquired	Other Transactions Behavior	Result
1	Start transaction	BEGIN;	No lock yet	No blocking	Transaction started
2	Select row with FOR UPDATE	SELECT * FROM accounts WHERE id=1 FOR UPDATE;	Row id=1 locked for update	Other txns wait if they try FOR UPDATE on id=1	Row locked, data returned
3	Another txn tries FOR UPDATE on same row	SELECT * FROM accounts WHERE id=1 FOR UPDATE;	Blocked	Waits until first txn commits or rollbacks	Blocked until lock released
4	Commit first transaction	COMMIT;	Locks released	Blocked txns proceed	Locks released, changes saved
5	Blocked txn proceeds	SELECT * FROM accounts WHERE id=1 FOR UPDATE;	Row id=1 locked for update	No blocking now	Row locked, data returned
6	End	-	No locks held	No blocking	All locks released, transactions complete

💡 Locks are released after COMMIT or ROLLBACK, allowing other transactions to proceed.

Variable Tracker

Variable	Start	After Step 2	After Step 4	After Step 5	Final
locked_rows	none	row id=1 locked	none (locks released)	row id=1 locked	none (locks released)
transaction_state	none	active	committed	active	committed

Key Moments - 3 Insights

Why does the second transaction wait at step 3?

Because the first transaction holds a FOR UPDATE lock on the same row, PostgreSQL blocks the second transaction to prevent conflicting changes until the first transaction commits or rolls back (see execution_table step 3).

What happens to locks after COMMIT at step 4?

All row-level locks held by the transaction are released, allowing other waiting transactions to proceed (see execution_table step 4).

What is the difference between FOR UPDATE and FOR SHARE?

FOR UPDATE locks rows for exclusive updates blocking others from modifying them, while FOR SHARE allows concurrent reads but blocks updates. This example uses FOR UPDATE to show exclusive locking.

Visual Quiz - 3 Questions

Look at the execution_table at step 2. What lock is acquired on the row with id=1?

A No lock acquired

B Row locked for shared read

C Row locked for update (exclusive lock)

D Table-level lock acquired

💡 Hint

Check the 'Lock Acquired' column at step 2 in execution_table.

At which step does the first transaction release its locks?

A Step 4

B Step 3

C Step 2

D Step 5

💡 Hint

Look for 'Locks released' in the 'Lock Acquired' column in execution_table.

If the first transaction did not commit or rollback, what would happen to the second transaction at step 3?

A It would proceed immediately

B It would be blocked indefinitely

C It would get an error

D It would lock a different row

💡 Hint

See 'Other Transactions Behavior' at step 3 in execution_table.

Concept Snapshot

Row-level locking in PostgreSQL:
- Use FOR UPDATE to lock selected rows exclusively.
- Use FOR SHARE to lock rows for shared access.
- Locks block other transactions trying conflicting locks.
- Locks are held until COMMIT or ROLLBACK.
- Prevents race conditions in concurrent updates.

Full Transcript

Row-level locking in PostgreSQL works by starting a transaction, then selecting rows with FOR UPDATE or FOR SHARE to lock them. The locked rows prevent other transactions from modifying them until the first transaction commits or rolls back. This ensures data consistency when multiple users access the same rows. The example shows a transaction locking a row with FOR UPDATE, blocking another transaction trying to lock the same row until the first transaction finishes. Locks are released after commit or rollback, allowing waiting transactions to proceed.