This is a follow-up issue for #6299 which rewrote the handling of the background queue. The current design is a SELECT followed by an INSERT which works because while there is a key on identifier, it is not required to be unique.
We can simplify this logic and also prevent situations where a unique job is inserted in parallel (we run into a TOCTOU problem here!) by changing the existing key into a unique key. Since non-unique jobs have their identifier set to null, these will be ignored by the index.
This requires a separate update script to remove any duplicate unique jobs before changing the key type. From then on we simplify the handler to just issue an INSERT IGNORE to gracefully handle conflicts without requiring a write lock on the table.
This is a follow-up issue for #6299 which rewrote the handling of the background queue. The current design is a
SELECTfollowed by anINSERTwhich works because while there is a key onidentifier, it is not required to be unique.We can simplify this logic and also prevent situations where a unique job is inserted in parallel (we run into a TOCTOU problem here!) by changing the existing key into a unique key. Since non-unique jobs have their
identifierset tonull, these will be ignored by the index.This requires a separate update script to remove any duplicate unique jobs before changing the key type. From then on we simplify the handler to just issue an
INSERT IGNOREto gracefully handle conflicts without requiring a write lock on the table.