Can’t lock nonexistent record in MySQL?!
I think it is a good habit to check logs now and then during development, because sometimes it seems the new feature you just finished works well, but it actually has some serious problems, which can be revealed in logs. For example, the problem of N+1 queries we be clearly recorded in logs if there is one.
One day, I was surprised by numerous deadlock errors in logs. The deadlocks were results of an INSERT query, which was wrapped in an transaction. At the beginning of the transaction, there was a SELECT … FOR UPDATE query to get a lock. How could it result in deadlocks?
Descriptions of this problem in detail can be found here and here, some bug reports in MySQL’s bug system. These reports also indicate that the bug has been there since 2007, but it can still be reproduced with latest version of MySQL. What a pity!
One workaround is to use a mutex table, where an existing record will be locked before the new record is inserted. For example, there are two tables: sellers and products. A seller has many products, but should not have any duplicate products. In this case, sellers table can be used as mutex table. Before a new product is inserted, a lock will be created on the seller’s record. With this additional query, it is guaranteed that only one thread can perform the action at any given time. No duplicate. No deadlock.
The problem was solved with the workaround finally. The application runs smoothly now, and logs are clean.