📋

Description

This error indicates that a transaction attempted to acquire a lock on a database resource but had to wait longer than the configured timeout period. It typically occurs when another transaction holds the required lock, preventing the current operation from proceeding.

💬

Error Message

Lock wait timeout exceeded; try restarting transaction

🔍

Known Causes

4 known causes

⚠️

Long-Running Transactions

A transaction holds a lock for an extended duration, blocking other transactions from accessing the same resources and causing them to time out.

⚠️

High Concurrency/Contention

Many concurrent transactions simultaneously attempt to access or modify the same data, leading to increased lock contention and potential timeouts.

⚠️

Inefficient Queries or Missing Indexes

Poorly optimized SQL queries or the absence of suitable indexes can cause transactions to scan large datasets, holding locks longer than necessary.

⚠️

Low `innodb_lock_wait_timeout` Setting

The configured `innodb_lock_wait_timeout` system variable might be set too low for the typical workload, causing legitimate waits to prematurely time out.

🛠️

Solutions

5 solutions available

1. Increase Lock Wait Timeout easy

Allow more time for lock acquisition

1

Check current timeout

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

2

Increase for current session

SET SESSION innodb_lock_wait_timeout = 120;  -- 120 seconds

3

Or set globally

SET GLOBAL innodb_lock_wait_timeout = 120;

4

Make permanent in my.cnf

[mysqld]
innodb_lock_wait_timeout = 120

2. Find and Kill Blocking Transaction medium

Identify what's holding the lock

1

Check InnoDB status for locks

SHOW ENGINE INNODB STATUS;

2

Find blocking transactions

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

3

See running transactions

SELECT * FROM information_schema.INNODB_TRX;

4

Kill blocking process if necessary

-- Find the blocking thread ID from above queries
KILL thread_id;

3. Optimize Transaction Design medium

Reduce lock holding time

1

Keep transactions short

-- Bad: Long transaction holding locks
START TRANSACTION;
UPDATE users SET status = 'active' WHERE id = 1;
-- ... long processing ...
COMMIT;

-- Good: Minimal lock time
START TRANSACTION;
UPDATE users SET status = 'active' WHERE id = 1;
COMMIT;
-- ... processing after commit ...

2

Use optimistic locking

-- Add version column
UPDATE users SET name = 'New', version = version + 1
WHERE id = 1 AND version = 5;
-- Check affected rows; if 0, someone else updated first

4. Retry Transaction on Timeout medium

Handle timeout gracefully in code

1

Implement retry logic

import time
import mysql.connector

def execute_with_retry(query, params, max_retries=3):
    for attempt in range(max_retries):
        try:
            cursor.execute(query, params)
            conn.commit()
            return True
        except mysql.connector.Error as e:
            if e.errno == 1205:  # Lock wait timeout
                time.sleep(1 * (attempt + 1))  # Exponential backoff
                continue
            raise
    return False

5. Add Missing Indexes medium

Proper indexes reduce lock scope

1

Check if UPDATE/DELETE uses index

EXPLAIN UPDATE users SET status = 'active' WHERE email = 'test@test.com';

2

Add index to reduce locked rows

-- Without index: locks many/all rows
-- With index: locks only matching rows
ALTER TABLE users ADD INDEX idx_email (email);

🔗

Related Errors

5 related errors

MySQL Error 1205: Lock Wait Timeout