MariaDB InnoDB Deadlock Diagnosis: Reading Error 1213/1205 Traces

You’re looking at a MariaDB server and see ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction or ERROR 1205 (701): Lock wait timeout exceeded. This means two or more transactions are holding locks that the other needs, forming a cycle InnoDB cannot resolve. The useful takeaway: you can identify the offending queries and their lock modes by reading the latest deadlock trace with SHOW ENGINE INNODB STATUS, then apply a targeted fix—such as adding a missing index or trimming a long-running transaction—rather than killing the server process.

When your application reports deadlock errors

Deadlocks surface when an application performs concurrent writes without adequate ordering or indexing. Typical symptoms include application timeouts, repeated Deadlock found messages in client logs, and decreased throughput under load. InnoDB’s default behavior is to detect the cycle and abort one transaction (the victim), returning the error to the application.

Error code	Meaning	First action
1213	Deadlock found when trying to get lock; try restarting transaction	Check the latest deadlock trace
1205	Lock wait timeout exceeded; try restarting transaction	Check for long-running queries

Ordered checks to isolate the cause

Work through these steps in order. Each step narrows the scope from the whole server to a specific statement.

1. Check the error log. Examine the MariaDB error log for deadlock entries around the time the application reported the error. The log timestamp narrows the window for investigation. On most systems the log is at /var/log/mysql/mariadb.log or visible via journalctl -u mariadb.
2. Run the deadlock diagnostic command. On the MariaDB server, as a user with SUPER privilege or database administration access, execute:

mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G"

This command outputs the latest InnoDB status, including the most recent deadlock trace. The trace appears under the LATEST DETECTED DEADLOCK section. If the trace is empty, no deadlock has been recorded since the server started, so check the error log for older entries.

1. Read the trace structure. The trace lists each transaction involved, the lock it holds (table, index, row), and the lock it is waiting for. Look for the two statements that form the cycle. For example:

*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 10 sec
UPDATE orders SET status='paid' WHERE order_id=1001;
*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 8 sec
UPDATE inventory SET qty=qty-1 WHERE product_id=42;
*** WE ROLL BACK TRANSACTION (2)

The trace also shows the lock modes: X (exclusive) for writes, S (shared) for reads. A deadlock usually involves two X locks, but can also involve S and X when a transaction upgrades a shared lock.

1. Compare with application logs. Match the transaction IDs or timestamps in the trace to your application’s logs to identify the code path that issued the conflicting statements. This step is essential because the trace alone does not tell you which business operation triggered the deadlock.

Fixes tied to your findings

Once you know the pattern, apply the fix that matches the root cause. The most common causes and their remedies are:

Missing index causes full‑table scans

If the trace shows UPDATE or DELETE statements that scan many rows, the table likely lacks a supporting index. Without an index, InnoDB locks every row it examines, increasing the chance of a cycle. Add an index that covers the WHERE clause columns.

ALTER TABLE orders ADD INDEX idx_order_status (status);

Verify the index is used by running EXPLAIN on the query. The type column should show ref or range, not ALL.

Long‑running transactions hold locks too long

If the trace shows a transaction that has been active for seconds or minutes, it may be holding locks while waiting for user input or an external service. Shorten the transaction by moving slow operations outside the database transaction, or by committing earlier. Also check for missing COMMIT statements in application code.

Lock ordering is inconsistent

Two transactions that update the same rows in different orders will deadlock. For example, transaction A updates row 1 then row 2, while transaction B updates row 2 then row 1. Fix this by always accessing rows in a consistent order, such as by primary key or a business key.

Lock wait timeout is too low

If you see ERROR 1205 frequently but no deadlock trace, the issue may be that transactions wait longer than innodb_lock_wait_timeout (default 50 seconds). If your workload legitimately needs longer waits, increase the value, but first investigate why the lock is held so long.

SET GLOBAL innodb_lock_wait_timeout = 100;

This change is dynamic but resets on restart. To make it permanent, add it to the [mariadb] section of my.cnf.

Escalation criteria

If you have applied the index and ordering fixes and deadlocks still occur, escalate to a deeper review:

• Deadlocks appear across multiple tables that are not logically related, suggesting a design issue in the data model.
• The trace shows a transaction waiting on a lock held by a transaction that is itself waiting on another, creating a chain longer than two—this may indicate a lock-ordering bug in a stored procedure.
• You see deadlocks even under low concurrency, which points to a single transaction that acquires locks in an unpredictable order.
• Application retry logic is not handling the 1213 error, causing cascading failures.

In these cases, work with the application team to redesign the transaction boundaries or consider using a message queue to serialize writes. Do not kill the MariaDB process to resolve a deadlock; the error is already handled by InnoDB, and killing the server may leave orphaned locks or corrupt the redo log.

Verifying the fix

After applying a fix, reproduce the workload that previously caused the deadlock. Check the error log and run SHOW ENGINE INNODB STATUS again to confirm no new deadlock entries appear. Monitor for a period that covers your peak traffic. If you changed innodb_lock_wait_timeout, verify the new value with SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'.

Deadlock diagnosis is a repeatable process: read the trace, identify the cycle, fix the root cause, and verify. The trace is your primary evidence, and the fixes are targeted to the specific lock pattern you see.