Triggers and table lock
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
Subject
Views
Written By
Posted
Triggers and table lock
2589
June 19, 2014 01:09AM
1238
June 19, 2014 08:12AM
1250
June 20, 2014 02:30AM
1159
June 20, 2014 06:21AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.