Deadlock detection and prevention in MySQL - Deep Dive

Overview - Deadlock detection and prevention

What is it?

Deadlock detection and prevention is about managing situations where two or more database transactions wait forever for each other to release locks. This causes the system to freeze those transactions, stopping progress. The database system must find these deadlocks and fix them by stopping one transaction so others can continue. Without this, your database could get stuck and stop working properly.

Why it matters

Deadlocks can cause your applications to hang or crash because transactions wait endlessly for resources. If deadlocks are not handled, users may experience slow responses or failures, leading to lost data or unhappy customers. Detecting and preventing deadlocks keeps the database running smoothly and ensures data stays consistent and reliable.

Where it fits

Before learning deadlock detection and prevention, you should understand basic database transactions and locking concepts. After this, you can learn about transaction isolation levels and performance tuning to optimize database concurrency and reliability.

Mental Model

Core Idea

Deadlock detection and prevention is about stopping transactions from waiting forever by identifying circular waits and breaking them early.

Think of it like...

Imagine two people trying to pass through a narrow hallway from opposite ends, each waiting for the other to move first. Neither can pass, so they are stuck. Deadlock detection is like a third person noticing this and asking one to step back so the other can pass.

┌─────────────┐       waits for       ┌─────────────┐
│ Transaction A│ ────────────────▶ │ Transaction B│
└─────────────┘                    └─────────────┘
       ▲                                  │
       │                                  ▼
┌─────────────┐       waits for       ┌─────────────┐
│ Transaction C│ ◀─────────────── │ Transaction D│
└─────────────┘                    └─────────────┘

If a cycle forms (A waits for B, B waits for C, C waits for A), a deadlock occurs.

Build-Up - 7 Steps

1

Foundation

Understanding database locks

Concept: Introduce what locks are and why databases use them.

Databases use locks to control access to data so that multiple users don't change the same data at the same time. A lock can be shared (read) or exclusive (write). When a transaction locks data, others may have to wait until the lock is released.

Result

You know that locks prevent data conflicts by controlling who can read or write data at a time.

Understanding locks is essential because deadlocks happen when transactions wait for locks held by each other.

2

Foundation

What is a deadlock in databases

Concept: Explain the deadlock concept as a cycle of waiting transactions.

A deadlock happens when two or more transactions wait for each other to release locks, creating a cycle. None can proceed because each is waiting for the other. This stops progress and wastes resources.

Result

You can identify deadlocks as circular waits that freeze transactions.

Knowing deadlocks are cycles helps you understand why they cause permanent waiting.

3

Intermediate

How MySQL detects deadlocks

🤔Before reading on: do you think MySQL detects deadlocks proactively or only after transactions wait too long? Commit to your answer.

Concept: MySQL detects deadlocks by checking for cycles in the lock wait graph during transaction execution.

MySQL builds a graph of transactions waiting for locks. When a new lock wait is added, it checks if this creates a cycle. If yes, it detects a deadlock and chooses one transaction to roll back to break the cycle.

Result

Deadlocks are detected quickly, and one transaction is stopped to let others continue.

Understanding MySQL's cycle detection explains why deadlocks are resolved automatically without manual intervention.

4

Intermediate

Deadlock prevention techniques

🤔Before reading on: do you think preventing deadlocks means avoiding all locks or managing lock order? Commit to your answer.

Concept: Deadlock prevention involves strategies like ordering locks consistently and using timeouts to avoid cycles.

One common method is to always acquire locks in the same order across transactions. Another is to use lock timeouts so transactions give up waiting after some time. These reduce the chance of deadlocks forming.

Result

Fewer deadlocks occur, improving database performance and reliability.

Knowing prevention techniques helps design applications that avoid deadlocks rather than just reacting to them.

5

Intermediate

Handling deadlocks in application code

🤔Before reading on: do you think applications should ignore deadlock errors or retry transactions? Commit to your answer.

Concept: Applications must handle deadlock errors by retrying transactions safely.

When MySQL detects a deadlock, it rolls back one transaction and returns an error. Applications should catch this error and retry the transaction because the conflict is temporary.

Result

Applications become more robust and avoid failures caused by deadlocks.

Understanding error handling for deadlocks prevents data loss and improves user experience.

6

Advanced

Deadlock detection internals in InnoDB

🤔Before reading on: do you think InnoDB checks all locks globally or only related locks for deadlocks? Commit to your answer.

Concept: InnoDB uses a lock wait graph limited to transactions waiting on row locks to detect deadlocks efficiently.

InnoDB tracks which transactions wait for which locks on rows. When a transaction waits, InnoDB checks if adding this wait creates a cycle in the graph. It only checks related locks, not all locks, to keep detection fast.

Result

Deadlock detection is efficient and scales well with many transactions.

Knowing InnoDB's selective checking explains why deadlock detection is fast even in busy systems.

7

Expert

Surprising deadlock causes and solutions

🤔Before reading on: do you think read-only transactions can cause deadlocks? Commit to your answer.

Concept: Even read-only transactions can cause deadlocks due to gap locks and lock escalation in repeatable read isolation.

InnoDB uses gap locks to prevent phantom reads, which can cause deadlocks if transactions lock overlapping gaps. Also, lock escalation or foreign key constraints can create unexpected deadlocks. Understanding these helps tune isolation levels or queries to reduce deadlocks.

Result

You can diagnose and fix subtle deadlocks that seem unrelated to writes.

Recognizing non-obvious deadlock causes prevents wasted debugging time and improves database stability.

Under the Hood

Deadlock detection works by building a graph where nodes are transactions and edges represent waiting for locks held by others. The database engine continuously monitors lock requests and waits. When a new wait edge creates a cycle in this graph, a deadlock is detected. The engine then chooses a victim transaction to roll back, breaking the cycle and freeing locks. This process happens inside the storage engine (like InnoDB) at runtime, ensuring minimal delay.

Why designed this way?

This design balances performance and correctness. Checking all locks globally would be too slow, so only relevant waits are tracked. Rolling back one transaction is simpler and safer than trying to reorder or force locks. Early database systems lacked deadlock detection, causing hangs. Modern systems added this to improve reliability and user experience.

┌───────────────┐       waits for       ┌───────────────┐
│ Transaction 1 │ ────────────────▶ │ Transaction 2 │
└───────────────┘                    └───────────────┘
       ▲                                  │
       │                                  ▼
┌───────────────┐       waits for       ┌───────────────┐
│ Transaction 3 │ ◀─────────────── │ Transaction 4 │
└───────────────┘                    └───────────────┘

Cycle detected → choose victim → rollback → cycle broken

Myth Busters - 4 Common Misconceptions

Quick: Do you think deadlocks only happen with write operations? Commit yes or no.

Common Belief: Deadlocks only occur when transactions write data because reads don't block.

Reality: Deadlocks can happen with reads too, especially under repeatable read isolation due to gap locks and locking strategies.

Why it matters: Ignoring read locks can cause missed deadlocks, leading to unexpected transaction failures and confusion.

Quick: Do you think increasing lock wait timeout solves deadlocks? Commit yes or no.

Common Belief: Setting a higher lock wait timeout prevents deadlocks by waiting longer.

Reality: Increasing timeout only delays deadlock detection; it does not prevent deadlocks and can worsen system performance.

Why it matters: Misconfiguring timeouts can cause longer waits and poor user experience without fixing deadlocks.

Quick: Do you think deadlock detection always picks the transaction that caused the deadlock as victim? Commit yes or no.

Common Belief: The transaction that caused the deadlock is always rolled back.

Reality: The database chooses the victim based on factors like transaction age and rollback cost, not just who caused it.

Why it matters: Assuming the cause is rolled back can mislead debugging and recovery strategies.

Quick: Do you think disabling transactions removes deadlocks? Commit yes or no.

Common Belief: If you avoid using transactions, deadlocks won't happen.

Reality: Deadlocks arise from concurrent resource waits, which can still happen in some non-transactional operations or implicit transactions.

Why it matters: Avoiding transactions is not a reliable deadlock solution and risks data inconsistency.

Expert Zone

1

Deadlock victim selection balances rollback cost and transaction importance, which can affect application behavior subtly.

2

Gap locks used for phantom read prevention can cause deadlocks even in read-only transactions under certain isolation levels.

3

Lock ordering is critical but hard to enforce in complex applications with dynamic queries, requiring careful schema and query design.

When NOT to use

Deadlock detection and prevention are essential for transactional databases but less relevant for NoSQL or eventual consistency systems. In high-throughput systems where latency is critical, optimistic concurrency control or versioning may be better alternatives.

Production Patterns

In production, developers use retry logic on deadlock errors, monitor deadlock frequency via logs, and tune queries and indexes to minimize lock contention. Consistent lock ordering and using lower isolation levels when possible are common strategies.

Connections

Operating System Deadlocks

Similar pattern of resource waiting and cycle detection

Understanding OS deadlocks helps grasp database deadlocks since both involve circular waits and resource allocation problems.

Concurrency Control

Builds on locking and transaction management concepts

Deadlock detection is a key part of concurrency control, ensuring multiple operations can safely run at the same time.

Traffic Management

Opposite problem of preventing gridlocks in road intersections

Studying traffic flow and gridlock prevention offers insights into designing systems that avoid deadlocks by controlling resource access order.

Common Pitfalls

#1 Ignoring deadlock errors in application code

Wrong approach:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- No error handling for deadlocks

Correct approach:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If deadlock error occurs, catch it and retry transaction

Root cause:

Developers often forget that deadlocks cause transaction rollbacks and must be handled explicitly to retry safely.

#2 Acquiring locks in inconsistent order across transactions

Wrong approach:

Transaction A locks table X then table Y;
Transaction B locks table Y then table X;

Correct approach:

Both Transaction A and B lock tables in the same order, e.g., table X then table Y;

Root cause:

Inconsistent lock order creates cycles in waiting, causing deadlocks.

#3 Setting lock wait timeout too high to avoid deadlocks

Wrong approach:

SET innodb_lock_wait_timeout = 1000; -- very high timeout

Correct approach:

Use default or reasonable timeout and rely on deadlock detection to resolve conflicts quickly

Root cause:

Misunderstanding that longer waits prevent deadlocks, when they only delay detection and hurt performance.

Key Takeaways

Deadlocks happen when transactions wait in a cycle for each other's locks, causing a standstill.

Databases detect deadlocks by finding cycles in the lock wait graph and resolve them by rolling back one transaction.

Preventing deadlocks involves consistent lock ordering, using timeouts, and designing queries carefully.

Applications must handle deadlock errors by retrying transactions to maintain smooth operation.

Even read-only transactions can cause deadlocks due to locking strategies like gap locks under certain isolation levels.