MySQL Error 1213

Error number

1213

SQLSTATE

40001

Symbol

ER_LOCK_DEADLOCK

Product

MySQL Server

Versions

MySQL 5.7, 8.0 and 8.4 with InnoDB tables (deadlock detection on by default, innodb_deadlock_detect)

Category

Locking & Transactions

Last reviewed

24 October 2026

What it means

Error 1213 means two or more transactions were each waiting for a lock the other held, so InnoDB broke the deadlock by rolling back one whole transaction, the one that received this error.

A deadlock happens when transaction A holds a lock that B needs while B holds a lock that A needs; neither can continue. InnoDB checks for this cycle every time a transaction has to wait (innodb_deadlock_detect, ON by default), so the error arrives at once rather than after a timeout. It picks a victim, rolls back that transaction entirely and returns 1213 to it. The other transaction then gets its lock and carries on as if nothing happened.

The MySQL manual says InnoDB tries to pick small transactions to roll back, measured by the number of rows inserted, updated or deleted. In our runs on 8.0.46, when both transactions had changed the same number of rows, the session whose statement completed the cycle received 1213; when that session had already changed more rows, the other session was rolled back instead. Do not rely on either: any transaction in the cycle can be the victim. SQLSTATE 40001 is the standard "serialization failure" code that frameworks use to decide a transaction can be retried. Note that SQL Server reports deadlocks as error 1205; in MySQL 1205 is the lock wait timeout.

Common causes

• Two code paths update the same rows or tables in a different order, for example a transfer that debits account 1 then 2 running at the same time as one that debits 2 then 1.
• Two transactions read the same row with SELECT ... FOR SHARE (or check a foreign key, which takes a shared lock) and then both try to update it, so each waits for the other's shared lock.
• Range locks under REPEATABLE READ: locking reads, UPDATEs or DELETEs on ranges take next-key and gap locks, and concurrent INSERTs into those gaps collide.
• Missing indexes make statements scan and lock many more rows than they change, which raises the chance that two transactions overlap.
• Long transactions that touch many rows, which keep their locks for longer and collide with more concurrent work.

Diagnose it

Read-only These statements only read metadata (information_schema, performance_schema and SHOW statements) or your own data. They do not change data, privileges or configuration, so they are safe to run first.

SHOW ENGINE INNODB STATUS;

SELECT name, count
FROM information_schema.innodb_metrics
WHERE name = 'lock_deadlocks';

SHOW VARIABLES LIKE 'innodb_deadlock_detect';
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

SELECT t.trx_mysql_thread_id, t.trx_started, t.trx_rows_modified,
       l.object_name, l.index_name, l.lock_mode, l.lock_status, l.lock_data
FROM information_schema.innodb_trx AS t
JOIN performance_schema.data_locks AS l
  ON l.engine_transaction_id = t.trx_id
ORDER BY t.trx_started, l.object_name;

EXPLAIN SELECT id, balance
FROM accounts
WHERE id = 2;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

-- pseudocode for the application
-- for attempt in 1..3:
--     START TRANSACTION
--     run all statements of the unit of work
--     COMMIT, then stop
--     on error 1213: ROLLBACK, wait a few random milliseconds, try again
--     on any other error: ROLLBACK and report it

START TRANSACTION;
UPDATE accounts SET balance = balance - 10 WHERE id = LEAST(1, 2);   -- always the lower id first
UPDATE accounts SET balance = balance + 10 WHERE id = GREATEST(1, 2);
COMMIT;

START TRANSACTION;
SELECT qty FROM stock WHERE sku = 'MUG-1' FOR UPDATE;
UPDATE stock SET qty = qty - 1 WHERE sku = 'MUG-1';
COMMIT;

-- or, in one statement
UPDATE stock SET qty = qty - 1 WHERE sku = 'MUG-1' AND qty > 0;

ALTER TABLE orders ADD INDEX ix_orders_customer (customer_id);

SET GLOBAL innodb_print_all_deadlocks = ON;
-- later
SET GLOBAL innodb_print_all_deadlocks = OFF;

Examples

Two transfers update the same rows in opposite order

In our runs session B received 1213 (its transaction was rolled back) and session A's waiting UPDATE then completed. Both transactions were the same size; which one is chosen can differ.

CREATE TABLE accounts (
    id      INT PRIMARY KEY,
    balance DECIMAL(10,2) NOT NULL
);
INSERT INTO accounts VALUES (1, 100.00), (2, 50.00);

-- Session A
START TRANSACTION;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;

-- Session B
START TRANSACTION;
UPDATE accounts SET balance = balance - 5 WHERE id = 2;

-- Session A: waits for B's lock on row 2
UPDATE accounts SET balance = balance + 10 WHERE id = 2;

-- Session B: needs A's lock on row 1, completing the cycle
UPDATE accounts SET balance = balance + 5 WHERE id = 1;

-- then ROLLBACK (or COMMIT) in both sessions

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

Two sessions read with FOR SHARE, then both update the row

In our runs session B received 1213 and session A's UPDATE then went ahead.

CREATE TABLE stock (
    sku VARCHAR(20) PRIMARY KEY,
    qty INT NOT NULL
);
INSERT INTO stock VALUES ('MUG-1', 10);

-- Session A
START TRANSACTION;
SELECT qty FROM stock WHERE sku = 'MUG-1' FOR SHARE;

-- Session B
START TRANSACTION;
SELECT qty FROM stock WHERE sku = 'MUG-1' FOR SHARE;

-- Session A: needs an exclusive lock, waits for B's shared lock
UPDATE stock SET qty = qty - 1 WHERE sku = 'MUG-1';

-- Session B: needs an exclusive lock too, waits for A's shared lock
UPDATE stock SET qty = qty - 1 WHERE sku = 'MUG-1';

-- then ROLLBACK (or COMMIT) in both sessions

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

The larger transaction survives, the smaller one is rolled back

Here session A received 1213 on its waiting UPDATE, and its first UPDATE was undone too: after the error A read balance 100.00 for row 1 again. Session B's last UPDATE completed. This matches the manual: InnoDB tries to roll back the smaller transaction.

CREATE TABLE accounts (
    id      INT PRIMARY KEY,
    balance DECIMAL(10,2) NOT NULL
);
INSERT INTO accounts VALUES (1, 100.00), (2, 50.00);
CREATE TABLE audit_log (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    note VARCHAR(100)
);

-- Session A
START TRANSACTION;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;

-- Session B: writes an audit row first, so it has changed more rows
START TRANSACTION;
INSERT INTO audit_log (note) VALUES ('transfer 2 to 1');
UPDATE accounts SET balance = balance - 5 WHERE id = 2;

-- Session A: waits for B's lock on row 2
UPDATE accounts SET balance = balance + 10 WHERE id = 2;

-- Session B: completes the cycle
UPDATE accounts SET balance = balance + 5 WHERE id = 1;

-- then ROLLBACK (or COMMIT) in both sessions

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

How to prevent it

Design for deadlocks rather than hoping they never happen: wrap each unit of work in a transaction that can be retried safely, and retry it on 1213. Keep transactions short, access tables and rows in a fixed order in every code path, take the exclusive lock first (FOR UPDATE) when a read is followed by a write, and index the columns used in UPDATE, DELETE and locking-read conditions.

Monitor the lock_deadlocks counter in information_schema.innodb_metrics. If it rises, capture the deadlocks (SHOW ENGINE INNODB STATUS, or innodb_print_all_deadlocks for a while), find the pair of statements involved and change their order or locking, rather than only adding more retries.

Common questions

Learn more on CodeWithSQL

• Transactions, locks and deadlocks
• SQL transactions and ACID explained

Official documentation

• Deadlocks in InnoDB
• Deadlock detection
• How to minimize and handle deadlocks
• InnoDB error handling

Last technically reviewed 24 October 2026; examples and diagnostics run on MySQL 8.0.46. Message templates checked against the server's error catalogue (perror) for that version.