Triggers and table lock

[Matteo Porru]

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 ancillary 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) should be:

- Avoid two triggers running simultaneously write on AlarmCount table and update related records. In fact I fear I may have two triggers fired by different records of Alarm table, updating the same record of AlarmCount (at the same time)
- Make sure AlarmMembership table does not get modified meanwhile (e.g. the target MemberId gets deleted meanwhile).

how can I achieve this? Any workaround?
Thank you in advance