Mysql - Serialization failure: 1213 Deadlock found when try to get lock; try restarting transaction

Question

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

Answer 1

There are several things you are doing wrong here. First off, there is no reason for you to be table locking the tables. One of the design goals of InnoDB is that it has row level locking.

Second, you should be using a SELECT FOR UPDATE statement to lock the rows you are working with, then do your UPDATE and COMMIT.

I also don't see why you are joining Products to categories and menus when those tables are simply informational in regards to this update. This seems like a "data processing" function.