Row-level locking (FOR UPDATE, FOR SHARE) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What rows are locked by FOR UPDATE?

Consider a table accounts with columns id and balance. Which rows will be locked by this query?

SELECT * FROM accounts WHERE balance > 1000 FOR UPDATE;

PostgreSQL

CREATE TABLE accounts (id SERIAL PRIMARY KEY, balance INT);
INSERT INTO accounts (balance) VALUES (500), (1500), (2000);

A Only rows with balance > 1000 are locked for update.

B All rows in the table are locked for update.

C No rows are locked; FOR UPDATE does not lock rows.

D Only rows with balance <= 1000 are locked for update.

💡 Hint

FOR UPDATE locks rows returned by the query to prevent concurrent updates.

The FOR UPDATE clause locks only the rows returned by the query. Here, only rows with balance > 1000 are locked.

❓ query_result

intermediate

What is the effect of FOR SHARE?

Given the table orders with columns order_id and status, what does this query do?

SELECT * FROM orders WHERE status = 'pending' FOR SHARE;

PostgreSQL

CREATE TABLE orders (order_id SERIAL PRIMARY KEY, status TEXT);
INSERT INTO orders (status) VALUES ('pending'), ('shipped'), ('pending');

A Locks rows with status 'pending' to prevent other transactions from updating or deleting them.

B Locks rows with status 'pending' to prevent other transactions from reading them.

C Locks all rows in the table regardless of status.

D Does not lock any rows; FOR SHARE is ignored.

💡 Hint

FOR SHARE locks rows to prevent updates or deletes but allows reads.

The FOR SHARE clause locks the selected rows to prevent other transactions from modifying or deleting them, but it allows concurrent reads.

📝 Syntax

advanced

Identify the syntax error in this locking query

Which option contains a syntax error when trying to lock rows for update?

SELECT * FROM products WHERE price > 100 FOR UPDATE NOWAIT;

A SELECT * FROM products WHERE price > 100 FOR UPDATE NOWAIT;

B SELECT * FROM products WHERE price > 100 FOR UPDATE OF products NOWAIT;

C SELECT * FROM products WHERE price > 100 FOR SHARE NOWAIT;

D SELECT * FROM products WHERE price > 100 FOR UPDATE WAIT;

💡 Hint

Check the valid locking options and keywords in PostgreSQL.

PostgreSQL supports NOWAIT but not WAIT as a locking option. Option D uses an invalid keyword WAIT, causing a syntax error.

❓ optimization

advanced

Which query minimizes locking duration?

You want to lock only necessary rows for update to reduce lock contention. Which query is best?

PostgreSQL

CREATE TABLE inventory (item_id SERIAL PRIMARY KEY, quantity INT);
INSERT INTO inventory (quantity) VALUES (10), (0), (5);

A SELECT * FROM inventory FOR SHARE;

B SELECT * FROM inventory WHERE quantity > 0 FOR UPDATE;

C SELECT * FROM inventory WHERE quantity <= 0 FOR SHARE;

D SELECT * FROM inventory FOR UPDATE;

💡 Hint

Lock only rows you intend to update.

Query A locks only rows with quantity > 0 for update, minimizing locked rows and reducing contention.

🧠 Conceptual

expert

What happens if two transactions lock the same row with FOR UPDATE?

Transaction A and Transaction B both execute SELECT * FROM employees WHERE id = 1 FOR UPDATE; at the same time. What happens?

PostgreSQL

CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO employees (name) VALUES ('Alice');

A Both transactions acquire the lock simultaneously without waiting.

B Transaction B immediately fails with a deadlock error.

C Transaction B waits until Transaction A commits or rolls back before acquiring the lock.

D Transaction A releases the lock immediately after selecting the row.

💡 Hint

Consider how row-level locks serialize concurrent updates.

When two transactions try to lock the same row with FOR UPDATE, the second waits until the first finishes (commits or rolls back) to maintain data consistency.