title: How to handle MySQL deadlock situations on an application level?
body: <p>When a deadlock situation occurs in MySQL/InnoDB, it returns this familiar error:</p>
  
  <p>'Deadlock found when trying to get lock; try restarting transaction'</p>
  
  <p>So what i did was record all queries that go into a transaction so that they can simply be reissued if a statement in the transaction fails. Simple.</p>
  
  <p><strong>Problem: When you have queries that depend on the results of previous queries, this doesn't work so well.</strong> </p>
  
  <p>For Example:</p>
  
  <pre><code>START TRANSACTION;
  INSERT INTO some_table ...;
  -- Application here gets ID of thing inserted: $id = $database-&gt;LastInsertedID()
  INSERT INTO some_other_table (id,data) VALUES ($id,'foo');
  COMMIT;
  </code></pre>
  
  <p>In this situation, I can't simply reissue the transaction as it was originally created. The ID acquired by the first SQL statement is no longer valid after the transaction fails but is used by the second statement. Meanwhile, many objects have been populated with data from the transaction which then become obsolete when the transaction gets rolled back. The application code itself does not "roll back" with the database of course.</p>
  
  <p><strong>Question is: How can i handle these situations in the application code?</strong> (PHP)</p>
  
  <p>I'm assuming two things. Please tell me if you think I'm on the right track:</p>
  
  <p>1) Since the database can't just reissue a transaction verbatim in all situations, my original solution doesn't work and should not be used.</p>
  
  <p>2) The only good way to do this is to wrap any and all transaction-issuing code in it's own try/catch block and attempt to reissue the code itself, not just the SQL.</p>
  
  <p>Thanks for your input. You rock.</p>