Description:
Scaling an application with multiple threads/processes/instances that uses a queue-like table causes deadlocks. A secondary index is used by queries for work, and updated when work is completed. When the "skip locked" modifier is used, transaction 1 is performing a query for work and transaction 2 is updating rows, deadlocks occur quite frequently.
With MySQL index record locking on locking reads, T1 locks secondary index records and wants the PK index record lock, while T2 finds rows by PK and updates the secondary index value. MySQL deletes the secondary index and inserts a new tuple in the index when the value changes. But T1 has the secondary index record locked.
How to repeat:
Create a table with PK id and secondary index status. In one transaction, select for update where status=<some value> skip locked matching one or more rows, while at the same time in a second transaction update the value for secondary index on at least one of the rows with secondary index locked by transaction one.
Suggested fix:
Multiple potential ways to fix or improve:
1) Change the way MySQL locks index records before evaluating the where clause. Presumably it works this way, though, for performance and this change would be detrimental to performance and also potentially most risky. However, it might be reasonably safe to make this change with a config setting to turn it off/on at user's discretion.
2) Change MySQL to filter locked index records and not just locked data rows when using the new skip locked modifier
3) Use shared locks instead of exclusive locks on index records, and upgrade to exclusive locks if/when necessary
4) Use a mutex or similar mechanism to acquire both SI and PK index locks simultaneously or neither, reducing or eliminating potential for deadlock.
5) Possibly other(s)?
© 2023, Oracle Corporation and/or its affiliates