Common Locking Errors in MySQL

Locking errors occur when multiple transactions or sessions compete for the same resources.
Below are the most common locking errors in MySQL, along with their causes and solutions.

1. Lock wait timeout exceeded: Occurs when a transaction waits too long for a lock held by another session.

   Solution: Roll back the transaction or reduce contention with shorter transactions.

2. Deadlock found when trying to get lock: Happens when two or more transactions are waiting for each other’s locks, causing a cycle.

   Solution: Ensure consistent locking order and avoid holding locks for long periods.

3. Table is locked for read/write: Occurs when a table is explicitly locked using LOCK TABLES, and another session tries to access it.

   Solution: Use UNLOCK TABLES or wait until the lock is released.

4. Can’t execute the query because another session has a lock: The table or row is already locked by another transaction.

   Solution: Wait or use non-blocking techniques like SELECT ... FOR UPDATE NOWAIT.

5. Transaction in progress – can’t acquire new lock: You’re trying to acquire a lock within a transaction that’s already locked to something incompatible.

   Solution: Carefully structure the transaction and avoid overlapping lock requests.

6. Lock wait timeout with autocommit ON: With autocommit = 1, locks are not released until the query finishes — still causing timeouts.

   Solution: Use shorter queries or disable autocommit to manually control commits.

7. Metadata lock errors: Happens when one session is altering a table structure while another is reading/writing to it.

   Solution: Avoid DDL operations during peak traffic or use LOCK TABLES carefully.