📋

Description

This error occurs when a transaction in MariaDB attempts to acquire a lock on a database resource (like a row or table) but cannot obtain it within the configured `innodb_lock_wait_timeout` period. It typically indicates that another transaction is holding the required lock, preventing concurrent operations from proceeding.

💬

Error Message

Lock wait timeout exceeded; try restarting transaction

🔍

Known Causes

4 known causes

⚠️

Long-Running Transactions

Transactions that perform extensive operations or are left open for too long can hold locks, blocking other transactions from accessing the same resources.

⚠️

High Concurrency and Resource Contention

Multiple concurrent transactions attempting to modify the same rows or tables can lead to contention, where locks are frequently requested and held.

⚠️

Inefficient Queries or Missing Indexes

Poorly optimized SQL queries without appropriate indexes can escalate lock granularity or scan more data, holding locks for longer durations than necessary.

⚠️

Short Lock Wait Timeout Setting

The `innodb_lock_wait_timeout` variable might be set too low for the typical workload, causing transactions to time out prematurely under normal contention.

🛠️

Solutions

5 solutions available

1. Retry the Transaction easy

Simply retry as the error message suggests

1

Implement retry logic in your application

import time
import mysql.connector

def execute_with_retry(cursor, query, params, max_retries=3):
    for attempt in range(max_retries):
        try:
            cursor.execute(query, params)
            return cursor.fetchall()
        except mysql.connector.Error as e:
            if e.errno == 1205 and attempt < max_retries - 1:
                time.sleep(1)  # Wait before retry
                continue
            raise

2

PHP retry example

$maxRetries = 3;
for ($i = 0; $i < $maxRetries; $i++) {
    try {
        $pdo->beginTransaction();
        // ... your queries ...
        $pdo->commit();
        break;
    } catch (PDOException $e) {
        $pdo->rollBack();
        if (strpos($e->getMessage(), '1205') !== false && $i < $maxRetries - 1) {
            sleep(1);
            continue;
        }
        throw $e;
    }
}

2. Find and Kill Blocking Transaction medium

Identify what's holding the lock

1

Find blocking transactions

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

2

See all current locks

SELECT * FROM information_schema.INNODB_LOCKS;

3

Find long-running queries

SELECT * FROM information_schema.PROCESSLIST
WHERE TIME > 30
ORDER BY TIME DESC;

4

Kill blocking process if appropriate

KILL process_id;

3. Increase Lock Wait Timeout easy

Allow more time for locks to be released

1

Check current timeout setting (default is 50 seconds)

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

2

Increase timeout for current session

SET innodb_lock_wait_timeout = 120;

3

Increase globally (persists until restart)

SET GLOBAL innodb_lock_wait_timeout = 120;

4

Make permanent in my.cnf

[mysqld]
innodb_lock_wait_timeout = 120

4. Optimize Queries and Add Indexes medium

Reduce lock duration by faster queries

1

Check if query is using indexes

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

2

Add index on frequently locked columns

CREATE INDEX idx_customer_id ON orders(customer_id);

3

Use SELECT FOR UPDATE only when needed

-- Avoid locking if you just need to read
-- Use normal SELECT instead of:
SELECT * FROM products WHERE id = 1 FOR UPDATE;

5. Reduce Transaction Scope medium

Keep transactions short to release locks faster

1

Break large transactions into smaller ones

-- Instead of one big transaction:
START TRANSACTION;
-- process 10000 rows
COMMIT;

-- Break into batches:
while rows_remaining:
    START TRANSACTION;
    -- process 100 rows
    COMMIT;

2

Move non-critical operations outside transaction

-- Don't do this inside transaction:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
send_email_notification();  -- Slow, move outside!
COMMIT;

-- Better:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
send_email_notification();  -- After commit

3

Commit frequently in batch operations

SET autocommit = 0;
DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 1000;
COMMIT;
DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 1000;
COMMIT;
-- Repeat until done

🔗

Related Errors

5 related errors

MariaDB Error 1205: Lock Wait Timeout Exceeded