MySQL Error 1205

Error number

1205

SQLSTATE

HY000

Symbol

ER_LOCK_WAIT_TIMEOUT

Product

MySQL Server

Versions

MySQL 5.7, 8.0 and 8.4 with InnoDB tables (default wait 50 seconds, innodb_lock_wait_timeout)

Category

Locking & Transactions

Last reviewed

24 October 2026

What it means

Error 1205 means a statement waited longer than innodb_lock_wait_timeout for a row lock held by another transaction, so InnoDB gave up and rolled back that statement.

InnoDB locks the rows (and, for range conditions, the gaps between index records) that an UPDATE, DELETE, INSERT or locking read such as SELECT ... FOR UPDATE touches, and keeps those locks until the transaction commits or rolls back. Another transaction that needs one of those locks waits. If the lock is not released within innodb_lock_wait_timeout seconds (50 by default), the waiting statement fails with 1205. The session that gets the error is the victim of the wait, not the cause: the cause is the other transaction that is holding the lock, very often one that is idle and was never committed.

By default only the statement that timed out is rolled back, not the whole transaction (innodb_rollback_on_timeout is OFF). The transaction stays open with its earlier changes and locks, so the application must decide to retry the statement, or roll back and retry the transaction. This is different from a deadlock (1213), where InnoDB rolls back a whole transaction immediately. In SQL Server, by contrast, error 1205 is the deadlock error; see SQL Server 1222 for its lock timeout.

Common causes

• A transaction updated rows and then stayed open: an application that forgot to commit, a connection pool that keeps a transaction across requests, or an interactive client session left with autocommit off or after START TRANSACTION.
• A long batch job (a large UPDATE, DELETE or data migration) holds locks on many rows while short requests wait for them.
• A locking read (SELECT ... FOR UPDATE or FOR SHARE) over a range locks the gaps as well, so INSERTs into that range wait.
• A missing index makes an UPDATE or DELETE scan, and lock, far more rows than it changes.
• Work outside the database (an API call, a file upload, user think time) happens while the transaction holds row locks.

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.

SELECT waiting_pid, waiting_query, blocking_pid, blocking_query,
       wait_age_secs, locked_table, locked_index
FROM sys.innodb_lock_waits;

SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id,
       trx_rows_locked, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

SELECT object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE object_schema = DATABASE();

SELECT requesting_engine_transaction_id, blocking_engine_transaction_id
FROM performance_schema.data_lock_waits;

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
SHOW VARIABLES LIKE 'innodb_rollback_on_timeout';
SELECT name, count
FROM information_schema.innodb_metrics
WHERE name IN ('lock_timeouts', 'lock_row_lock_waits');

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.

KILL 5389;  -- the blocking_pid from sys.innodb_lock_waits

SET SESSION innodb_lock_wait_timeout = 5;  -- this connection only

SELECT id, status
FROM orders
WHERE status = 'new'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;

ALTER TABLE orders ADD INDEX ix_orders_status (status);

Examples

An open transaction blocks an UPDATE of the same row

Session B gets the error after the 1-second wait. In our run, B's transaction stayed open afterwards with its earlier changes (only the timed-out statement was rolled back).

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: changes a row and does not commit
START TRANSACTION;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;

-- Session B (a second connection): needs the same row
SET SESSION innodb_lock_wait_timeout = 1;  -- wait 1 second instead of 50
UPDATE accounts SET balance = balance + 10 WHERE id = 1;  -- waits, then fails

-- Session A
ROLLBACK;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

A range locked with FOR UPDATE blocks an INSERT

Under the default REPEATABLE READ isolation level the locking read takes next-key locks. In our run an INSERT for customer 8 also waited, while one for customer 20 succeeded at once.

CREATE TABLE orders (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    status      VARCHAR(20) NOT NULL,
    KEY ix_orders_customer (customer_id)
);
INSERT INTO orders (customer_id, status) VALUES (5, 'paid'), (7, 'new'), (7, 'paid'), (9, 'new');

-- Session A: locks customer 7's orders (and the gaps around them)
START TRANSACTION;
SELECT id, status FROM orders WHERE customer_id = 7 FOR UPDATE;

-- Session B: adds an order for customer 7
SET SESSION innodb_lock_wait_timeout = 1;
INSERT INTO orders (customer_id, status) VALUES (7, 'new');  -- waits, then fails

-- Session A
ROLLBACK;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

A plain SELECT reads at once, a locking read waits

CREATE TABLE orders (
    id     INT PRIMARY KEY,
    status VARCHAR(20)
);
INSERT INTO orders VALUES (1, 'new'), (2, 'new');

-- Session A
START TRANSACTION;
UPDATE orders SET status = 'packing' WHERE id = 1;

-- Session B
SET SESSION innodb_lock_wait_timeout = 1;
SELECT id, status FROM orders WHERE id = 1;             -- returns 'new' at once (consistent read)
SELECT id, status FROM orders WHERE id = 1 FOR UPDATE;  -- waits, then fails

-- Session A
ROLLBACK;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

How to prevent it

Keep transactions short: do the reads and calculations first, then open the transaction, make the related changes and commit at once. Never hold a transaction open across user input, network calls or a long loop, and make sure every code path (including errors) commits or rolls back. Index the columns used in UPDATE and DELETE conditions, and run big batch changes in small committed chunks outside peak hours.

Watch for it: alert on transactions in information_schema.innodb_trx that have been open longer than a few seconds, and on a rising lock_timeouts count. Handle 1205 in the application with a deliberate retry policy, remembering that the transaction is still open after the error.

Common questions

Learn more on CodeWithSQL

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

Official documentation

• InnoDB startup options and system variables (innodb_lock_wait_timeout)
• InnoDB error handling
• The data_lock_waits table
• Locking reads (NOWAIT and SKIP LOCKED)

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.