mysql 表触发器 行锁,MySQL中的触发器和表锁定

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:

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)

Make sure AlarmMembership table does not get modified meanwhile (e.g. the target MemberId gets deleted meanwhile).

Any advice is very welcome!

解决方案

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!