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 autoincrement.
menus table
id, name, status
categories table
id, menu_id, name
products table
id, menu_id, category_id, status, name, url, content
Multiple scripts can run in parallel, executing the same php file which 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 select result is found, it's status is updated to 1 (otherwise I rollback and release the locks)
UPDATE products SET status = 1 WHERE id = [product_id];
if the last query is successful I run
COMMIT();
UNLOCK TABLES;
otherwise
ROLLBACK();
UNLOCK TABLES;
After this the script is making a curl request to that products' url to get some content from it and to update the product's row 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]
So, the script is acquiring X lock on the mentioned tables, fetching one row from products table that has status of 0 (means - TODO), trying to set its status to 1 (means PENDING), and unlocks the tables. After that tries to execute some logic in php, and finally trying to update the products table - updating the content column and also the status to 2 (means DONE).
If I run like 5 scripts in parallel, after a few mins of running for the very last step (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 2 transactions are waiting each other to update the same rows in reverse order, however I can not figure out the reason for deadlock in this case. I mean scripts are working and locking the tables in same order, so if one script has locked the products(and other) tables, acquiring exclusive lock, the another scripts should wait in queue for those locks to be released, so it should not cause deadlock.
On the other hand each script is selecting products with status -> 0 and trying to update to 1, and during the same "session" from 1 to 2, so I do not see how this can be a reason for deadlock. What I am missing here ?
edit:
Though I did not mention how I am using the categories and menus tables' information, but I do need to fetch them as well, their usage is not important as I am not doing any db processing with them.
I used to use row-level locking SELECT FOR UPDATE, however I was getting deadlocks like in this question MySQL InnoDB dead lock on SELECT with exclusive lock (FOR UPDATE) , so, I had to change the code to table-level locking
Thanks