A while ago, I had a very annoying issue with one of the Python scripts in my office. What happened is, that the script was causing an ERROR 1205 (HY000): Lock wait timeout exceeded;error in the database although the queries were straightforward and not very time-consuming at all. The script was using multiple threads. After hours of annoying debugging, we found the issue was related to how different database engines handle locking.

What is locking in the database?

Whenever a query is executed, the database engine puts a lock either on the table or on the rows of the table so that no other query can modify the same data at the same time. Now, different database engines implement locking in a different way. Let’s talk about the two most common and popular MySQL engines- MyISAM and InnoDB.

Locking in MyISAM

MyISAM does table-level locking. Basically, for any database query, it locks the whole table. MyISAM supports two types of locking- READ and WRITE. READ lock is applied for SELECT statements and WRITE lock is implemented for INSERT/UPDATE/DELETE statements.

Notes

• For any SELECT query, a READ lock is applied. Multiple queries can have multiple READ locks at the same time on the same table.
• For any INSERT/UPDATE/DELETE query, a WRITE lock is applied immediately. Only one query can have a WRITE lock on a table.
• If a READ lock is applied on a table, another READ lock is acceptable on the same table but a WRITE lock is not acceptable.
• If a WRITE lock is applied on a table, no other lock is acceptable.

Locking in InnoDB

InnoDB supports both table-level and row-level locking. We’ll discuss only row-level locking here as this is the best feature of InnoDB which supports concurrency. In row-level locking, only a row is locked i.s.o. the whole table. So, different queries can insert, update or delete different rows at the same time which increases the efficiency of the application. But, there’s a catch. To have the benefit of concurrency, it must be ensured that multiple queries are not trying to handle any common rows in between.

InnoDB has Shared Locking (READ) and Exclusive Locking (WRITE). It also supports auto lock conversion- it can upgrade a lock from Shared to Exclusive which is a handful for an UPDATE statement with WHERE condition. A single row can have multiple Shared locks at the same time but only one Exclusive lock at a time.

Notes

• For any SELECT query, a Shared lock is applied. Multiple queries can have multiple Shared locks at the same time on the same row.
• For any INSERT/UPDATE/DELETE query, an Exclusive lock or a combination of Shared and Exclusive lock is applied based on the nature of the query and also the structure of the database and table.
• If a Shared lock is applied on a row, another Shared lock is acceptable on the same row but an Exclusive lock is not acceptable.
• If an Exclusive lock is applied on a row, no other lock is acceptable.

The issue with the Lock wait timeout exceeded

Let’s assume, we have a table with 5 rows that have only two columns- id and status. The database engine is InnoDB. All the rows have status=0 initially. Say, two parallel queries are executed as the following.

Now, the queries must read the rows first to find matching rows of the WHERE condition. So, both the query will acquire a Shared lock on all the rows of the table and choose 3 rows to update leaving the Shared lock on other rows. As the table has 5 rows and LIMIT is set to 3 then it’s a must that these two queries will have at least one row in common (in practice it can be even all 3 rows in common). Let’s assume, that query Q1 has selected rows R1, R3, and R5. On the other hand, query Q2 has selected R2, R3, and R5. Both queries will now try to update the status column with a random number. So, both Q1 and Q2 will try to upgrade their Shared lock to Exclusive lock. For, rows R1 and R2, the update will happen successfully. When Q1 will try to upgrade the Shared lock on R3 to an Exclusive lock for the UPDATE statement to complete, it can not do so as Q2 also has a Shared lock on R3. So, Q1 will wait for Q2 to finish. But, Q2 can not finish updating R3 and R5 as Q1 has a Shared lock both on R3 and R5. So, Q2 can not upgrade the Shared lock on R3 and R5 to an Exclusive lock to complete the UPDATE statement. As a result, both Q1 and Q2 are now waiting for each other to finish causing a deadlock.

Solution

An easy solution is to apply two-phase locking. We can break an UPDATE statement into two parts like the following.

This way, no two concurrent queries can have a Shared lock on this row where id=50. The clause FOR UPDATE will apply an Exclusive lock immediately on this row if no other lock is already applied.