Fix MySQL Deadlock Detected

What's Actually Happening

MySQL deadlocks occur when two or more transactions hold locks that each needs to proceed. Neither can complete, and MySQL kills one transaction to break the cycle. Applications see deadlock errors and must retry.

The Error You'll See

Application error:

SQLException: Deadlock found when trying to get lock; try restarting transaction
Error Code: 1213
SQL State: 40001

MySQL error log:

```bash $ tail /var/log/mysql/error.log

[ERROR] InnoDB: Deadlock detected: Transaction 1:持有锁 on rows 1-5, waiting for lock on rows 10-15 Transaction 2:持有锁 on rows 10-15, waiting for lock on rows 1-5 We roll back transaction 2 ```

SHOW ENGINE INNODB STATUS:

LATEST DETECTED DEADLOCK 2026-04-16 00:01:00 0x7f8a1c0b9700 *** (1) TRANSACTION: TRANSACTION 12345, ACTIVE 2 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 10, OS thread handle 123456, query id 100 localhost root updating UPDATE orders SET status = 'processing' WHERE id = 1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table test.orders`

*** (2) TRANSACTION: TRANSACTION 12346, ACTIVE 1 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 11, OS thread handle 123457, query id 101 localhost root updating UPDATE orders SET status = 'completed' WHERE id = 2 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table test.orders ```

Why This Happens

1. 1.Lock order mismatch - Transactions lock rows in different order
2. 2.Lock escalation - Row locks escalate to table locks
3. 3.Long transactions - Transactions hold locks for extended periods
4. 4.Gap locks - InnoDB gap locks causing unexpected conflicts
5. 5.External locks - Explicit LOCK TABLES conflicts with transaction locks
6. 6.Concurrent updates - Multiple transactions updating same rows

Step 1: Analyze Deadlock Information

```bash # Get detailed deadlock information mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G" | grep -A 50 "LATEST DETECTED DEADLOCK"

# View recent deadlocks from error log tail -100 /var/log/mysql/error.log | grep -i deadlock

# Check deadlock count mysql -u root -p -e " SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks'; "

# Monitor deadlock occurrences mysql -u root -p -e " SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Innodb_deadlocks', 'Innodb_lock_wait_timeout', 'Innodb_row_lock_current_waits', 'Innodb_row_lock_time', 'Innodb_row_lock_waits' ); " ```

Step 2: Identify Involved Transactions

```bash # From SHOW ENGINE INNODB STATUS output: # Find TRANSACTION IDs, thread IDs, and queries

# Example deadlock pattern: # Transaction 1: locks row A, waits for row B # Transaction 2: locks row B, waits for row A # Classic circular dependency

# Check running transactions mysql -u root -p -e " SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query, trx_rows_locked, trx_lock_structs FROM information_schema.INNODB_TRX ORDER BY trx_started; "

# Check current locks mysql -u root -p -e " SELECT lock_id, lock_trx_id, lock_type, lock_mode, lock_table, lock_index, lock_data FROM information_schema.INNODB_LOCKS; "

# Check lock waits mysql -u root -p -e " SELECT requesting_trx_id, requested_lock_id, blocking_trx_id, blocking_lock_id FROM information_schema.INNODB_LOCK_WAITS; " ```

Step 3: Fix Lock Order Consistency

```sql -- WRONG: Different lock orders causing deadlock -- Transaction 1: UPDATE orders SET status = 'done' WHERE id = 1; UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100;

-- Transaction 2: UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100; UPDATE orders SET status = 'done' WHERE id = 1; -- Deadlock! T1 holds orders row, needs inventory; T2 holds inventory, needs orders

-- CORRECT: Consistent lock order across all transactions -- Always lock in same order: orders first, then inventory -- Transaction 1: UPDATE orders SET status = 'done' WHERE id = 1; UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100;

-- Transaction 2: UPDATE orders SET status = 'done' WHERE id = 2; UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100;

-- Establish lock ordering convention: -- 1. Parent tables before child tables -- 2. Higher-ID rows before lower-ID rows -- 3. Primary key order -- 4. Same table order in all transactions ```

Step 4: Use SELECT FOR UPDATE Strategically

```sql -- Lock rows explicitly before updating -- Prevents deadlocks by acquiring locks upfront

-- Transaction 1: START TRANSACTION; SELECT * FROM orders WHERE id = 1 FOR UPDATE; SELECT * FROM inventory WHERE product_id = 100 FOR UPDATE; UPDATE orders SET status = 'done' WHERE id = 1; UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100; COMMIT;

-- Transaction 2: START TRANSACTION; SELECT * FROM orders WHERE id = 2 FOR UPDATE; SELECT * FROM inventory WHERE product_id = 100 FOR UPDATE; UPDATE orders SET status = 'done' WHERE id = 2; UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100; COMMIT;

-- Use SKIP LOCKED for queue processing (MySQL 8.0+) SELECT * FROM tasks WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 10; -- Skips locked rows, prevents deadlock in concurrent processing ```

Step 5: Reduce Transaction Duration

```sql -- WRONG: Long transaction holding locks START TRANSACTION; SELECT * FROM orders WHERE id = 1; -- ... process data for 30 seconds ... UPDATE orders SET status = 'done' WHERE id = 1; COMMIT;

-- CORRECT: Minimize lock hold time START TRANSACTION; -- Do all reads first (no locks on plain SELECT) SELECT * FROM orders WHERE id = 1; COMMIT;

-- Process data outside transaction

START TRANSACTION; UPDATE orders SET status = 'done' WHERE id = 1; COMMIT;

-- Keep transactions as short as possible -- Only include statements that MUST be atomic ```

Step 6: Add Appropriate Indexes

```bash # Deadlocks often occur due to full table scans locking many rows

# Check if queries use indexes mysql -u root -p -e " EXPLAIN UPDATE orders SET status = 'done' WHERE customer_id = 100; "

# If type is ALL (full table scan), add index mysql -u root -p -e " CREATE INDEX idx_customer_id ON orders(customer_id); "

# Analyze table to update statistics mysql -u root -p -e "ANALYZE TABLE orders;"

# Verify index usage mysql -u root -p -e " EXPLAIN UPDATE orders SET status = 'done' WHERE customer_id = 100; " # Should show type: ref or range, not ALL ```

Step 7: Configure InnoDB Lock Settings

```bash # Edit /etc/mysql/mysql.conf.d/mysqld.cnf

# Lock wait timeout (default 50s) innodb_lock_wait_timeout = 30

# Reduce deadlock detection overhead for high concurrency innodb_deadlock_detect = ON # Keep ON for safety

# Locking reads behavior innodb_autoinc_lock_mode = 2 # Interleaved for INSERTs

# Restart MySQL sudo systemctl restart mysql

# Check current settings mysql -u root -p -e " SHOW VARIABLES LIKE 'innodb_lock%'; SHOW VARIABLES LIKE 'innodb_deadlock%'; " ```

Step 8: Implement Retry Logic

```python # Application retry pattern for deadlocks import mysql.connector from mysql.connector import errorcode

def execute_with_retry(conn, query, max_retries=3): for attempt in range(max_retries): try: cursor = conn.cursor() cursor.execute(query) conn.commit() return cursor except mysql.connector.Error as err: if err.errno == errorcode.ER_LOCK_DEADLOCK: conn.rollback() print(f"Deadlock detected, retrying ({attempt + 1}/{max_retries})") continue else: raise raise Exception(f"Failed after {max_retries} retries due to deadlocks")

# Use exponential backoff for retries import time

def execute_with_backoff(conn, query, max_retries=5): for attempt in range(max_retries): try: cursor = conn.cursor() cursor.execute(query) conn.commit() return True except mysql.connector.Error as err: if err.errno == errorcode.ER_LOCK_DEADLOCK: conn.rollback() wait_time = 0.1 * (2 ** attempt) # Exponential backoff time.sleep(wait_time) continue raise return False ```

// Java retry pattern
public void executeWithRetry(DataSource ds, String sql) throws SQLException {
    int maxRetries = 3;
    for (int i = 0; i < maxRetries; i++) {
        try (Connection conn = ds.getConnection()) {
            conn.createStatement().execute(sql);
            return;
        } catch (SQLException e) {
            if (e.getErrorCode() == 1213 && i < maxRetries - 1) {
                // Deadlock, retry
                continue;
            }
            throw e;
        }
    }
}

Step 9: Avoid Gap Locks

```sql -- Gap locks can cause unexpected deadlocks -- Use READ COMMITTED to reduce gap locks

-- Set transaction isolation level SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Or in configuration -- innodb_locks_unsafe_for_binlog = 1 (deprecated) -- Use READ COMMITTED instead

-- For simple updates without ranges: UPDATE orders SET status = 'done' WHERE id = 1; -- Uses record lock only

-- For range updates: UPDATE orders SET status = 'done' WHERE id BETWEEN 1 AND 10; -- May use gap locks

-- Use unique index lookups to avoid gap locks -- Primary key lookups only lock the specific row ```

Step 10: Monitor and Alert on Deadlocks

```bash # Create monitoring script cat << 'EOF' > /usr/local/bin/check_deadlocks.sh #!/bin/bash DEADLOCKS=$(mysql -u root -p$MYSQL_PASS -N -e " SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks'; " | awk '{print $2}')

if [ "$DEADLOCKS" -gt 10 ]; then echo "ALERT: $DEADLOCKS deadlocks detected" mysql -u root -p$MYSQL_PASS -e "SHOW ENGINE INNODB STATUS\G" | \ mail -s "MySQL Deadlock Alert" admin@company.com fi EOF

chmod +x /usr/local/bin/check_deadlocks.sh

# Add to cron echo "*/5 * * * * root /usr/local/bin/check_deadlocks.sh" > /etc/cron.d/mysql-deadlocks

# Create monitoring view mysql -u root -p -e " CREATE VIEW deadlock_stats AS SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME LIKE 'Innodb%lock%'; "

# Query deadlock stats mysql -u root -p -e "SELECT * FROM deadlock_stats;" ```

Deadlock Prevention Checklist

Verify the Fix

```bash # After applying fixes

# 1. Check deadlock count is stable mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';" # Should not increase rapidly

# 2. Monitor lock waits mysql -u root -p -e " SELECT count(*) FROM information_schema.INNODB_LOCK_WAITS; " # Should be 0 or low

# 3. Check transaction durations mysql -u root -p -e " SELECT trx_id, trx_started, NOW() - trx_started AS duration FROM information_schema.INNODB_TRX WHERE NOW() - trx_started > INTERVAL 10 SECOND; " # Should return empty or few rows

# 4. Run test transactions concurrently # Deadlock errors should not occur

# 5. Verify indexes are used mysql -u root -p -e "EXPLAIN UPDATE orders SET status = 'done' WHERE id = 1;" # Should use PRIMARY key

# 6. Check isolation level mysql -u root -p -e "SELECT @@transaction_isolation;" ```

Related Issues

• [Fix MySQL Lock Wait Timeout](/articles/fix-mysql-lock-wait-timeout)
• [Fix MySQL Transaction Rollback](/articles/fix-mysql-transaction-rollback)
• [Fix MySQL InnoDB Lock Conflict](/articles/fix-mysql-innodb-lock-conflict)