SQL · Queries that survive scale
How do database locks work and how do you avoid contention?
Short answer
Databases use row, page, and table locks to coordinate concurrent writes. Lock only what you must, keep transactions short, and choose a consistent lock order to reduce blocking.
Most engines combine row-level locks with MVCC. Ordinary reads usually do not block writers, but writes take locks and conflicting writes queue. Explicit locking such as SELECT FOR UPDATE is useful when a read must be protected before a later update.
BEGIN;
SELECT id, balance
FROM accounts
WHERE id = 1
FOR UPDATE;
UPDATE accounts
SET balance = balance - 50
WHERE id = 1;
COMMIT;Ways to reduce contention
- Keep transactions short and avoid external calls while holding locks
- Lock rows in a consistent order, such as ascending primary key
- Prefer row locks over table locks
- Use SKIP LOCKED for queue-like workers that can take the next available row
- Consider optimistic concurrency with a version column for low-contention updates
- Set lock timeouts so a stuck transaction fails instead of blocking indefinitely
- Use advisory locks only when the lock is tied to application logic, not row identity
Pessimistic locking prevents conflicts upfront but reduces concurrency. Optimistic concurrency allows more parallel work but requires handling version conflicts and retries. The right choice depends on conflict frequency and the cost of retrying.
Follow-ups you should expect
Interviewers use these to check whether the first answer was understanding or memorisation.
- 1What is the difference between optimistic and pessimistic locking?
- 2When would you use SELECT FOR UPDATE SKIP LOCKED?
- 3How do lock timeouts help?
- 4Why can an idle transaction block other work?
Say it in the interview
Databases coordinate concurrent changes with row, page, and table locks, often alongside MVCC for reads. I use explicit row locks when a value must stay stable between a read and a write, and keep the transaction short. To reduce contention I lock in a consistent order, avoid table locks, and sometimes use optimistic concurrency with a version column. I also set timeouts so stuck transactions fail instead of blocking the system.
Use this as a model, not a script. Saying it in your own words is what makes it survive follow-up pressure.
Related questions
Reading is step one
Now say it out loud.
Understanding an answer and delivering it under pressure are different skills. Rehearse this topic in a live SQL mock interview and get scored feedback on depth, clarity and confidence.