Introduction MySQL InnoDB uses row-level locking for UPDATE operations. When multiple transactions try to update the same rows or overlapping index ranges, some transactions must wait. If the wait exceeds `innodb_lock_wait_timeout` (default 50 seconds), the waiting transaction is rolled back with `ERROR 1205 (HY000): Lock wait timeout exceeded`.

Symptoms - `ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction` - Application operations timing out after exactly `innodb_lock_wait_timeout` seconds - `SHOW ENGINE INNODB STATUS` shows `TRANSACTIONS` section with blocked transactions - Certain rows or ranges consistently causing timeouts under concurrent load - Application retry logic creating cascading timeout storms

Common Causes - Two transactions updating the same row in opposite order - Missing index on WHERE clause columns causing gap locks on entire table - Long-running transactions holding locks while waiting for external resources - Hot row contention (e.g., counter increments on a single row) - Foreign key checks acquiring locks on parent table rows

Step-by-Step Fix 1. **Identify the blocking transaction": ```sql -- Check for current lock waits SELECT r.trx_id AS waiting_trx_id, r.trx_query AS waiting_query, r.trx_wait_started AS wait_start, b.trx_id AS blocking_trx_id, b.trx_query AS blocking_query, TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_seconds FROM information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id; ```

  1. 1.**Check InnoDB status for detailed lock information":
  2. 2.```sql
  3. 3.SHOW ENGINE INNODB STATUS\G
  4. 4.-- Look for the TRANSACTIONS section
  5. 5.-- Find: "LOCK WAIT" and the query being blocked
  6. 6.`
  7. 7.**Kill the blocking transaction if necessary":
  8. 8.```sql
  9. 9.-- Find the blocking thread
  10. 10.SELECT
  11. 11.p.id,
  12. 12.p.user,
  13. 13.p.host,
  14. 14.p.db,
  15. 15.p.command,
  16. 16.p.time,
  17. 17.p.state,
  18. 18.p.info
  19. 19.FROM information_schema.processlist p
  20. 20.JOIN information_schema.innodb_trx t ON t.trx_mysql_thread_id = p.id
  21. 21.WHERE p.time > 30;

-- Kill the blocking thread KILL <thread_id>; ```

  1. 1.**Add missing indexes to reduce lock scope":
  2. 2.```sql
  3. 3.-- Check the query plan for the UPDATE
  4. 4.EXPLAIN UPDATE orders SET status = 'shipped' WHERE customer_id = 123 AND status = 'pending';

-- Add the missing index CREATE INDEX idx_orders_customer_status ON orders (customer_id, status); ```

  1. 1.**Implement retry logic for lock wait timeouts":
  2. 2.```python
  3. 3.import time
  4. 4.import mysql.connector

def update_with_retry(conn, query, params, max_retries=3): for attempt in range(max_retries): try: cursor = conn.cursor() cursor.execute(query, params) conn.commit() return cursor.rowcount except mysql.connector.Error as err: if err.errno == 1205: # Lock wait timeout conn.rollback() time.sleep(0.1 * (2 ** attempt)) continue raise ```

Prevention - Keep transactions short to minimize lock hold time - Always index WHERE clause columns in UPDATE statements - Use `SELECT ... FOR UPDATE SKIP LOCKED` for queue-like processing - Set `innodb_lock_wait_timeout` to a reasonable value (5-10s for OLTP) - Monitor `Innodb_row_lock_waits` and `Innodb_row_lock_time_avg` in `SHOW STATUS` - Design schemas to avoid hot row contention (use counters table with modulo sharding) - Use optimistic locking (version columns) instead of pessimistic locking when possible