`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, My queue table looks like this: CREATE TABLE IF NOT EXISTS `api_queue` ( I've also noticed that sometimes the first transaction above (containing the UPDATE query) doesn't appear to actually apply at all - though I think this may well be an unrelated bug. Why does it hit deadlocks so often? Even if a transaction locks all the rows currently in a table, should I expect this to cause contention when new rows are added to the table? If so, why is that? The first transaction is typically pretty fast when it doesn't hit a deadlock (0.1 - 0.2 seconds to complete), but it does seem to hit deadlocks about 10% of the time. ![]() In a typical 5 minutes, the queue will build up a backlog of about 100 items, though occasionally it can be in the thousands if a lot of changes have occurred in the catalog. Application sanity checks the number of processing items it's about to delete against how many it's processed, and then.ĭELETE FROM api_queue WHERE status IN (:status_processing, :status_processing_duplicate) SELECT COUNT(*) AS total FROM api_queue WHERE status = :status_processing Once processing is complete, we purge the queue SET qdupes.status = IF(qs.id=qdupes.id, :status_processing, :status_processing_duplicate) INNER JOIN api_queue qdupes ON qdupes.products_id=qs.products_id AND qdupes.action=qs.action Application checks this result set is empty, then. This is exactly what the processor does with the queue: START TRANSACTION The queue processor job is the only thing that does anything with the table, apart from the parts of the system which just blindly insert rows. Subsequent duplicates in the queue are matched up and marked as the duplicate of an earlier queued item that is being processed. Each row to be processed has it's status field changed from a pending value to a processing value. Every 5 minutes, a job runs to process items from the queue. ![]() Rows are simply inserted into the table by other parts of the system and forgotten about. I have a database table that is basically a first-in-first-out queue.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |