Bug #28502 Triggers that update another innodb table will block on X lock unnecessarily
Submitted: 17 May 2007 19:42 Modified: 25 May 2007 8:00
Reporter: Ed Dawley Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.41, 5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb, locking, trigger

[17 May 2007 19:42] Ed Dawley
Description:
There are possibly 2 bugs here:

- a trigger that does not fire will still obtain an X lock on another table if that table is updated within the trigger
- a trigger still obtains an X lock on another table even if it does not actually attempt to update that table

How to repeat:
Setup
------
drop table if exists parent;
drop table if exists child;
create table parent (id int unsigned not null, count int unsigned not null, primary key (id)) engine = innodb;
create table child (id int unsigned not null, parent_id int unsigned, primary key (id)) engine = innodb;

delimiter |

CREATE TRIGGER child_update AFTER UPDATE on child FOR EACH ROW BEGIN
                                IF(NEW.parent_id IS NOT NULL) THEN
                                        UPDATE parent SET count = count + 1 WHERE id = NEW.parent_id;
                                END IF;
                        END;
|

delimiter ;

insert into parent set id = 1, count = 0;

Client A
--------
lock table parent write;

Client B
--------
insert into child set id = 1;

B will block until A releases the table lock.

Suggested fix:
Obtain a pessimistic X lock when the update in the trigger actually fires.  B should not block.
[17 May 2007 20:29] Ed Dawley
The initial bug description was incorrect.  The problem is not that the trigger obtains an X lock but that it must perform a check on tables that it could possibly touch.  I do not know if this places any locks.  It does cause needless blocking and/or errors.

Another example:

drop table if exists ed;
drop table if exists a_table_that_does_not_exist;
create table ed (id int unsigned not null, a_num int not null, primary key (id)) engine = innodb;

delimiter |
CREATE TRIGGER ed_insert AFTER INSERT on ed FOR EACH ROW BEGIN
                                IF(NEW.a_num = 'a string') THEN
                                        UPDATE a_table_that_does_not_exist  SET count = count + 1 WHERE id = NEW.id;
                                END IF;
                        END;

|

delimiter ;

insert into ed set id = 1;

ERROR 1100 (HY000): Table 'a_table_that_does_not_exist' was not locked with LOCK TABLES

You should never get this error since the column definition does not allow a_num to ever equal a string.
[17 May 2007 21:26] Sveta Smirnova
Thank you for the report.

Verified as described.
[25 May 2007 8:00] Konstantin Osipov
Thank you for your bug report.
This bug is a duplicate of Bug#26141. Please subscribe to the older bug to track progress of your problem.
[17 Jul 2007 15:30] Bugs System
Pushed into 5.0.48
[17 Jul 2007 15:31] Bugs System
Pushed into 5.1.21-beta