Triggers and table lock in MySQL

Question

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.

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 (ERROR: Error Code: 1314. LOCK is not allowed in stored procedures):

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 ;

The goals to achieve with these LOCKS (or alternative constructs) are:

1. 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)
2. Make sure AlarmMembership table does not get modified meanwhile (e.g. the target MemberId gets deleted meanwhile).

Any advice is very welcome!

Answer 1

I think the best way to handle this would be to use the SELECT ... FOR UPDATE pattern described here: http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

For reference:

Let us look at another example: We have an integer counter field in a table child_codes that we use to assign a unique identifier to each child added to table child. It is not a good idea to use either consistent read or a shared mode read to read the present value of the counter because two users of the database may then see the same value for the counter, and a duplicate-key error occurs if two users attempt to add children with the same identifier to the table.

Here, LOCK IN SHARE MODE is not a good solution because if two users read the counter at the same time, at least one of them ends up in deadlock when it attempts to update the counter.

To implement reading and incrementing the counter, first perform a locking read of the counter using FOR UPDATE, and then increment the counter. For example:

 SELECT counter_field FROM child_codes FOR UPDATE; UPDATE child_codes
 SET counter_field = counter_field + 1; 

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row > it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

. . .

Note Locking of rows for update using SELECT FOR UPDATE only applies when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0. If autocommit is enabled, the rows matching the specification are not locked.

So in your case, you would replace

LOCK TABLES AlarmCount WRITE, AlarmMembership READ;
  UPDATE AlarmCount SET num = num - 1 
  WHERE RuleId = OLD.RuleId AND
      MemberId = 0 AND
      IsResolved = OLD.IsResolved;

With something like

SELECT num FROM AlarmCount WHERE RuleId = OLD.RuleId AND
          MemberId = 0 AND
          IsResolved = OLD.IsResolved FOR UPDATE;
UPDATE AlarmCount SET num = num - 1;

I say "something like" because it's not entirely clear to me what OLD.RuleId and OLD.IsResolved is referencing. Also worth noting from http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html is:

The preceding description is merely an example of how SELECT ... FOR UPDATE works. In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table:

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field +
1); 
SELECT LAST_INSERT_ID();

The SELECT statement merely retrieves the identifier information (specific to the current connection). It does not access any table.

In other words, you can probably optimize this pattern further by only accessing the table once... but again there's some details about your schema that I don't quite follow, and I'm not sure I could provide the actual statement you'd need. I do think if you take a look SELECT ... FOR UPDATE, though, that you'll see what the pattern boils down to, and what you need to do to make this work in your environment.

I should mention as well that there are some storage engine environment and transaction isolation levels that you'll want to consider. There is a very, very good discussion on SO on this topic here: When to use SELECT ... FOR UPDATE?

Hope this helps!