:Handle row lock waits and timeouts in RDS for MySQL

Problem description

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

Causes

Under normal conditions, a session holding an exclusive row lock completes its transaction quickly and releases the lock through a commit or rollback. Waiting sessions acquire the lock before the timeout and continue. Lock wait timeouts occur when a session holds a row lock longer than innodb_lock_wait_timeout allows.

Common patterns:

• Idle connection with an open transaction: An application opened a transaction and performed some work, but never committed or rolled back — leaving the lock held indefinitely.

• Long-running batch DML: A large UPDATE or DELETE holds row locks for an extended period while processing many rows.

• Interrupted application session: A client application's database session is interrupted without the instance detecting the disconnection, so the transaction and its locks are not released.

Solution

The steps below apply only when a row lock wait is actively occurring. The default innodb_lock_wait_timeout in RDS for MySQL is 50 seconds, which makes a live lock wait difficult to observe. To reproduce the issue in a test environment, set innodb_lock_wait_timeout to a large value — do not change this in production.

1. Log on to an RDS for MySQL instance using DMS.

2. Run the following query to identify which session is blocking which, along with the query being blocked and how long the blocker has been running:

   SELECT
     r.trx_mysql_thread_id    AS waiting_thread,
     r.trx_query              AS waiting_query,
     b.trx_mysql_thread_id    AS blocking_thread,
     b.trx_query              AS blocking_query,
     b.trx_started            AS blocking_started,
     TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_duration_sec
   FROM information_schema.INNODB_LOCK_WAITS w
   JOIN information_schema.INNODB_TRX b ON w.blocking_trx_id  = b.trx_id
   JOIN information_schema.INNODB_TRX r ON w.requesting_trx_id = r.trx_id;

   The result shows the thread ID and current query of both the waiting session and the blocker. Use blocking_duration_sec to gauge how long the blocker has held the lock.

   For a more detailed view, query each table individually:

   -- All active transactions
   SELECT * FROM information_schema.INNODB_TRX;
   
   -- Transactions waiting for locks
   SELECT * FROM information_schema.INNODB_LOCK_WAITS;
   
   -- Locks currently held
   SELECT * FROM information_schema.INNODB_LOCKS;

3. A blocker is a session holding a lock that prevents Data Manipulation Language (DML) operations in other sessions. If a transaction rollback is acceptable for the blocker, terminate it using the thread ID from Step 2:

   KILL <blocking_thread_id>;

   This rolls back the blocker's open transaction and releases its locks, allowing waiting sessions to proceed.

4. If the issue persists or you cannot identify the root cause, see Lock blocking to use the lock blocking statistics page to locate long-term blockers and view session details.

Prevent recurrence

Terminating the blocking session is an emergency measure. To prevent lock wait timeouts from recurring, address the underlying cause:

• Commit or roll back transactions promptly: Keep transactions short. Avoid leaving transactions open while waiting for application logic or user input.

• Break large batch operations into smaller chunks: Instead of one large UPDATE or DELETE, process rows in smaller batches and commit after each batch to release locks incrementally.

• Add appropriate indexes: Table scans on unindexed columns acquire more row locks than necessary. Add indexes to columns used in WHERE clauses of DML statements to reduce the number of rows locked.

• Detect idle connections at the application layer: Configure connection pool timeouts and enable TCP keepalive so the database detects disconnected clients and releases their locks promptly.

Applicable to

ApsaraDB RDS for MySQL