Anand Sukumaran Nair

Startup Founder, Software Engineer, Abstract thinker
Co-founder & CTO @ Engagespot (Techstars NYC '24)

Gap-locking and phantom reads - Affecting MySQL Parallelism

We had 4-5 workers picking jobs from a MySQL table. But we noticed the parallelism was inefficient. Even though the workers were designed to pick different jobs, only one process was doing the work while the others were simply waiting.

After investigating, we found it was due to the gap-locking mechanism with the SELECT … FOR UPDATE query.

We run SELECT … FOR UPDATE to fetch specific rows in each worker. But instead of locking just those specific rows, MySQL locks several other rows because of gap locks and next-key locks. MySQL does this to prevent something called phantom reads.

What are phantom reads?

A phantom read happens when:

• Transaction A reads rows that match a condition.
• Transaction B inserts new rows that match that same condition.
• Transaction A reads again and finds new rows-these are the “phantoms.”
• To prevent this, MySQL uses gap locks to lock the gaps between index records. This means other transactions can’t insert new rows into those gaps during the transaction.

Why did this affect our workers?

Because of gap locking, when one worker locks rows with SELECT … FOR UPDATE, it also locks adjacent gaps. Other workers trying to lock different rows might be blocked if their target rows fall into these locked gaps.

Solution:

Use proper indexing: Ensure queries use indexes effectively to minimize locking. Adjust isolation levels: Using READ COMMITTED instead of REPEATABLE READ can reduce gap locking. Modify queries: Fetch and lock only the necessary rows to reduce the locked range. Understanding MySQL’s locking mechanisms helps us write better queries and improve parallelism.