title: Mysql - Serialization failure: 1213 Deadlock found when try to get lock; try restarting transaction
body: <p>I have <code>menus</code>, <code>categories</code> and <code>products</code> tables. I am using mysql 5.5, All tables are innoDB and in all cases id is the primary key (int) with autoincrement.</p>
  
  <pre><code>menus table
  id, name, status
  
  categories table
  id, menu_id, name
  
  products table
  id, menu_id, category_id, status, name, url, content
  </code></pre>
  
  <p>Multiple scripts can run in parallel, executing the same php file which contains the following logic.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>if after select result is found, it's status is updated to 1 (otherwise I rollback and release the locks)</p>
  
  <pre><code>UPDATE products SET status = 1 WHERE id = [product_id];
  </code></pre>
  
  <p>if the last query is successful I run</p>
  
  <pre><code>COMMIT();
  UNLOCK TABLES;
  </code></pre>
  
  <p>otherwise</p>
  
  <pre><code>ROLLBACK();
  UNLOCK TABLES;
  </code></pre>
  
  <p>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 </p>
  
  <pre><code>// 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]
  </code></pre>
  
  <p>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).</p>
  
  <p>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</p>
  
  <pre><code>Error: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when try to get lock; try restarting transaction
  </code></pre>
  
  <p>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 <code>products</code>(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.<br>
  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 ?</p>
  
  <p><strong>edit:</strong> </p>
  
  <p>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.</p>
  
  <p>I used to use row-level locking <code>SELECT FOR UPDATE</code>, however I was getting deadlocks like in this question <a href="https://stackoverflow.com/questions/5432370/mysql-innodb-dead-lock-on-select-with-exclusive-lock-for-update/5432714#5432714">MySQL InnoDB dead lock on SELECT with exclusive lock (FOR UPDATE)</a> , so, I had to change the code to table-level locking</p>
  
  <p>Thanks</p>