title: How to prevent database deadlocks in concurrent transactions?
body: <p>The scenario:</p>
  
  <p><strong>transaction A starts...</strong></p>
  
  <pre><code>START TRANSACTION;
  UPDATE table_name SET column_name=column_name+1 WHERE id = 1 LIMIT 1;
  </code></pre>
  
  <p><strong>At the same time, transaction B starts...</strong></p>
  
  <pre><code>START TRANSACTION;
  UPDATE table_name SET column_name=column_name+1 WHERE id = 2 LIMIT 1;
  UPDATE table_name SET column_name=column_name-1 WHERE id = 1 LIMIT 1;
  COMMIT;
  </code></pre>
  
  <p><em>Right Now, transaction B is waiting for row 1, which is locked in transaction A.</em></p>
  
  <p><strong>And transaction A continues...</strong></p>
  
  <pre><code>UPDATE table_name SET column_name=column_name-1 WHERE id = 2 LIMIT 1;
  COMMIT;
  </code></pre>
  
  <p><strong><em>And now we have a dead lock, so both transactions are waiting for each other to unlock a row that they want to update :'(</em></strong></p>
  
  <p>As I asked in the title, how can we prevent deadlocks in RDBMS transactions?</p>
  
  <p>I think the only way to fix this situation is that we rollback transaction B and re-execute it. But how can we find out that we are in a deadlock, and get out of it immediately, and how can we guarantee that we do not stock in and endless loop (on very heavy web applications, for example).</p>
  
  <p>If it is necessary, I'm using MySQL. But any solution for other RDBMSes are welcome - for helping other peoples coming here from Google :)</p>