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.

Practice

1. What does the FOR UPDATE clause do in a PostgreSQL query?

easy

A. Locks selected rows to prevent other transactions from modifying them until the current transaction ends.

B. Locks the entire table to prevent any access by other transactions.

C. Allows other transactions to read but not modify the selected rows.

D. Unlocks rows that were previously locked by another transaction.

Solution

1. Step 1: Understand the purpose of FOR UPDATE

   The FOR UPDATE clause locks the rows returned by the query to prevent other transactions from modifying them.

2. Step 2: Compare with other locking types

   Unlike FOR SHARE, which allows reading but prevents writing, FOR UPDATE blocks other transactions from modifying the locked rows until the current transaction ends.

3. Final Answer:

   Locks selected rows to prevent other transactions from modifying them until the current transaction ends. -> Option A

4. Quick Check:

   FOR UPDATE = exclusive row lock [OK]

Hint: FOR UPDATE locks rows for writing, blocking others [OK]

Common Mistakes:

• Confusing FOR UPDATE with table-level locks
• Thinking FOR UPDATE allows other writes
• Mixing FOR UPDATE with FOR SHARE behavior

2. Which of the following is the correct syntax to lock rows for reading using row-level locking in PostgreSQL?

easy

A. SELECT * FROM orders FOR UPDATE;

B. SELECT * FROM orders FOR SHARE;

C. SELECT * FROM orders LOCK ROW;

D. SELECT * FROM orders WITH LOCK;

Solution

1. Step 1: Identify the clause for read locks

   In PostgreSQL, FOR SHARE is used to lock rows for reading, allowing other transactions to read but not modify.

2. Step 2: Check syntax correctness

   Options B and C are invalid syntax. SELECT * FROM orders FOR UPDATE; locks rows for writing, not reading.

3. Final Answer:

   SELECT * FROM orders FOR SHARE; -> Option B

4. Quick Check:

   Read lock = FOR SHARE [OK]

Hint: FOR SHARE locks rows for reading, syntax: SELECT ... FOR SHARE [OK]

Common Mistakes:

• Using FOR UPDATE instead of FOR SHARE for read locks
• Using non-existent LOCK ROW or WITH LOCK syntax
• Confusing table-level locks with row-level locks

3. Consider the following two transactions running concurrently:

-- Transaction 1
BEGIN;
SELECT * FROM products WHERE id = 10 FOR UPDATE;
-- Transaction 2
BEGIN;
SELECT * FROM products WHERE id = 10 FOR SHARE;

What will happen when Transaction 2 tries to execute its SELECT statement?

medium

A. Transaction 2 will immediately acquire the FOR SHARE lock and proceed.

B. Transaction 2 will cause a deadlock error immediately.

C. Transaction 2 will wait until Transaction 1 commits or rolls back before proceeding.

D. Transaction 2 will ignore the lock and read the row without waiting.

Solution

1. Step 1: Understand locking conflict between FOR UPDATE and FOR SHARE

   A FOR UPDATE lock is exclusive and blocks other transactions from acquiring conflicting locks, including FOR SHARE.

2. Step 2: Analyze Transaction 2 behavior

   Transaction 2's FOR SHARE lock request conflicts with Transaction 1's FOR UPDATE lock, so it must wait until Transaction 1 finishes.

3. Final Answer:

   Transaction 2 will wait until Transaction 1 commits or rolls back before proceeding. -> Option C

4. Quick Check:

   FOR UPDATE blocks FOR SHARE until commit [OK]

Hint: FOR UPDATE blocks FOR SHARE until transaction ends [OK]

Common Mistakes:

• Assuming FOR SHARE can proceed during FOR UPDATE lock
• Expecting deadlock without waiting
• Ignoring lock conflicts between FOR UPDATE and FOR SHARE

4. You wrote this query to lock rows for update:

SELECT * FROM customers FOR SHARE;

But you want to prevent other transactions from modifying these rows. What is wrong and how to fix it?

medium

A. FOR SHARE locks the entire table; use WHERE clause to limit rows.

B. FOR SHARE is deprecated; use LOCK TABLE instead.

C. FOR SHARE requires an explicit NOWAIT clause to lock rows.

D. FOR SHARE locks rows only for reading; replace it with FOR UPDATE to lock for writing.

Solution

1. Step 1: Identify the locking behavior of FOR SHARE

   FOR SHARE locks rows to allow reading but does not prevent other transactions from modifying them.

2. Step 2: Choose correct clause to prevent modifications

   To block other transactions from modifying rows, use FOR UPDATE instead of FOR SHARE.

3. Final Answer:

   FOR SHARE locks rows only for reading; replace it with FOR UPDATE to lock for writing. -> Option D

4. Quick Check:

   Prevent writes = use FOR UPDATE [OK]

Hint: Use FOR UPDATE to block writes, not FOR SHARE [OK]

Common Mistakes:

• Thinking FOR SHARE blocks writes
• Using LOCK TABLE unnecessarily
• Missing WHERE clause but unrelated to locking type

5. You want to update multiple rows in a table but ensure no other transaction can read or modify these rows until your transaction finishes. Which locking clause should you use in your SELECT statement before updating?

hard

A. SELECT * FROM table_name FOR UPDATE;

B. SELECT * FROM table_name FOR SHARE;

C. SELECT * FROM table_name FOR NO KEY UPDATE;

D. SELECT * FROM table_name FOR KEY SHARE;

Solution

1. Step 1: Understand the locking levels

   FOR UPDATE locks rows exclusively, blocking writes by others until commit, but does not block reads.

2. Step 2: Compare with other lock types

   FOR SHARE and FOR KEY SHARE allow reads; FOR NO KEY UPDATE is less restrictive and allows some concurrent updates.

3. Step 3: Choose the strictest lock to block writes

   Only FOR UPDATE fully blocks other transactions from modifying the rows, but it does not block reads.

4. Final Answer:

   SELECT * FROM table_name FOR UPDATE; -> Option A

5. Quick Check:

   Block writes = FOR UPDATE [OK]

Hint: FOR UPDATE blocks writes until commit [OK]

Common Mistakes:

• Choosing FOR SHARE or KEY SHARE which allow writes
• Using NO KEY UPDATE which is less strict
• Not understanding lock levels and their effects