Wednesday, February 11, 2009

Triggers, table locks and NDB

At a customer yesterday, I confirmed what Jonas suspected and what is probably related to bug 42474. Scroll down and look for the output of SHOW PROCESSLIST.
It seems that TRIGGERs causes Table Locks to be taken when used with Ndb cluster tables!! I have created a bug report .

If we do an update on a table that has an update trigger the trigger will upon execution lock the entire table that is affected by the trigger.

This was verified by having several threads updating random records in a table. When one update gets to execute, the trigger will block the other updates from happening. This was shown using SHOW FULL PROCESSLIST¸ which shows a bunch of statements being in the state Locked. Also, the Table_locks_waited (SHOW GLOBAL STATUS) was also constantly increasing.

This shouldn't happen as Cluster uses Row-level locking. Innodb handles this by downgrading the table lock to a row-level lock.

Removing the TRIGGER "fixed" the problem (i.e, proving it is broken).

So if you experience terrible scalability with Cluster and Triggers, then this may explain why..

2 comments:

Doc said...

I could be completely missing the point, but the FAQ says

24.5.3: Does MySQL 5.1 have statement-level or row-level triggers?

In MySQL 5.1, all triggers are FOR EACH ROW — that is, the trigger is activated for each row that is inserted, updated, or deleted. MySQL 5.1 does not support triggers using FOR EACH STATEMENT.

Johan Andersson said...

Hello,The trigger is indeed executed for each row, but the side effect for NDB is that a table lock is taken on the mysql server where the trigger fires. It is unnecessary since NDB has row level locks.