Deadlocks and blocking look similar in a dashboard — queries stuck, latency climbing, transactions piling up — but the database resolves them differently, and so must you. Adding retry logic when you have a blocking problem won’t help. Investigating lock contention when you have a long-running transaction holding locks will send you down the wrong path entirely. These are two distinct failure modes. Treating them as one is how engineers waste hours in incident response.

Situation

Row-level locking is how relational databases protect concurrent writes. Any transaction that modifies a row acquires a lock on it; others that need the same row wait. This is expected behavior — not a bug — and for most workloads it resolves quickly as transactions commit or roll back.

Lock problems surface when that assumption breaks: a transaction holds a lock longer than expected, two transactions each wait for what the other holds, or a missing index forces the database to lock far more rows than necessary. The symptoms look similar from the outside — stalled queries, timeouts, connection pool pressure — but the causes and correct responses are completely different.

The Problem

Engineers see “lock wait timeout exceeded” or a deadlock error, conclude there is a locking problem, and apply whatever fix they read about most recently — retry logic, a lock_timeout change, an index. Any of those might be wrong for the actual problem present.

Blocking and deadlocks have different root causes, different detection mechanisms, and different remediation paths. Applying deadlock fixes to a blocking problem — or vice versa — obscures the real signal and delays finding the actual cause.

The core question: given a stalled transaction or a lock error, how do you determine which condition you have, and what do you do about each one?

Core Concept

These are not the same condition expressed at different severity levels. They are structurally different.

Blocking is one transaction waiting for a lock held by another. The waiter sits until the holder commits or rolls back — no automatic resolution occurs. The database waits indefinitely (or until a lock_timeout fires). The fix is almost always about the holder: find it, understand why it’s holding the lock longer than expected, and address that.

A deadlock is a cycle. Transaction A holds lock X and waits for lock Y. Transaction B holds lock Y and waits for lock X. Neither can proceed. PostgreSQL and MySQL InnoDB detect this automatically via a wait-for graph, pick one transaction as the victim, and terminate it — the other proceeds. Deadlocks resolve themselves; the application must handle the error and retry. The fix is about eliminating the cycle, typically by acquiring locks in a consistent order across transactions.

flowchart TD
    subgraph Blocking [Blocking — Linear Wait]
        T1[Transaction A] -->|Holds Lock| R1[Row 1]
        T2[Transaction B] -->|Waits for Lock| R1
    end

    subgraph Deadlock [Deadlock — Circular Wait]
        T3[Transaction C] -->|Holds Lock| R2[Row 2]
        T4[Transaction D] -->|Holds Lock| R3[Row 3]
        T3 -->|Waits for Lock| R3
        T4 -->|Waits for Lock| R2
    end
BlockingDeadlock
CauseOne transaction holds a lock another needsTwo transactions each wait for what the other holds
ResolutionManual — requires the holder to commit or roll backAutomatic — database detects the cycle and kills one victim
Error surfacedlock_timeout if configured; otherwise the query just waitsExplicit deadlock error (PostgreSQL: ERROR: deadlock detected; MySQL: ERROR 1213: Deadlock found)
Correct responseFind and address the long-running transactionHandle the error in the application; fix lock ordering
Where to lookpg_stat_activity (PostgreSQL); SHOW ENGINE INNODB STATUS (MySQL)PostgreSQL server log; MySQL SHOW ENGINE INNODB STATUS

PostgreSQL detection: pg_stat_activity surfaces every session currently blocked on a lock via SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event_type = 'Lock';. Deadlocks are logged at ERROR level in the server log.

MySQL InnoDB detection: SHOW ENGINE INNODB STATUS\G includes a LATEST DETECTED DEADLOCK section showing the two transactions, the locks held and waited for, and which was rolled back as the victim. For blocking, information_schema.INNODB_LOCK_WAITS shows live lock waits.

Lock timeout vs deadlock detection are separate mechanisms. lock_timeout (PostgreSQL) and innodb_lock_wait_timeout (MySQL) abort a waiting transaction after a configured interval — that is a timeout, not a deadlock. Deadlock detection runs independently on the server side regardless of timeout settings. A blocking event terminated by a timeout was never a deadlock; the application log error codes differ accordingly.

Row-level vs table-level locking: missing indexes force broader locks. A DELETE WHERE status = 'pending' without an index on status may escalate to a table lock in InnoDB rather than acquiring row locks for only matching rows — turning a narrow delete into a blocking event for every other writer on that table.

In Practice

PostgreSQL’s lock management documentation describes the wait-for graph approach: “PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete.” It explicitly recommends consistent lock ordering as the prevention strategy (https://www.postgresql.org/docs/current/explicit-locking.html).

MySQL’s InnoDB deadlock documentation draws a sharp distinction from lock wait timeouts: a lock wait timeout rolls back only the current SQL statement, whereas a deadlock detection event rolls back the entire transaction (https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html). That distinction matters for application retry logic — a partial statement rollback and a full transaction rollback require different recovery paths.

The documented pattern from both systems: deadlock handling belongs in the application layer with a full-transaction retry. Blocking calls for operational investigation — find the long-running holder and address it at source.

Where It Breaks

ScenarioWhat breaksWhy
ORM batch inserts without consistent row orderingDeadlocks under concurrent batch operationsTwo batches inserting the same rows in different orders create lock cycle; ORM doesn’t guarantee insertion order
Missing index on a filtered column used in writesBlocking affects all writers to the table, not just contended rowsNo row-level lock available, so InnoDB or PostgreSQL acquires a broader lock than necessary
Connection pool holding open transactionsLong-running blocking events that appear intermittentIdle connections holding uncommitted transactions keep locks live; the blocking appears random because it follows the pool’s transaction lifecycle, not the application’s

What to Do Next

  • Problem: Engineers apply the wrong fix because blocking and deadlocks produce similar symptoms but have structurally different causes and resolution paths.
  • Solution: Identify which condition you have first — use pg_stat_activity or SHOW ENGINE INNODB STATUS to determine whether a lock cycle or a long-running holder is the root cause — then respond accordingly.
  • Proof: If pg_stat_activity shows one session in Lock wait state with a single blocking pid, you have blocking. If the PostgreSQL log shows ERROR: deadlock detected or MySQL reports a deadlock in SHOW ENGINE INNODB STATUS, you have a deadlock.
  • Action: This week, add lock_timeout = '5s' (PostgreSQL) or lower innodb_lock_wait_timeout (MySQL) to surface blocking events that would otherwise wait silently, and confirm your application explicitly handles the 40P01 error code (PostgreSQL deadlock) with a retry path.