Diagnosing and Resolving MariaDB InnoDB Lock Wait Timeout Errors

Recognizable condition

Applications return ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction when executing UPDATE, DELETE, or INSERT statements under concurrent load. The error indicates that a transaction waited longer than the configured innodb_lock_wait_timeout for a row lock held by another transaction.

Cause and diagnostic overview

Symptom	Likely cause
Frequent timeout errors on hot rows	Long‑running transactions or missing indexes causing full table scans
Timeouts appear only under high concurrency	Insufficient innodb_lock_wait_timeout value for the workload
Deadlocks appear in the InnoDB status	Lock ordering issues due to application logic

Ordered checks

1. Verify the current timeout value. Run this in the MariaDB client as a user with the PROCESS privilege:

   SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

   Note the returned value (seconds).

2. Identify transactions that are waiting or holding locks. Execute:

   SHOW ENGINE INNODB STATUS\LATEST DETECTED DEADLOCK

   Look for the LATEST DETECTED DEADLOCK section or the TRANSACTIONS section that lists WAITING FOR LOCK and LOCK WAIT lines. If you have access to the performance_schema, you can also query:

   SELECT * FROM performance_schema.data_locks WHERE LOCK_STATUS='WAITING' LIMIT 10;

   This requires the PROCESS privilege.

3. Examine the query plan for the statement that triggered the timeout. Replace your_table and the WHERE clause with the actual query:

   EXPLAIN SELECT * FROM your_table WHERE id = 123 FOR UPDATE;

   Check the type and rows columns; a value of ALL or a high rows count suggests a missing index.

4. Assess server load and connection count to see if the system is saturated:

   SHOW STATUS LIKE 'Threads_connected';

   High connection counts combined with long‑running queries can exacerbate lock waits.

Fixes tied to findings

• Adjust innodb_lock_wait_timeout – If the current value is too low for legitimate long‑running transactions, increase it globally (requires SUPER privilege):

  SET GLOBAL innodb_lock_wait_timeout = 120;

  Verify the change with SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';. Risk: A higher timeout allows problematic transactions to hold locks longer, potentially increasing contention. Test the new value in a staging environment first.

  Rollback: Store the original value before changing it, then revert:

  SET GLOBAL innodb_lock_wait_timeout = <original_value>;

• Add missing indexes – If EXPLAIN shows a full table scan, create an appropriate index. Example for a column status used in a WHERE clause:

  ALTER TABLE your_table ADD INDEX idx_status (status);

  Verify usage after creation:

  EXPLAIN SELECT * FROM your_table WHERE status = 'active';

  Risk: Indexes increase write overhead and storage consumption. Benchmark the impact on your workload.

  Rollback: Drop the index if it proves detrimental:

  ALTER TABLE your_table DROP INDEX idx_status;

• Shorten transaction duration – Refactor application code to commit or roll back as soon as possible. Techniques include:

  • Fetching only needed rows.
  • Avoiding heavy computations inside a transaction.
  • Using optimistic locking or retry logic with exponential backoff.

  After changes, monitor SHOW ENGINE INNODB STATUS for a reduction in innodb_row_lock_current_waits.

• Consider InnoDB tuning options – Enable the adaptive hash index if read‑heavy workloads benefit:

  SET GLOBAL innodb_adaptive_hash_index = ON;

  Or adjust innodb_thread_concurrency to limit concurrent threads and reduce lock contention.

  Verify effect by checking SHOW ENGINE INNODB STATUS for lower lock wait times.

Escalation criteria

• Lock wait timeouts persist after:
• Increasing innodb_lock_wait_timeout to a value that matches your longest legitimate transaction.
• Adding indexes that reduce examined rows to a reasonable count (EXPLAIN rows < 1000 for the problematic query).
• Refactoring transactions to be shorter than the new timeout.
• SHOW ENGINE INNODB STATUS shows:
• Frequent deadlocks (LATEST DETECTED DEADLOCK appears repeatedly).
• High and sustained innodb_row_lock_current_waits (> 10% of innodb_row_lock_time_avg).
• Increasing innodb_lock_waits despite timeout adjustments.
• If the above conditions hold, consider:
• Reviewing application design for lock ordering (e.g., always access tables in the same order).
• Partitioning hot tables to spread lock contention.
• Upgrading to a newer MariaDB release (e.g., 10.6+) that includes improved InnoDB lock handling and performance_schema enhancements.

Verification

1. After changing innodb_lock_wait_timeout, run:

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

2. After creating an index, confirm the plan uses it:

EXPLAIN SELECT … WHERE indexed_column = …;

3. During a representative load window, monitor:

SHOW ENGINE INNODB STATUS\G

Look for decreasing values in innodb_row_lock_current_waits and zero occurrences of Lock wait timeout exceeded in the application logs.