title: Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction"
body: <p>I have a MySQL table with about 5,000,000 rows that are being constantly updated in small ways by parallel Perl processes connecting via DBI.  The table has about 10 columns and several indexes.</p>
  
  <p>One fairly common operation gives rise to the following error sometimes:</p>
  
  <pre><code>DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at Db.pm line 276.
  </code></pre>
  
  <p>The SQL statement that triggers the error is something like this:</p>
  
  <pre><code>UPDATE file_table SET a_lock = 'process-1234' WHERE param1 = 'X' AND param2 = 'Y' AND param3 = 'Z' LIMIT 47
  </code></pre>
  
  <p>The error is triggered only sometimes. I'd estimate in 1% of calls or less. However, it never happened with a small table and has become more common as the database has grown.</p>
  
  <p>Note that I am using the a_lock field in file_table to ensure that the four near-identical processes I am running do not try and work on the same row. The limit is designed to break their work into small chunks.</p>
  
  <p>I haven't done much tuning on MySQL or DBD::mysql. MySQL is a standard Solaris deployment, and the database connection is set up as follows:</p>
  
  <pre><code>my $dsn = "DBI:mysql:database=" . $DbConfig::database . ";host=${DbConfig::hostname};port=${DbConfig::port}";
  my $dbh = DBI-&gt;connect($dsn, $DbConfig::username, $DbConfig::password, { RaiseError =&gt; 1, AutoCommit =&gt; 1 }) or die $DBI::errstr;
  </code></pre>
  
  <p>I have seen online that several other people have reported similar errors and that this may be a genuine deadlock situation.</p>
  
  <p>I have two questions:</p>
  
  <ol>
  <li><p>What exactly about my situation is causing the error above?</p></li>
  <li><p>Is there a simple way to work around it or lessen its frequency? For example, how exactly do I go about "restarting transaction at Db.pm line 276"?</p></li>
  </ol>
  
  <p>Thanks in advance.</p>