MySQL Bugs: #28502: Triggers that update another innodb table will block on X lock unnecessarily

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

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.

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.

Thank you for the report.

Verified as described.

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.

Pushed into 5.0.48

Pushed into 5.1.21-beta