Re: Locking problem with triggers

[Carl Dunham]

We are seeing something similar. This is not a full test case, but an illustration derived from our code:

update `a` join `b` on `a`.`id`=`b`.`aid` set `b`.`something`="hello" where `a`.`acctid`=1234 and `b`.`something`="there";

There is an update trigger on `b` that checks for some columns being changed (not including `something`). In many cases, the result set impacted by the above update can be tens of thousands of records. Tables `a` and `b` are innodb, and have millions of rows each.

Once the trigger starts, queries like:

update `a` set `a`.`foo`=27;

lock, *even if they were running before the above update starts*. Effectively, big retroactive table locks get placed on both `a` and `b`.

Adding an index on `b` for `something` didn't help.

Any ideas?