Skip to content

Interview PrepConcurrency · Advanced

Locks and Transaction Failures ​

30. What is optimistic locking? ​

The short answer

Optimistic locking assumes conflicts are rare, so it takes no lock while a user works. Each row carries a version number or updated_at timestamp. When saving, the update says "only if the version is still the one I read", WHERE id = $1 AND version = $2, and increments it. If someone else saved first, the update matches zero rows, and the app tells the user to reload, or retries. It suits edit forms and APIs where people hold data for seconds or minutes, and HTTP exposes the same idea with ETag and If-Match.

ts
async function renameProduct(id: number, name: string, expectedVersion: number) {
  const res = await pool.query(
    `UPDATE products SET name = $1, version = version + 1
     WHERE id = $2 AND version = $3`,                                           // (1)
    [name, id, expectedVersion])
  if (res.rowCount === 0) throw new HttpError(409, 'Someone else changed this product. Reload and try again.')   // (2)
}
  1. The version check is part of the update. The database checks and writes in one atomic step, so no gap exists for another writer.
  2. Zero rows updated means a conflict, not a missing product, if you already know it exists. Answer 409 Conflict, or 412 Precondition Failed when the client sent If-Match.

The story it prevents. Asha and Ben both open product 7 at 10:00. Asha saves a new price at 10:02, version 3 becomes 4. Ben saves a new description at 10:03 with version 3. Without the check, Ben's save would silently overwrite Asha's price with the old one. With it, Ben's update matches nothing and he is asked to reload (the API version of this).

31. What is pessimistic locking? ​

The short answer

Pessimistic locking assumes conflicts are likely, so it locks the rows first and makes others wait. In PostgreSQL, SELECT … FOR UPDATE inside a transaction locks the selected rows until COMMIT or ROLLBACK; anyone else trying to update or lock them waits. It suits short, high-contention operations such as booking the last seat, moving money between accounts, or claiming a job from a queue. Keep the transaction short, never wait for a user or an external API while holding the lock, and add NOWAIT or a lock timeout so waits cannot pile up.

ts
await client.query('BEGIN')
const { rows: [seat] } = await client.query(
  'SELECT id, status FROM seats WHERE id = $1 FOR UPDATE', [seatId])            // (1)
if (seat.status !== 'free') { await client.query('ROLLBACK'); throw new HttpError(409, 'Seat taken') }
await client.query("UPDATE seats SET status = 'held', held_by = $1 WHERE id = $2", [userId, seatId])
await client.query('COMMIT')                                                    // (2)
  1. Lock the seat row. A second buyer running the same code waits here until the first commits, then sees held.
  2. The lock is released at commit.

A job queue in PostgreSQL with SKIP LOCKED:

sql
UPDATE jobs SET status = 'running', started_at = now()
WHERE id = (
  SELECT id FROM jobs WHERE status = 'queued'
  ORDER BY created_at
  FOR UPDATE SKIP LOCKED                    -- skip rows another worker already locked
  LIMIT 1
)
RETURNING id, payload;

Ten workers run this at once, and each gets a different job immediately instead of queueing behind one row.

OptimisticPessimistic
Lock takenNoneRows locked until commit
Conflict foundAt save timePrevented up front
Best whenConflicts are rare; long think timeConflicts are common; short operations
Failure modeRetries and "please reload"Waiting, timeouts, deadlocks

32. What is a deadlock, and how can it be resolved or prevented? ​

The short answer

A deadlock is when two transactions each hold a lock the other needs, so both wait forever. Transaction A locks row 1 and wants row 2; transaction B locks row 2 and wants row 1. The database detects this cycle, in PostgreSQL after deadlock_timeout, one second by default, and aborts one of them with a deadlock error, so the other can continue. Prevent it by always taking locks in the same order, for example by sorting IDs, keeping transactions short, and locking everything you need up front. Handle it by retrying the aborted transaction.

transaction AAsha pays Bentransaction BBen pays Asharow: Asha's accountlocked by Arow: Ben's accountlocked by B① holds② holds③ A waits for Ben's row④ B waits for Asha's row
Each holds what the other needs. ① and ② each transaction locks its first row. ③ and ④ each then waits for the row the other holds. Neither can finish, so after a second the database aborts one, and that one must be retried.

Prevention: always lock in the same order.

ts
async function transfer(fromId: number, toId: number, amount: number) {
  const [first, second] = [fromId, toId].sort((a, b) => a - b)                  // (1)
  await client.query('BEGIN')
  await client.query('SELECT id FROM accounts WHERE id = ANY($1) ORDER BY id FOR UPDATE', [[first, second]])   // (2)
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId])
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, toId])
  await client.query('COMMIT')
}
  1. Sort the account IDs, so Asha-to-Ben and Ben-to-Asha both lock the lower ID first.
  2. Lock both rows up front, in ID order. The second transfer now waits at the first lock instead of forming a cycle.

Other prevention rules: keep transactions short, touch tables in a consistent order across the codebase, add the indexes that let updates lock only the rows they need, and set lock_timeout so a stuck wait fails quickly.

33. How do database transactions fail, and how should an application respond? ​

The short answer

Transactions fail for two kinds of reasons. Retryable failures are temporary: a deadlock, a serialization failure at Serializable or Repeatable Read, a lock timeout, a dropped connection, or a database failover. The right response is to roll back and run the whole transaction again, with a short random delay and a limit. Permanent failures will fail again: a constraint violation such as a duplicate email, bad input, or a check failing. Turn those into a clear 400 or 409 for the user. Never retry blindly a transaction whose commit result you did not see, unless it is idempotent.

ts
const RETRYABLE = new Set(['40001', '40P01', '55P03'])                          // (1)

export async function withTransaction<T>(work: (c: PoolClient) => Promise<T>, attempts = 3): Promise<T> {
  for (let i = 1; ; i++) {
    const client = await pool.connect()
    try {
      await client.query('BEGIN')
      const result = await work(client)                                         // (2)
      await client.query('COMMIT')
      return result
    } catch (err: any) {
      await client.query('ROLLBACK').catch(() => {})
      if (!RETRYABLE.has(err.code) || i === attempts) throw err                 // (3)
      await sleep(20 * 2 ** i + Math.random() * 50)                             // (4)
    } finally {
      client.release()
    }
  }
}
  1. PostgreSQL error codes that mean "try again": 40001 serialization failure, 40P01 deadlock detected, 55P03 lock not available.
  2. The whole unit of work runs inside, so a retry repeats every statement, not just the last one.
  3. Anything else, or the last attempt, is thrown to the caller.
  4. Wait a little longer each time, with randomness, so two retrying transactions do not collide again in lockstep.
ErrorCodeResponse
Unique violation23505409 Conflict: "email already registered"
Foreign key violation23503400 or 409: referenced item does not exist
Check violation23514400: invalid value
Serialization failure, deadlock40001, 40P01Retry the whole transaction
Connection lost during COMMITUnknown outcome: check, or rely on an idempotency key

The unknown-outcome case. If the connection drops while COMMIT is in flight, the transaction may or may not have committed. Retrying a payment then risks charging twice. Make such operations idempotent, for example with a unique idempotency_key column, so a retry either succeeds or finds the earlier result (idempotency keys).

Combine chapters into one PDF

Pick the chapters you want. They print in the order you pick them.

Interview Prep
Databases & Caching
Foundations
SQL
Performance
Concurrency
Scaling
Caching
Other topics
Nothing picked yet