Skip to content
RED SAGSkills
hardDatabase Administrator interview question

The application is timing out and you suspect lock contention in production. Walk through how you diagnose and fix it.

What the interviewer wants to hear

  • Find blocked and blocking sessions using pg_locks / pg_blocking_pids or performance_schema
  • Identify the root blocker, often an idle in transaction session or a DDL
  • Decide whether to cancel or terminate it safely
  • Check deadlock logs
  • Prevent recurrence: timeouts, short transactions, consistent lock order, lock_timeout for DDL

Model answer

I look at pg_stat_activity together with pg_blocking_pids() in PostgreSQL, or performance_schema.data_lock_waits and the sys.innodb_lock_waits view in MySQL, to build the blocking tree. Usually there is one root blocker. Common culprits are a session that is idle in transaction holding row locks after an app bug, a long batch update, or an ALTER TABLE waiting for an exclusive lock, which then blocks every query behind it in the queue. If the root blocker is safe to stop, I cancel it with pg_cancel_backend or end it with pg_terminate_backend, after checking with the owning team. Then I prevent repeats: keep transactions short, set idle_in_transaction_session_timeout and statement_timeout, always use lock_timeout for DDL, update rows in a consistent order, and review the deadlock entries in the logs.

Can you answer it under time pressure?

Take a timed hard round and get scored on every answer.

Start mock interview

More hard Database Administrator questions