Deadlock Definition

A deadlock refers to a state in concurrent transactions where multiple transactions each hold some resources and are simultaneously waiting for resources held by the other party, leading to circular waiting, and ultimately all related transactions cannot continue execution.

  • Simple understanding: Two transactions block each other, you wait for me to release, I wait for you to release, so neither can proceed.

Necessary Conditions

The occurrence of a deadlock usually requires the following four conditions to be met simultaneously (if any one is not met, there will be no deadlock):

  • Mutual exclusion condition: A resource can only be held by one transaction at a time (such as table locks, row locks).
  • Non-preemption condition: Resources already allocated to a transaction cannot be forcibly taken away; they can only be released by the transaction itself.
  • Partial allocation condition (request and hold condition): A transaction has already acquired some resources but is still applying for new resources.
  • Circular wait condition: There is a transaction waiting queue forming a cycle: T1 waits for T2’s resources, T2 waits for T3’s resources, T3 waits for T1’s resources.

Table Lock Deadlock

Causes: When multiple database users concurrently access resources, deadlocks may occur if the request order is improper. Here is a typical deadlock scenario:

  1. User A’s transaction flow:

    • First executes SELECT * FROM tableA FOR UPDATE (acquires exclusive lock on table A)
    • Then tries to execute SELECT * FROM tableB FOR UPDATE (waiting to acquire lock on table B)

  2. User B’s transaction flow:

    • First executes SELECT * FROM tableB FOR UPDATE (acquires exclusive lock on table B)
    • Then tries to execute SELECT * FROM tableA FOR UPDATE (waiting to acquire lock on table A)

At this point the system enters a deadlock state:

  • User A holds lock on table A, waiting to acquire lock on table B
  • User B holds lock on table B, waiting to acquire lock on table A
  • The two transactions wait for each other to release resources, forming circular waiting

Common application scenarios:

  • In e-commerce systems, when user A modifies the order table, they need to simultaneously update the inventory table
  • When user B modifies the inventory table, they need to simultaneously update the order table
  • Deadlocks may occur if the two operations occur simultaneously with inconsistent order

Solutions:

  1. Resource ordering method:

    • Define a fixed access order for all resources (such as tables)
    • For example, number all tables and access in strict numerical order from small to large
    • Example: If there are tables A(id=1), B(id=2), C(id=3), all transactions must first access A, then B, finally C

  2. Lock timeout mechanism:

    • Set reasonable lock wait timeout (such as 5 seconds)
    • Automatically roll back current transaction and retry after timeout
    • SQL Server example: SET LOCK_TIMEOUT 5000

  3. Transaction optimization suggestions:

    • Try to narrow transaction scope
    • Avoid user interaction within transactions
    • Split large transactions into multiple small transactions
    • Use optimistic locking instead of pessimistic locking

Row-Level Deadlock

Cause 1: If a query in a transaction executes without index conditions, causing a full table scan, row-level locks escalate to full table record locks (equivalent to table-level locks). After multiple such transactions execute, deadlocks and blocking easily occur, and the application system becomes increasingly slow, further加重阻塞和死锁。

Solution 1: Don’t use queries that are too complex with multiple table joins in SQL statements. Use EXPLAIN execution plan to analyze SQL statements. For SQL with full table scans and full table locks, create corresponding indexes for optimization.

Cause 2: The two transactions both want to acquire locks held by the other party, waiting for each other, thus creating a deadlock.

Solution 2: In the same transaction, try to acquire all needed resources at once. Sort resources by id and process in order.

Shared Lock to Exclusive Lock

Cause: Transaction A queries a record, then updates that record. At this time, transaction B also updates the same record. Transaction B’s exclusive lock must wait for A to release the shared lock before it can acquire it, only waiting in queue.

Transaction A:

-- Shared lock 1
SELECT * FROM dept WHERE deptno = 1 LOCK IN SHARE MODE;

-- Exclusive lock 3
UPDATE dept SET dname='java' WHERE deptno=1;

Transaction B:

-- Because 1 has a shared lock, cannot acquire other locks, need to wait
UPDATE dept SET dname='java' WHERE deptno=1

Solution:

  • For button controls, immediately disable on click to prevent users from clicking repeatedly, avoiding multiple record operations at the same time.
  • Use optimistic locking for control, which avoids the overhead of database locks in long transactions and greatly improves system performance under high concurrency.

Deadlock Investigation

View Deadlocks

View logs of recent deadlocks through the following command:

SHOW ENGINE INNODB STATUS\G;

View Lock Status

To deeply analyze MySQL InnoDB engine’s row lock competition, view key status variables through the following commands:

SHOW STATUS LIKE 'innodb_row_lock%';

These status variables provide the following important information:

  1. innodb_row_lock_current_waits (current number of waiting locks)

    • Real-time display of the number of sessions currently waiting for row locks

  2. innodb_row_lock_time (total lock time)

    • Cumulative statistics of total row lock wait time since database startup (milliseconds)

  3. innodb_row_lock_time_avg (average wait time)

    • Average wait time per row lock (milliseconds)
    • Health indicator: Usually should remain below 100ms, values exceeding 500ms require focused attention

  4. innodb_row_lock_time_max (maximum wait time)

    • Records the longest single transaction wait time for row locks since system startup

  5. innodb_row_lock_waits (total wait count)

    • Cumulative total number of row lock waits

Common Methods to Avoid Deadlocks

1. Maintain Consistent Locking Order

  • Primary key ordering method: When needing to update multiple rows of data, lock in ascending or descending order of primary key values
  • Table-level ordering method: When multi-table operations are involved, agree on a fixed table access order (such as dictionary order of table names)
  • Resource grading method: Grade database resources (such as table, page, row), lock in fixed order from high to low or low to high

2. Try to Shorten Transaction Time

  • Reduce interaction: Avoid user interaction, network requests and other time-consuming operations within transactions
  • Pre-process data: Complete data preparation and validation work outside transactions
  • Batch operations: Use batch updates instead of loop single updates
  • Timely commit: Commit immediately after completing core operations, don’t keep unnecessary transactions open

3. Reasonably Select Transaction Isolation Level

  • READ UNCOMMITTED: Lock-free reading, but may read dirty data
  • READ COMMITTED: Default level for most databases, only locks rows being read
  • REPEATABLE READ: Ensures consistent results when reading within a transaction (MySQL default)
  • SERIALIZABLE: Most strict isolation level, largest lock overhead

4. Use More Suitable Indexes

  • Avoid full table scans: Ensure WHERE conditions all hit indexes, reduce risk of lock escalation to table locks
  • Precision indexes: Use covering indexes to reduce table lookup operations
  • Index selectivity: High-selectivity indexes can reduce the range of locked data
  • Note index failure: Avoid operations that cause index failure such as using functions, type conversion on indexed columns

5. Decompose Large Transactions

  • Feature splitting: Split one large transaction into multiple independent small transactions by function
  • Data sharding: Split by data dimension
  • Timed commit: Commit every N records processed during large data volume operations
  • Compensation mechanism: For operations that must maintain atomicity, implement compensation transactions to handle partial failures

6. Explicitly Control Locking Order

  • Pre-locking: Use SELECT … FOR UPDATE to lock all necessary resources at the beginning of the transaction
  • Lock upgrade strategy: First acquire shared locks, upgrade to exclusive locks when needed
  • Timeout mechanism: Set lock acquisition timeout
  • Deadlock detection: Implement application-layer deadlock detection and retry mechanism
  • Lock granularity control: Select row locks, page locks or table locks according to scenarios
SELECT * FROM table LOCK IN SHARE MODE;

Exclusive Lock (Row-Level Lock - Write Lock)

SELECT * FROM employees WHERE id = 1 FOR UPDATE;

Features

  1. Exclusivity
  2. Exclusive access
  3. Blocks other operations

Optimistic Locking

Optimistic locking is a concurrency control mechanism that requires developers to design and implement themselves.

Implementation Methods

Version Number Mechanism

-- Read
SELECT id, name, version FROM products WHERE id = 1;

-- Update
UPDATE products
SET name = 'new product name', version = version + 1
WHERE id = 1 AND version = 1;

Applicable Scenarios

  • Applications with more reads and fewer writes
  • Low probability of concurrent conflicts

Variant Implementations

  • Timestamp method
  • Conditional update

Notes

  • Need to handle update failure situations
  • Not suitable for high-concurrency write scenarios