Row locks coordinate conflicting changes to selected records. Lock scope, acquisition order and transaction duration affect contention.
Before you start
You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.
The practical goal is to reason through this situation: Lock a balance row while validating and applying a transfer. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: Identify protected rows
Choose the records whose coordinated change preserves the invariant.
Step 2: Acquire in consistent order
Transfers locking account A then B can conflict with reverse acquisition.
Step 3: Shorten the locked interval
Avoid unrelated remote waits and retain deadlock handling.
Worked scenario
Lock a balance row while validating and applying a transfer.
Transfer one locks A then waits for B, while transfer two locks B then waits for A. Consistent ordering by account ID reduces this cycle. It does not remove every possible deadlock, so detected failures still need a bounded safe retry policy for the whole transaction.
Common mistake
Waiting on remote services while holding locks increases contention.
Verify the behavior
Run opposite-direction transfers and inspect lock waits, invariant preservation and retry behavior.
Interview exercise
Reduce deadlock risk.
Answer and reasoning
Acquire related locks in a consistent order, keep transactions short and retain retry handling for detected deadlocks.
Continue learning
Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.