Database Advanced 14 views

Lock Wait Timeout Exceeded

A database query waited too long for a lock held by another transaction.

Explanation

Lock wait timeout exceeded occurs when a transaction waits longer than the configured timeout period to acquire a lock held by another transaction. Unlike a deadlock (where both transactions block each other), this is a one-way wait - one transaction is holding a lock and the other is waiting for it. The waiting transaction is rolled back after the timeout. Common causes include long-running transactions, missing indexes causing table-level locks, and high-concurrency scenarios.

Common Causes

Long-running transactions holding locks
Missing indexes causing table locks
High-concurrency write operations
Lock escalation from row to table
Transaction timeout too short

Solution

Identify and optimize long-running transactions that hold locks too long. Add proper database indexes to reduce lock scope. Use SELECT ... FOR UPDATE sparingly. Implement optimistic locking for high-concurrency scenarios. Increase innodb_lock_wait_timeout in MySQL if appropriate (default is 50 seconds). Monitor active transactions with SHOW ENGINE INNODB STATUS. Break large transactions into smaller ones. Use queue-based processing for write-heavy operations.

Code Example

// Long transaction holding a lock
DB::beginTransaction();
User::where('id', 1)->update(['status' => 'active']);
// ... long processing ...
// Another query tries to update same row - TIMEOUT!
DB::commit();

// Fix: keep transactions short
DB::beginTransaction();
User::where('id', 1)->update(['status' => 'active']);
DB::commit(); // commit immediately

// Monitor locks in MySQL
SHOW ENGINE INNODB STATUS;
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_trx;

// Increase timeout (MySQL)
SET GLOBAL innodb_lock_wait_timeout = 120;
// Or in my.cnf: innodb_lock_wait_timeout = 120

// Use optimistic locking in Eloquent
class User extends Model
{
    use OptimisticLocking;
    // Adds 'version' column for conflict detection
}

Error Information

Language

php

Difficulty

Advanced

Views

Related Errors

PHP

Lock Wait Timeout Exceeded

Explanation

Common Causes

Solution

Code Example

Error Information

Related Errors

Trying to Access Array Offset on Value of Type Null

Service Unavailable (503)

Laravel 500 Internal Server Error

SerializationException