Home / Databases / MySQL

DatabasesTroubleshootingIntermediate

MySQL InnoDB deadlocks - reading SHOW ENGINE INNODB STATUS, logging every deadlock and fixing the access pattern

Error 1213 means InnoDB found a deadlock and rolled back one transaction. Log every deadlock, read which statements and locks were involved, then fix it with consistent ordering, better indexes, shorter transactions and application retries.

Deepak Bhardwaj5 min read

Applies toMySQL 8.0 and 8.4 LTS with InnoDB; Amazon RDS/Aurora MySQL; Azure Database for MySQL Flexible Server

Symptoms

Applications log:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

This is different from a lock wait timeout (ERROR 1205), where a transaction waited longer than innodb_lock_wait_timeout. With a deadlock, InnoDB detects the cycle straight away and rolls back the transaction that's cheapest to undo (the "victim").

An occasional deadlock is normal on a busy OLTP system. A steady rate, or a sudden increase after a release, needs fixing.

1. See the latest deadlock

SHOW ENGINE INNODB STATUS\G

Find the LATEST DETECTED DEADLOCK section:

*** (1) TRANSACTION:
TRANSACTION 4211, ACTIVE 0 sec starting index read
UPDATE accounts SET balance = balance - 10 WHERE id = 7
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `bank`.`accounts` ... lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `bank`.`accounts` ... lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
...
UPDATE accounts SET balance = balance + 10 WHERE id = 3
*** WE ROLL BACK TRANSACTION (1)

Note these points:

  • The statements each transaction was running. Earlier statements in the same transaction aren't shown, so check the app code for those.
  • The index where the locks were taken: PRIMARY or a secondary index.
  • The lock mode: X (exclusive), S (shared), gap / insert intention (range locking).

2. Log every deadlock

Only the latest deadlock is kept in the status output. To log all of them to the error log:

SET PERSIST innodb_print_all_deadlocks = ON;

On RDS/Aurora and Azure, set it in the parameter group or server parameters, then read the error log from the console or CLI.

Count how often they happen:

SELECT NAME, COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'lock_deadlocks';

(Enable the counter first if it shows as disabled: SET GLOBAL innodb_monitor_enable = 'lock_deadlocks';.)

3. Current lock waits (while it's happening)

SELECT r.trx_id AS waiting_trx, r.trx_query AS waiting_query,
       b.trx_id AS blocking_trx, b.trx_query AS blocking_query,
       b.trx_started, TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_age_s
FROM performance_schema.data_lock_waits w
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID;

The sys.innodb_lock_waits view gives the same information in a friendlier format.

4. Common patterns and fixes

Pattern Example Fix
Opposite update order T1 updates row A then B, T2 updates B then A Always lock rows in the same order (for example by primary key)
Missing index on the WHERE column UPDATE orders SET ... WHERE customer_ref = ? scans and locks many rows Index the filter column so only the target rows are locked
Gap locks under REPEATABLE READ Concurrent inserts into the same range after a SELECT ... FOR UPDATE Use exact-match lookups on unique keys, or READ COMMITTED where the app allows it
Long transactions User think time or remote calls inside a transaction Keep transactions short; do the remote calls before or after
Foreign keys Inserting child rows takes shared locks on parent rows Consistent ordering; avoid updating parent rows in the same hot path
Batch jobs vs OLTP A large UPDATE ... WHERE status = 'X' runs alongside app traffic Break the batch into small chunks by primary key and commit each one

Check the plan of the statements involved:

EXPLAIN UPDATE orders SET status = 'shipped' WHERE customer_ref = 'C-1042';

type: ALL or a large rows estimate means the statement locks far more rows than it changes.

Isolation level

MySQL's default is REPEATABLE READ, which uses gap and next-key locks for range scans. Many applications work correctly with READ COMMITTED, which takes far fewer gap locks:

SET PERSIST transaction_isolation = 'READ-COMMITTED';

Warning

Changing the isolation level changes application behaviour (repeatable reads within a transaction). Test it, and keep binary logging in ROW format, which READ COMMITTED requires.

5. Retries in the application

Deadlocks can't be eliminated completely. The application must retry the whole transaction on error 1213:

import time, random, pymysql

def run_with_retry(conn, work, attempts=3):
    for i in range(attempts):
        try:
            with conn.cursor() as cur:
                work(cur)
            conn.commit()
            return
        except pymysql.err.OperationalError as e:
            conn.rollback()
            if e.args[0] != 1213 or i == attempts - 1:
                raise
            time.sleep(0.05 * (2 ** i) + random.random() * 0.05)

Prevent it next time

  • Keep innodb_print_all_deadlocks on and alert on the rate.
  • Review new queries for index use on UPDATE/DELETE predicates.
  • Consistent lock ordering as a coding rule for multi-row transactions.
  • Chunk batch jobs and run them outside peak hours.

Checklist

  • Deadlock details captured (statements, index, lock modes)
  • All deadlocks logged; rate measured
  • Missing indexes on locking statements fixed
  • Consistent lock order in code
  • Transactions shortened, batches chunked
  • Application retries on 1213

Related fixes