title: Triggers and table lock in MySQL
body: <p>Scenario: I have some triggers that keep track of number of records of one table, together with other useful information. These triggers are fired upon add/delete/update on this table and take care of writing this information in another complementary table.</p>
  
  <p>Now these triggers will run on a multi-threaded environment where possibly I may have concurrent access to tables. 
  I wish I could make something like this, but it is forbidden (<strong>ERROR: Error Code: 1314. LOCK is not allowed in stored procedures</strong>):</p>
  
  <pre><code>DELIMITER $$
  DROP TRIGGER IF EXISTS del_alarmCount$$
  CREATE TRIGGER del_alarmCount AFTER DELETE ON Alarm
  FOR EACH ROW
  BEGIN
  SET autocommit=0;
  LOCK TABLES AlarmCount WRITE, AlarmMembership READ;
    UPDATE AlarmCount SET num = num - 1 
    WHERE RuleId = OLD.RuleId AND
        MemberId = 0 AND
        IsResolved = OLD.IsResolved;
  
    UPDATE AlarmCount SET num = num - 1 
    WHERE RuleId = OLD.RuleId AND
        IsResolved = OLD.IsResolved AND
        MemberId IN (SELECT MemberId FROM AlarmMembership WHERE AlarmId=OLD.Id);
  COMMIT;
  UNLOCK TABLES;
  END $$
  DELIMITER ;
  </code></pre>
  
  <p>The goals to achieve with these LOCKS (or alternative constructs) are:</p>
  
  <ol>
  <li>Avoid two triggers running simultaneously write on AlarmCount table and update related records (I guess I may have two triggers running for different records of Alarm table updating the same record of AlarmCount)</li>
  <li>Make sure AlarmMembership table does not get modified meanwhile (e.g. the target MemberId gets deleted meanwhile).</li>
  </ol>
  
  <p>Any advice is very welcome!</p>