MySmartCoPilot
Ask Passes Your pass 7 days left 1 day left Last day

Your country

Tools that support it use your country for local currency, number formats, units and paper size. Your choice is saved only in this browser.

Database Management Systems (DBMS) Module 12

Concurrency control interview questions

Interview questions on concurrency control: short warm-ups, then what each harder question tests, the first step of a good answer and its follow-ups.

22 questions: 5 warm-ups and 17 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

  1. Fresher

    What does two-phase locking guarantee, and what does it not?

    Answered in the lesson Locking and two-phase locking (2PL)

  2. Fresher to mid

    How do strict and rigorous two-phase locking differ?

    Answered in the lesson Locking and two-phase locking (2PL)

  3. Fresher

    What is a wait-for graph, and how does a database use it to handle deadlocks?

    Answered in the lesson Deadlocks: detection, prevention and timeouts

  4. Fresher to mid

    Why do databases use intention locks such as IS and IX?

    Answered in the lesson Multiple-granularity locking and phantoms

  5. Fresher to mid

    What do xmin and xmax mean on a PostgreSQL row version?

    Answered in the lesson MVCC and snapshot isolation inside PostgreSQL

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

  1. Mid to senior

    A batch job moves money between accounts, one transaction per transfer. To cut waiting, someone changes it to release each row's exclusive lock right after updating the row instead of at commit. What can go wrong now, and how would you demonstrate each failure?

    What it tests
    Dirty reads, cascading aborts and unrecoverable schedules from releasing exclusive locks early: the difference between basic and strict 2PL in a running system.
    First step
    Say what a released exclusive lock exposes: another transaction can read or overwrite a value whose writer can still roll back. Then build the smallest schedule that shows it: the job updates A and releases it, a reader reads A and commits, and the job then aborts.
    Follow-ups

    From the lesson Locking and two-phase locking (2PL)

    Opening the model answers with your pass…

    Model answers open with any MySmartCoPilot pass.

    • Pro pass · 1 week₹79$2.99
    • 30 days₹179$5.99
    • 1 year₹1,699$45

    One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

    Choose a passI have a licence key

    It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  2. Mid to senior

    An order service locks the customer row and every product row it reads, and holds each lock until commit. During a flash sale on one product, throughput collapses and most requests time out. What is happening, and what would you change without losing correctness?

    What it tests
    Contention on one hot row under strict or rigorous 2PL, throughput as one over the lock hold time, and ways to shorten or split the hold without allowing oversold stock.
    First step
    Every order for the product queues on one exclusive row lock, so the sale runs one order at a time. Measure how long each transaction holds that lock: if it also waits 150 ms for a payment call, the row admits at most about 6.7 orders a second, however many servers there are.
    Follow-ups

    From the lesson Locking and two-phase locking (2PL)

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  3. Mid to senior

    Your booking service logs a few deadlock errors (SQLSTATE 40P01) an hour, always between the code that books a group of seats and the code that moves an existing booking. How do you find the cause and remove it, and what do you do until the fix ships?

    What it tests
    Reading deadlock evidence, tracing it to two code paths that lock the same rows in different orders, fixing the order, and retrying the victim safely in the meantime.
    First step
    Turn on log_lock_waits and read the server log around each error. For both transactions, list the rows each one locked and in what order. If the booking path locks seats in the order the user clicked them and the move path in the order the booking stores them, the two orders cross.
    Follow-ups

    From the lesson Deadlocks: detection, prevention and timeouts

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  4. Fresher to mid

    A system uses wait-die to prevent deadlocks. A long transaction that builds a report keeps getting aborted, and every time it restarts it is aborted again. What could cause this, how does restarting with the original timestamp change it, and what does it still cost?

    What it tests
    How wait-die decides at each request, why it aborts transactions that were never deadlocked, starvation and the restart timestamp, and the work thrown away.
    First step
    State the rule first: a younger transaction that asks for a lock an older one holds is aborted. If the report restarts with a new timestamp, it is the youngest transaction every time, so every older holder of a row it needs kills it again.
    Follow-ups

    From the lesson Deadlocks: detection, prevention and timeouts

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  5. Senior to staff

    Your team wants to switch off InnoDB's deadlock detection and rely on a 5-second lock wait timeout, to save CPU on a server with thousands of concurrent connections. When is that a good trade, and when does it hurt?

    What it tests
    The cost of deadlock detection under high concurrency, what a timeout does instead, statement against transaction rollback, and measuring before changing anything.
    First step
    Compare what each side costs. Detection searches the wait-for graph whenever a transaction starts to wait, which gets expensive when thousands of threads queue for one hot row. A timeout costs nothing until it fires, but every real deadlock then stalls its transactions for the full 5 seconds.
    Follow-ups

    From the lesson Deadlocks: detection, prevention and timeouts

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  6. Mid to senior

    A nightly report takes a shared lock on the whole orders table to get consistent totals, and order inserts stall for its full 40 minutes. What is happening in lock terms, and how would you produce consistent totals without stopping the writers?

    What it tests
    Table-level shared locks against the intention locks of writers, the cost of a coarse lock, and alternatives: snapshot reads, a replica, or smaller pieces of work.
    First step
    Name the conflict first: every insert takes IX on the table before it locks its new row, and IX is incompatible with the report's S lock, so each insert waits for the report to commit. Then ask what the report actually needs, which is one consistent view of the table, not a lock on it.
    Follow-ups

    From the lesson Multiple-granularity locking and phantoms

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  7. Mid to senior

    A cleanup job deletes three months of old rows from a busy table in one transaction. Halfway through, every other query on the table starts to wait, and the engine reports that it escalated the job's row locks to a table lock. Explain what happened and how you would change the job.

    What it tests
    Lock escalation and its thresholds, why one long transaction collects so many locks, and deleting in small transactions instead.
    First step
    Count the locks. Deleting a million rows in one transaction means a million row locks, which in SQL Server take about 100 bytes each. Past the engine's threshold it swaps them for one exclusive table lock, and from then on every transaction that needs the table waits until the job commits.
    Follow-ups

    From the lesson Multiple-granularity locking and phantoms

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  8. Mid to senior

    A doctor may work at most three night shifts a week. The service counts the doctor's shifts for the week and inserts a new one if the count is below three. Under load, some doctors get four. Why did locking the rows it counted not prevent this, and how would you fix it?

    What it tests
    Phantoms from inserts that match a query's condition, why row locks cannot cover rows that do not exist yet, and fixes: a lock on a parent row, serializable isolation, or a constraint.
    First step
    Write the race as a schedule: both requests count two shifts, both insert, both commit. The rows they counted never changed, so locks on them conflict with nothing; the conflict is on the condition doctor = D and week = W, which only a predicate, gap or coarser lock covers.
    Follow-ups

    From the lesson Multiple-granularity locking and phantoms

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  9. Fresher to mid

    Thomas's write rule lets the database silently skip a transaction's write. Why is that safe, and what kind of schedule does it accept that basic timestamp ordering rejects? Use the smallest example you can.

    What it tests
    The timestamp-ordering rules, obsolete writes, the difference between conflict and view serializability, and blind writes.
    First step
    Take R1(Q) W2(Q) W1(Q). Basic ordering aborts T1 at its write, because T2, a younger transaction, has already written Q. Then ask what T1's write would achieve in the timestamp order T1, T2: T2's value would overwrite it, so no transaction could ever read it.
    Follow-ups

    From the lesson Timestamp ordering and optimistic control

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  10. Mid to senior

    An inventory service updates stock with optimistic version checks. Normally under 1 % of updates retry, but during a sale on a few products 70 % of attempts fail and latency explodes. Explain the mechanism with numbers, and what you would change for the hot products.

    What it tests
    How optimistic control degrades under contention, expected attempts and the extra load of retries, and moving hot items to single conditional updates or short locks.
    First step
    Put a number on it: if a fraction p of attempts fails, a sale needs 1 / (1 - p) attempts on average, so at p = 0.7 each sale costs about 3.3 attempts. Every retry also reads and writes the same hot rows again, which pushes p higher still.
    Follow-ups

    From the lesson Timestamp ordering and optimistic control

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  11. Mid to senior

    A mobile app edits a user's profile offline and syncs later, and the same profile can be edited on two devices. Design the optimistic concurrency check across the API, and say what the client should do on a conflict.

    What it tests
    Optimistic control across requests: version numbers or ETags with If-Match, the 412 response, and resolving conflicts without losing anyone's edit.
    First step
    Give every profile a version that the server increments on each change. The client sends the version it last read with each update; the server applies the update only if that version still matches, in one conditional UPDATE, and otherwise reports a conflict instead of overwriting.
    Follow-ups

    From the lesson Timestamp ordering and optimistic control

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  12. Mid to senior

    A PostgreSQL table holds a steady two million rows, yet over a week it grows from 2 GB to 40 GB on disk, and autovacuum runs regularly. What do you suspect, how do you confirm it, and how do you fix it and get the space back?

    What it tests
    The vacuum horizon and what holds it back, finding it in the system views, and the difference between making space reusable and returning it to the operating system.
    First step
    Dead versions are piling up faster than VACUUM removes them, and when VACUUM runs regularly the usual reason is that it may not remove them: an old snapshot holds the horizon back. Look for the oldest backend_xmin in pg_stat_activity, then check replication slots and prepared transactions.
    Follow-ups

    From the lesson MVCC and snapshot isolation inside PostgreSQL

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  13. Senior to staff

    Your PostgreSQL logs warn that a database must be vacuumed within 10 million transactions. Explain what is happening, how much time you have, and what you do now and afterwards.

    What it tests
    Transaction ID wraparound and freezing, the stop point three million IDs before it, and the documented recovery steps, without single-user mode or VACUUM FULL.
    First step
    Work out the time first. The server refuses new transaction IDs once only three million are left, so the real budget is about seven million IDs; at 500 a second that is about four hours. Then find out why freezing has not kept up, because a plain VACUUM may not be able to finish while the cause remains.
    Follow-ups

    From the lesson MVCC and snapshot isolation inside PostgreSQL

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  14. Senior to staff

    An update-heavy table also serves hour-long analytical queries. Compare what this costs on PostgreSQL and on MySQL's InnoDB, given how each stores old row versions, and what you would tune on each.

    What it tests
    Versions in the heap against undo-log deltas, where each engine's costs land, such as bloat, index churn, undo growth or slow old reads, and the tuning levers of each.
    First step
    Start from where old versions live. PostgreSQL writes a whole new version per update and cannot vacuum versions newer than the oldest open snapshot, so an hour-long query bloats the table. InnoDB updates in place and keeps undo records, so the same query makes the undo history grow and must rebuild old versions as it reads.
    Follow-ups

    From the lesson MVCC and snapshot isolation inside PostgreSQL

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  15. Mid to senior

    A service runs its transactions at SERIALIZABLE on PostgreSQL. When a statement fails with SQLSTATE 40001, a colleague's code retries just that statement. Why is that wrong, and what should a correct retry wrapper do?

    What it tests
    What a serialization failure means, why the reads and decisions must run again, transient against lasting errors, backoff and caps, and idempotent side effects.
    First step
    Say what 40001 means: the transaction's reads came from a snapshot whose results cannot be fitted into a serial order with the transactions it overlapped. Every value it read, and every decision it made from them, may be stale, and the failed transaction is aborted anyway, so only a fresh run of the whole function can succeed.
    Follow-ups

    From the lesson Serializable snapshot isolation and retries

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  16. Senior to staff

    After a busy PostgreSQL service moves to SERIALIZABLE, 3 % of its transactions fail with 40001, and most of the failures look harmless. Where do they come from, and how would you bring the rate down without giving up serializability?

    What it tests
    How SSI's predicate locks and dangerous structures cause false positives, which workload features inflate them, and the documented ways to cut them.
    First step
    Separate real conflicts from false alarms. SSI aborts on a pattern of read-write dependencies, not on a proven cycle, and its SIRead locks cover what was read at the granularity of the plan: a sequential scan locks the whole table. Check the failing queries' plans and the lock promotion before blaming the workload.
    Follow-ups

    From the lesson Serializable snapshot isolation and retries

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  17. Mid to senior

    Design a jobs table in PostgreSQL for 20 workers so that no job runs twice and workers do not wait for each other. What does a worker's claim look like, and what happens when a worker crashes in the middle of a job?

    What it tests
    FOR UPDATE SKIP LOCKED, short claim transactions against long ones, leases and crash recovery, at-least-once processing and idempotency.
    First step
    Claim with one short statement: lock the oldest due job with FOR UPDATE SKIP LOCKED, mark it running with a lease that expires, say in five minutes, and commit at once. Then process outside any transaction, and mark the job done in a second short transaction.
    Follow-ups

    From the lesson Serializable snapshot isolation and retries

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

All Database Management Systems (DBMS) interview questions · The Database Management Systems (DBMS) track