What are table locks, and how can table lock timeout errors be avoided?

Environment:

All Sites, All versions.

Issue:

Locked database tables, often seen via "Locked" error or warning messages.

Cause/Resolution:

MySQL has locking capabilities to control data integrity in a multi-user concurrency environment.

In the database world, when we have several connections executing queries concurrently we need some way to ensure that the connections do not step on each other’s toes. That is where locks enter the picture. We can think of database locks in the same way as traffic signals on road intersections that regulate access to the resources to avoid accidents. In a road intersection, it is necessary to ensure that two cars do not cross each other’s path and collide. In a database it is necessary to ensure two queries’ access to the data does not conflict.

When a request is made to the database to update data, it holds a lock on the needed rows, and potentially rows nearby, so data can be manipulated without another request changing it. If a second request comes in to modify the same data it must wait for the first request to finish. If the first request holds the lock long enough the second request will hit the designated timeout and return without modifying the data. The database will respond to the second request with lock_wait_timeout error.

The most common causes of lock issues are transactions that modify a large number of rows or that are active for much longer than necessary. To reduce lock issues it is important to keep your transactions small and to avoid delays that keep the transactions open for longer than necessary.

1 – Only lock the rows that are being created, updated, and destroyed.

When updating/deleting many rows it is best to do so in a batch based on the Primary Key. Fewer rows being processed results fewer locks. For example, use a batch size of 2,000 to 10,000 rows per request.

Ordering different transactions the same way can help prevent locking or deadlocks. Multiple transactions updating data sets in one or more tables should use the same order of operation for their transactions. Avoid locking table A, B, C in one transaction and C, A, B in another.

Don’t mix queries that modify data with queries that only retrieve data.

2 - Releasing the locks as quickly as possible

Break big transactions into smaller transactions. Keeping transactions short makes them less prone to collision.

Optimizing queries to examine fewer rows results in fewer locks. Make sure the query is using the primary key to update or destroy data and not doing a full table scan. The faster the queries are, the less likely they will be holding a lock when another request needs it. 

Running an Analytics Report or ROQL query in its own transaction as it prevents keeping the transactions open for longer than necessary and cause the necessary locks to be held longer than needed.

Avoiding cURL calls on the same transaction as a query since it also keeps the transactions open for longer than necessary and may cause locks to be held unnecessarily longer.