Troubleshooting MySQL deadlocks and transaction failures

MySQL deadlock troubleshooting starts with reading the report InnoDB writes when it kills one of your transactions. A deadlock is not a MySQL bug: two transactions each hold a row lock the other one wants, InnoDB detects the cycle and rolls one back with error 1213. This guide walks through that report, the live lock tables, and the transaction changes that stop the same pair of queries colliding again.

Before you start

• A Linux VPS running MySQL 8.0/8.4 or MariaDB 10.6 or newer, with root or sudo access.
• SSH access as a sudo-capable user such as deploy. See Connect to your Linux VPS with SSH if you have not set that up.
• A MySQL account with PROCESS and read access to performance_schema. Root works for a one-off investigation.
• The application error log or the client that reported the failure, so you know which query failed.
• A staging copy of the database if you plan to change indexes or isolation levels.

Log in and open a session:

ssh deploy@203.0.113.10
mysql -u root -p

MySQL deadlock troubleshooting: read the InnoDB report

Two errors matter here:

• ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction — InnoDB picked your transaction as the victim and rolled it back.
• ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction — no cycle was detected, but a lock was held longer than innodb_lock_wait_timeout.

Show the most recent deadlock:

SHOW ENGINE INNODB STATUS\G

Scroll to LATEST DETECTED DEADLOCK. You get the timestamp, both transactions, the statement each one was running, the rows and index it locked, and which transaction was rolled back. That block is the whole diagnosis, so read it before changing anything.

To keep every deadlock instead of only the last one:

SET GLOBAL innodb_print_all_deadlocks = ON;

The log is usually /var/log/mysql/error.log on Debian and Ubuntu, and /var/log/mysqld.log on AlmaLinux and Rocky. Check Where is my.cnf? if your paths differ.

Inspect the live locks and transactions

When the problem is happening right now, look at what holds what:

SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id,
       trx_query, trx_rows_locked, trx_rows_modified
FROM information_schema.INNODB_TRX\G

SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME,
       LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks\G

SELECT * FROM performance_schema.data_lock_waits\G

data_lock_waits is the fastest view: it names the blocking transaction and the waiting one. On MariaDB the same information lives in information_schema.INNODB_LOCKS and INNODB_LOCK_WAITS, so adjust the query if you are not on MySQL 8.

To see the SQL behind a running thread:

SELECT THREAD_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS ms
FROM performance_schema.events_statements_current;

Find the missing index or the wide lock

Most deadlocks come from one of three patterns:

1. Two transactions update the same rows in a different order (A then B versus B then A).
2. A statement scans without a usable index, so InnoDB locks far more rows than the query returns.
3. A transaction stays open for seconds while doing work outside the database.

Check the plan for the statement from the deadlock report:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 FOR UPDATE;

If type is ALL or key is NULL, add the index the query needs and the lock footprint shrinks immediately. Under REPEATABLE READ, InnoDB also takes gap locks on ranges, which is why an unindexed SELECT... FOR UPDATE can deadlock with a plain INSERT.

Change the transaction so it stops deadlocking

• Touch tables and rows in the same order in every code path. Sort IDs before updating a set.
• Keep transactions short: no HTTP calls, file writes or sleeps between BEGIN and COMMIT.
• Add retry logic for errors 1213 and 1205. Roll back, wait a short random delay, then run the transaction again. Most deadlocks disappear on the second attempt.
• Use SELECT... FOR UPDATE only on rows you will actually change, and prefer primary-key lookups.
• Consider READ COMMITTED for high-contention OLTP workloads, which reduces gap locking.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Check it works

Run the workload that used to fail, then confirm the counter has stopped moving:

SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';

Check the error log for new entries:

sudo tail -n 100 /var/log/mysql/error.log | grep -i deadlock

Also confirm your retry wrapper logs the second attempt as a success rather than swallowing the error. Once the counter is flat under normal load, turn innodb_print_all_deadlocks back off.

Troubleshooting

• Deadlocks keep appearing with no obvious cycle — a long-running transaction is holding locks. Look at trx_started in INNODB_TRX; anything running for minutes is the cause.
• Lock wait timeout exceeded but no deadlock — raise innodb_lock_wait_timeout only after shortening transactions, otherwise you just make users wait longer.
• Deadlock only appears under load — retry logic is missing or too fast. Add jitter and a small backoff.
• The same statement deadlocks after adding an index — check the index column order matches the WHERE and ORDER BY clauses, and refresh statistics with ANALYZE TABLE.
• The application reports 1213 but the server log is empty — innodb_print_all_deadlocks is off, or you are reading the wrong error log.

Next steps

• Find slow queries in MySQL with the slow query log to catch transactions that hold locks too long.
• Tune MySQL and MariaDB memory settings for a small VPS so the buffer pool and lock structures fit your plan.
• MySQL command-line cheat sheet for server admins for the queries used above.
• Back up and restore MySQL databases with mysqldump before you change indexes or isolation levels.