tooolbox/blog/MySQL Deadlock Resolution: InnoDB Lock Analysis and Query Optimization
troubleshooting2024-09-01Β·9Β·275/348

MySQL Deadlock Resolution: InnoDB Lock Analysis and Query Optimization

Diagnosing and resolving MySQL InnoDB deadlocks through lock analysis, query restructuring, and index optimization strategies.

Introduction

Deadlocks are one of the most frustrating database issues because they are intermittent and hard to reproduce. They happen when two or more transactions are waiting for each other to release locks, creating a circular dependency. MySQL's InnoDB engine automatically detects deadlocks and rolls back one of the transactions, but this still causes errors in the application.

I encountered deadlocks in my trading platform's order processing system. The system handles concurrent order updates from multiple trading strategies, and under high load, deadlocks occurred several times per hour. Each deadlock caused a failed transaction, which required retry logic and added latency to order processing. Here is how I diagnosed and eliminated the deadlocks.

Environment

  • MySQL: 8.0.35 on Ubuntu 22.04
  • Engine: InnoDB
  • Table: orders (50M rows, ~20GB)
  • Concurrency: ~500 transactions/second during peak
  • Application: Go with database/sql and go-sql-driver/mysql

Problem

The application logs showed periodic deadlock errors:

2024-09-01 14:23:45 ERROR: Error 1213: Deadlock found when trying to get lock; try restarting transaction
  at (*DB).QueryRowContext
  query: UPDATE orders SET status = 'filled', filled_at = NOW() WHERE id = ?
  transaction started at: processOrder (order_service.go:145)

I checked the InnoDB status for deadlock information:

SHOW ENGINE INNODB STATUS\G

The "LATEST DETECTED DEADLOCK" section showed:

LATEST DETECTED DEADLOCK
------------------------
2024-09-01 14:23:44
*** (1) TRANSACTION:
TRANSACTION 12345678, ACTIVE 0 sec
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1112, 2 row lock(s)
MySQL thread id 456, OS thread id 140234567890
UPDATE orders SET status = 'filled', filled_at = NOW() WHERE id = 12345

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 345 page no 890 n bits 72 index PRIMARY of table `trading`.`orders`
record lock, heap no 3 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
 0: len 8; hex 8000000000003039; asc    0 9  ;

*** (2) TRANSACTION:
TRANSACTION 12345679, ACTIVE 0 sec
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1112, 2 row lock(s)
UPDATE orders SET status = 'cancelled', updated_at = NOW() WHERE id = 12346

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 345 page no 890 n bits 72 index PRIMARY of table `trading`.`orders`
record lock, heap no 4 PHYSICAL RECORD: n_fields 15; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (1)

The deadlock involved two transactions trying to update different rows in the same table. Transaction 1 was updating order ID 12345 while Transaction 2 was updating order ID 12346. They should not deadlock on different rows, which pointed to a gap lock issue.

Analysis

The key insight was in the deadlock details. The two transactions were updating different rows, but InnoDB was using gap locks on the PRIMARY index. Gap locks prevent other transactions from inserting rows in a range between two existing records.

The issue was that the UPDATE statements were using WHERE clauses that triggered gap locks:

// Transaction 1: Fill an order
_, err = tx.Exec(
    "UPDATE orders SET status = 'filled', filled_at = NOW() WHERE id = ? AND status = 'pending'",
    orderID,
)

// Transaction 2: Cancel a different order
_, err = tx.Exec(
    "UPDATE orders SET status = 'cancelled', updated_at = NOW() WHERE id = ? AND status = 'pending'",
    orderID,
)

Both transactions were filtering on status = 'pending', and InnoDB was acquiring gap locks on the status column to prevent concurrent modifications to rows with that status. The gap locks overlapped, causing the deadlock.

I confirmed this by checking the lock information:

SELECT * FROM performance_schema.data_locks;
+-------------+------------+-----------+-----------+-----------+-----------+------------+-----------+----------+
| LOCK_TYPE   | TABLE_NAME | LOCK_MODE | LOCK_TYPE | INDEX     | LOCK_DATA | COLUMN     | TRX_ID    | STATUS   |
+-------------+------------+-----------+-----------+-----------+-----------+------------+-----------+----------+
| RECORD      | orders     | X,GAP     | RECORD    | PRIMARY   | 12345     | id         | 12345678  | WAITING  |
| RECORD      | orders     | X,GAP     | RECORD    | PRIMARY   | 12346     | id         | 12345679  | WAITING  |
+-------------+------------+-----------+-----------+-----------+-----------+------------+-----------+----------+

The X,GAP lock mode confirmed that gap locks were the issue. The gap locks on the status column's index were overlapping.

Solution

I implemented a three-part fix:

1. Add a proper index for the status filter:

-- Create a composite index that covers both the filter and the sort
ALTER TABLE orders ADD INDEX idx_status_id (status, id);

This index allowed InnoDB to use a more targeted lock strategy instead of gap locks on the primary index.

2. Restructure the UPDATE queries to use SELECT FOR UPDATE SKIP LOCKED:

func processOrder(db *sql.DB, orderID int64) error {
    tx, err := db.BeginTx(context.Background(), &sql.TxOptions{
        Isolation: sql.LevelReadCommitted,
    })
    if err != nil {
        return fmt.Errorf("begin tx: %w", err)
    }
    defer tx.Rollback()

    // Use SKIP LOCKED to avoid waiting on locked rows
    var order Order
    err = tx.QueryRow(
        `SELECT id, status, symbol, quantity, price
         FROM orders
         WHERE id = ? AND status = 'pending'
         FOR UPDATE SKIP LOCKED`,
        orderID,
    ).Scan(&order.ID, &order.Status, &order.Symbol, &order.Quantity, &order.Price)

    if err == sql.ErrNoRows {
        return fmt.Errorf("order %d not available for processing", orderID)
    }
    if err != nil {
        return fmt.Errorf("lock order: %w", err)
    }

    // Process the order...
    _, err = tx.Exec(
        "UPDATE orders SET status = 'filled', filled_at = NOW() WHERE id = ?",
        order.ID,
    )
    if err != nil {
        return fmt.Errorf("update order: %w", err)
    }

    return tx.Commit()
}

The SKIP LOCKED clause tells InnoDB to skip rows that are already locked by another transaction instead of waiting for them. This eliminates the circular wait condition that causes deadlocks.

3. Switch to Read Committed isolation level:

tx, err := db.BeginTx(context.Background(), &sql.TxOptions{
    Isolation: sql.LevelReadCommitted,
})

The default MySQL isolation level is REPEATABLE READ, which uses gap locks. Switching to READ COMMITTED eliminates gap locks entirely, which prevents this class of deadlocks:

SET GLOBAL transaction_isolation = 'READ-COMMITTED';

4. Add retry logic for remaining deadlocks:

func withRetry(fn func() error, maxRetries int) error {
    var err error
    for i := 0; i < maxRetries; i++ {
        err = fn()
        if err == nil {
            return nil
        }
        if !isDeadlock(err) {
            return err
        }
        time.Sleep(time.Duration(i*50) * time.Millisecond)
    }
    return fmt.Errorf("max retries exceeded: %w", err)
}

func isDeadlock(err error) bool {
    var mysqlErr *mysql.MySQLError
    if errors.As(err, &mysqlErr) {
        return mysqlErr.Number == 1213
    }
    return false
}

After deploying all changes, I monitored for deadlocks:

-- Check deadlock count
SHOW STATUS LIKE 'Innodb_deadlocks';

Before the fix: approximately 15 deadlocks per hour during peak. After the fix: 0 deadlocks over 24 hours of monitoring.

Lessons Learned

  1. Gap locks are a common deadlock source β€” In REPEATABLE READ isolation, InnoDB uses gap locks that can cause deadlocks even on different rows. Switch to READ COMMITTED if gap locks cause problems.
  2. SKIP LOCKED eliminates contention β€” For work queues and task processing, SELECT ... FOR UPDATE SKIP LOCKED avoids lock contention entirely by skipping locked rows.
  3. Analyze SHOW ENGINE INNODB STATUS β€” The deadlock section shows exactly which transactions and locks are involved. Use this information to understand the root cause.
  4. Use composite indexes β€” Proper indexes allow InnoDB to use row-level locks instead of gap locks, reducing lock scope.
  5. Always implement retry logic β€” Even with optimizations, some deadlocks may occur. Retry logic with exponential backoff handles them gracefully.

This blog does not accept any external sponsorships, affiliate marketing, or ad revenue.