SQL Concurrency and Locks | Dataford Interview Questions

They asked me about concurrency/lock problems, how I would work with those requirements.

Using the provided lock request and lock compatibility data, write a SQL query to identify currently waiting requests blocked by granted requests on the same relation. Exclude requests from the same transaction.

Output

One row per waiting and blocking request pair
Columns: waiting_request_id, blocking_request_id, relation_name, waiting_mode, and blocking_mode
Include only incompatible lock pairs, ordered by waiting_request_id, then blocking_request_id

Schema

lock_requests

Column	Type	Description
request_idPK	INT	Unique lock request identifier
transaction_id	INT	Transaction that owns or requests the lock
relation_name	VARCHAR(100)	Database relation being locked
requested_mode	VARCHAR(50)	Requested PostgreSQL lock mode
granted	BOOLEAN	Whether the requested lock has been granted
requested_at	TIMESTAMP	Time at which the lock was requested

lock_mode_compatibility

Column	Type	Description
mode_aPK	VARCHAR(50)	First mode in canonical lexical order
mode_bPK	VARCHAR(50)	Second mode in canonical lexical order
is_compatible	BOOLEAN	Whether the two lock modes can coexist

Your question is SQL Concurrency and Locks. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem