I have menus , categories and products tables. I am using mysql 5.5, all tables are innoDB, and in all cases id is the primary key (int) with auto-increments.
menus table id, name, status categories table id, menu_id, name products table id, menu_id, category_id, status, name, url, content
Several scripts can be executed in parallel, executing the same php file that contains the following logic.
START TRANSACTION; SET autocommit = 0; LOCK TABLES products WRITE, categories WRITE, menus WRITE; SELECT p.`id`, p.`name`, p.`url`, p.`status`, c.`id` cat_id, c.`name` cat_name, m.`id` `menu_id`, m.`name` menu_name FROM products p LEFT JOIN categories c ON p.`subcategory_id` = c.`id` LEFT JOIN menus m ON p.`menu_id` = m.`id` WHERE p.`status` = 0 LIMIT 1
if after the selection result is found, its status is updated to 1 (otherwise, I roll back and release the locks)
UPDATE products SET status = 1 WHERE id = [product_id];
If the last request is successful, I ran
COMMIT(); UNLOCK TABLES;
otherwise
ROLLBACK(); UNLOCK TABLES;
After that, the script makes a curl request to the URL of this product to get some content from it and update the product line accordingly
// making curl request (might take a few dozen seconds, because proxy is being used and sometimes because of proxy failure the request is attempted again) trying to update the products table UPDATE products SET content = [received content], status = 2 WHERE id = [product_id]
Thus, the script obtains an X lock on the specified tables, extracts one row from the product table, which has the status 0 (means - TODO), trying to set its status to 1 (means "WAIT") and unlocks the tables. After that, it tries to execute some logic in php and, finally, it tries to update the product table - update the content column, as well as the status to 2 (means DONE).
If I run as 5 scripts in parallel, after several minutes of work at the last stage (updating the product to DONE), I get this error
Error: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when try to get lock; try restarting transaction
I understand the general idea of deadlocks when two transactions are waiting for each other to update the same rows in the reverse order, however I cannot understand the reason for the deadlock in this case. I mean that scripts work and lock tables in the same order, therefore, if one script blocked the products (and others) tables, having received an exclusive lock, other scripts should wait in line for those locks to be released, so it should not cause a dead end.
On the other hand, each script selects products with the status → 0 and tries to upgrade to 1 and during the same “session” from 1 to 2, so I don’t see how this can cause a deadlock. What am I missing here?
edit:
Although I did not mention how I use information about categories and menu tables, but I also need to get them, their use is not important, since I do not do any db processing with them.
I used to use SELECT FOR UPDATE row-level locking, however, I got stubs like this issue MySQL InnoDB lock on SELECT with exclusive lock (FOR UPDATE) , so I had to change the code to table-level locking
thanks