Locking & Deadlocks — Series 2

Preview — 3 of 10 questions

What's the difference between SELECT ... FOR SHARE and SELECT ... FOR UPDATE?

javascript

-- Transaction A:
BEGIN;
SELECT * FROM products WHERE id = 1 FOR SHARE;

-- Transaction B (concurrently):
SELECT * FROM products WHERE id = 1 FOR SHARE;
-- OK — both can hold a shared lock on the same row simultaneously

-- Transaction C (concurrently):
UPDATE products SET price = 10 WHERE id = 1;
-- BLOCKS — an exclusive-intent write conflicts with the shared locks held by A and B

AFOR SHARE acquires a shared row lock that allows multiple transactions to hold it simultaneously (blocking only writers, not other FOR SHARE readers); FOR UPDATE acquires an exclusive lock that blocks all other lockers, including other FOR SHARE and FOR UPDATE attempts

BFOR SHARE and FOR UPDATE are functionally identical, just different keywords for the same lock

CFOR SHARE only works on views, never on base tables

DFOR UPDATE allows multiple simultaneous holders, while FOR SHARE only allows one

PostgreSQL has finer-grained lock modes FOR NO KEY UPDATE and FOR KEY SHARE, in addition to FOR UPDATE/FOR SHARE. What problem do they solve?

javascript

-- Transaction A: updates a customer's phone number (not a key column)
UPDATE customers SET phone = '555-1234' WHERE id = 1;
-- takes a FOR NO KEY UPDATE-strength lock automatically

-- Transaction B: inserts an order referencing this customer
INSERT INTO orders (customer_id) VALUES (1);
-- takes a FOR KEY SHARE-strength lock on customers.id = 1 to verify it exists
-- FOR NO KEY UPDATE and FOR KEY SHARE are COMPATIBLE — B does not block on A

AThey are deprecated aliases for FOR UPDATE and FOR SHARE kept only for backward compatibility

BAn UPDATE that doesn't touch any column referenced by a foreign key only needs a FOR NO KEY UPDATE-strength lock, and a foreign key check on a referencing table only needs a FOR KEY SHARE-strength lock — these narrower modes are compatible with each other, whereas the older, coarser FOR UPDATE/FOR SHARE were not, reducing false lock conflicts between ordinary updates and concurrent FK checks

CThey allow locking rows in a different table than the one being queried

DThey exist purely for query planner hints and have no actual locking effect

Transaction A does SELECT * FROM parent WHERE id = 1 FOR UPDATE; then later tries INSERT INTO child (parent_id) VALUES (2);. Transaction B concurrently does SELECT * FROM parent WHERE id = 2 FOR UPDATE; then tries INSERT INTO child (parent_id) VALUES (1);. What can happen?

javascript

A: FOR UPDATE parent(1)        -- holds lock on parent row 1
B: FOR UPDATE parent(2)        -- holds lock on parent row 2
A: INSERT child(parent_id=2)   -- needs FK-check lock on parent row 2 → BLOCKS (B holds it)
B: INSERT child(parent_id=1)   -- needs FK-check lock on parent row 1 → BLOCKS (A holds it)
→ circular wait → PostgreSQL's deadlock detector aborts one transaction

AThis scenario cannot cause a deadlock since inserts don't take any locks on the parent table

BPostgreSQL silently serializes all INSERT statements referencing a foreign key, making this scenario impossible

CA deadlock: A holds a lock on parent row 1 and needs a FK-check-related lock on parent row 2 (via the INSERT); B holds a lock on parent row 2 and needs one on parent row 1 — a classic circular wait that PostgreSQL's deadlock detector will identify and resolve by aborting one transaction

DOnly UPDATE statements can participate in deadlocks; INSERT statements are always deadlock-free

Answer all 10 questions (7 more), see explanations for every answer, and track your score.