title: Does MySQL InnoDB engine queue DB Triggers automatically?
body: <p>Let's say I have 1000 requests being received on our servers to update a single MySQL table. Deadlock issues occur inevitably in this situation. We have retried posting the transaction as recommended for deadlocks but they still occur.</p>
  
  <p>We are thinking of coming up with an alternative solution below.</p>
  
  <ol>
  <li>Create Table A, B, C.</li>
  <li>Write Requests coming to the server to update Table D, into A or B or C.</li>
  <li>Create an INSERT trigger on Tables A, B and C respectively that will in turn write the data in Table D instead of directly exposing Table D to the 1000 requests coming to the server.</li>
  </ol>
  
  <p>So our question is when this happens and multiple rows gets written into Table A, B and C the underlying Triggers on Tables A, B and C might fire at the same time to update Table D.</p>
  
  <p>Does MySQL InnoDB engine automatically queue these triggers or should we have to handle this in our code?</p>
  
  <p>Any help is much appreciated.</p>
  
  <p>Table D that is being updated directly by all these requests now and where the deadlock occurs looks like this.</p>
  
  <pre><code>v_user_email    varchar(60) NO  PRI     
  v_device_IMEI   varchar(40) NO  PRI     
  i_adid          int(11)         NO  PRI     
  i_impressions   int(4)          YES 0   
  dt_pulllogdttm  datetime    NO          
  c_created_by    char(15)    NO          
  dt_created_on   datetime    NO          
  c_modified_by   char(15)    YES         
  dt_modified_on  datetime    YES 
  </code></pre>
  
  <p>PHP that inserts / updates rows in this table looks like this below. You will see that we try posting the transaction 3 times if it fails due to deadlock but there are transactions that fail even then and the log says due to deadlock.</p>
  
  <pre><code>$updateQuery = "UPDATE tb_ad_pull_log SET i_impressions = (i_impressions + 1), dt_pulllogdttm = SYSDATE(), c_modified_by = '$createdBy', dt_modified_on = SYSDATE() WHERE v_user_email = '$email' AND i_adid = $adId";
          if(ExecuteDeadLockQuery($updateQuery, "UPDATE", __LINE__) == 0) // If there is no record for this ad for the user, insert a new record
          {
              $insertQuery = "INSERT INTO tb_ad_pull_log VALUES('$email', '$device_IMEI', $adId, 1, SYSDATE(), '$createdBy', SYSDATE(), NULL, NULL)";
              ExecuteDeadLockQuery($insertQuery, "INSERT", __LINE__);
          }    
  </code></pre>
  
  <p>ExecuteDeadLockQuery function looks like this -</p>
  
  <pre><code>function ExecuteDeadLockQuery($query, $activity, $lineNumber)
      {
          global $errorLoggingPath;
          $maxAttempts = 3;
          $currentTry = 1;
          $noOfAffectedRows = -1;
  
          while($currentTry &lt;= $maxAttempts)
          {
              $currentTry++;
  
              mysql_query($query);
  
              if( mysql_errno() &lt;&gt; 0 ) // If error occured
              {
                  continue;
              }
              else
              {
                  $noOfAffectedRows = mysql_affected_rows();
                  break;
              }           
          }
  
          if($noOfAffectedRows == -1) // Query never executed successfully
          {
              LogError($activity . " failed in tb_ad_pull_log: " . mysql_error(), __FILE__, $lineNumber , $errorLoggingPath);
          }
  
          return $noOfAffectedRows;
      }
  </code></pre>
  
  <p>Is there a cleaner way to avoid this deadlock? Here are some logs that we have.</p>
  
  <pre><code>ERROR:  08-21-2011 14:09:57  UPDATE failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  83
  ERROR:  08-21-2011 14:09:57  INSERT failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  86
  ERROR:  08-21-2011 14:09:57  INSERT failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  86
  ERROR:  08-21-2011 14:09:57  UPDATE failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction   LINE  83
  ERROR:  08-21-2011 14:09:57  INSERT failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  86
  ERROR:  08-21-2011 14:09:57  UPDATE failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  83
  ERROR:  08-21-2011 14:09:59  UPDATE failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  83
  ERROR:  08-21-2011 14:09:59  UPDATE failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  83
  ERROR:  08-21-2011 14:10:01  UPDATE failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  83
  ERROR:  08-21-2011 14:10:01  INSERT failed in tb_ad_pull_log: Deadlock found when trying to get lock; try restarting transaction    LINE  86
  </code></pre>
  
  <p>Line 83 is the UPDATE statement in the PHP and 86 is the INSERT. Please keep in mind that this data can be written into this table at the rate of 5-8 transactions per second.</p>
  
  <p><em><strong>Additional info</em></strong></p>
  
  <p>With every INSERT and UPDATE into TABLE D a trigger executes that updates TABLE X and TABLE Y. Is this a reason for Table D to remain locked and hence the incoming requests gets a deadlock?</p>
  
  <p><em><strong>Finally got the problem but I am not sure how to solve it. The AFTER INSERT and AFTER UPDATE triggers on TABLE D locks the table when they are triggered and hence the incoming requests deadlock. Why I am so sure of this is because once I dropped these triggers the log stopped logging deadlock messages logged otherwise</em></strong></p>
  
  <p>Snippet of the Trigger code.</p>
  
  <pre><code>    CREATE DEFINER=CURRENT_USER TRIGGER tuadmin.t_update_CPM_updateBalance
  AFTER UPDATE
  ON tb_ad_pull_log
  FOR EACH ROW
  BEGIN
  
      DECLARE `cpm_value` decimal(10,4);
      DECLARE `clientid` int(4);
  
      /* Execute the below block if the requested ad is not the default ad */
      IF NEW.i_adid &lt;&gt; 1 THEN
  
          SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
              //do updates to TABLE X and Y logic
  END
  </code></pre>
  
  <p>This is where I do not understand why would these triggers hold a lock on Table D and not let any insert/update happen concurrently.</p>
  
  <p><em><strong>Will this avoid all problems if we drop the triggers and just call a SP from the PHP to do the work?</em></strong></p>