MySQL Bugs: #26141: mixing table types in trigger causes full table lock on innodb table

Bug #26141	mixing table types in trigger causes full table lock on innodb table
Submitted:	7 Feb 2007 10:01	Modified:	6 Mar 2010 20:16
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.36, 5.1.16	OS:	Linux (linux, windows)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any
Tags:	bfsm_2007_03_01, innodb, myisam, table lock, trigger

Description:
when a trigger changes tables which are of different engines, innodb tables become locked and no other threads can insert into them until the trigger completes.

|  1 | root | localhost | test | Query | 7 | init   | select sleep(30) into @a
|  2 | root | localhost | test | Query | 4 | Locked | insert into t1 values (9)

1       test.t2                     Locked - write        High priority write lock
1       test.t1                     Locked - write        High priority write lock
2       test.t2                     Waiting - write       High priority write lock

in the below testcase, changing both tables to be innodb causes the problem to go away.  

How to repeat:
#mysqld_safe started with no options, so the defaults apply.

# setup tables and trigger

drop table if exists `t1`;
drop table if exists `t2`;
drop trigger if exists `trig`;
create table `t1`(`c` int)engine=innodb;
create table `t2`(`c` int)engine=myisam;
delimiter ;;
create trigger `trig` after update on `t1` for each row
begin
	insert into `t2`(`c`) values (1);
	select sleep(30) into @a;
end ;;
delimiter ;
insert into `t1`(`c`) values (1);

#in connection1
update `t1` set `c`=2 where `c`=1;

#in connection2, while #connection1 is still running
insert into `t1` values (9);

Suggested fix:
not sure.

Thank you for a problem report. Verified just as described. With two InnoDB tables INSERT from second connection works immediately.

Bug #26396 looks related (same locking, but for stored functions).

Bug#28502 "Triggers that update another innodb table will block on X lock unnecessarily" was marked a duplicate of this bug.
Please ensure that a test case from Bug#28502 is added to the test suite when this bug is closed (a slightly different manifestation of the problem).

Investigation has shown that this problem stems from the fact that code in open_tables() which is responsible for adding tables used by triggers to the table list for prelocking does not distinguish types of statements. In example above table 't2' added to the prelocking list for both UPDATE (which is natural) and INSERT statement (which is not effecient). Indeed attempt to lock this MyISAM tables from both statements prevents their concurrent execution.

This issue can be solved by changing open_tables() in such way that it will add only really required tables to the prelocking list. To do this we need to make this routine aware of exact type of operation (INSERT, UPDATE, DELETE or some combination of them) which is going to be perfomed by current statement on particular table. The complete solution for this problem should also modify
algorithm which calculate sets of tables used by routines/triggers in such way
that it will take this information into account as well.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30574

ChangeSet@1.2517, 2007-07-10 01:50:20+04:00, kostja@bodhi.(none) +20 -0
  A fix and a test case for Bug#26141 "mixing table types in trigger 
  causes full table lock on innodb table".
  Also fixes Bug #28502 Triggers that update another innodb table 
  will block on X lock unnecessarily (duplciate).
  
  Both bug synopsises are misleading, InnoDB table in both cases is
  not X locked. The statements, however, cannot proceed concurrently,
  just as described in the bug reprots.
  
  If a user had an InnoDB table, and two triggers, AFTER UPDATE and 
  AFTER INSERT, competing for the same resource (e.g. a MyISAM table), 
  then INSERTS/UPDATES of the InnoDB table were not concurrent any more.
  The problem had other side-effects (see respective bug reports).
  
  This behavior was a consequence of a shortcoming of the pre-locking
  algorithm, which would not distinguish between different DML operations
  (e.g. INSERT and DELETE) and pre-lock all the tables
  that are used by any trigger defined on the subject table.
  
  The idea of the fix is to extend the pre-locking algorithm to keep track,
  for each table, what DML operation it is used for and not
  load triggers that are known to never be fired.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30592

ChangeSet@1.2517, 2007-07-10 14:51:33+04:00, kostja@bodhi.(none) +19 -0
  A fix and a test case for Bug#26141 mixing table types in trigger 
  causes full table lock on innodb table.
  Also fixes Bug#28502 Triggers that update another innodb table 
  will block on X lock unnecessarily (duplciate).
  
  Both bugs' synopses are misleading: InnoDB table is
  not X locked. The statements, however, cannot proceed concurrently, 
  but this happens due to lock conflicts for tables used in triggers,
  not for the InnoDB table. 
  
  If a user had an InnoDB table, and two triggers, AFTER UPDATE and 
  AFTER INSERT, competing for different resources (e.g. two distinct
  MyISAM tables), then these two triggers would not be able to execute
  concurrently. Moreover, INSERTS/UPDATES of the InnoDB table would
  not be able to run concurrently. 
  The problem had other side-effects (see respective bug reports).
  
  This behavior was a consequence of a shortcoming of the pre-locking
  algorithm, which would not distinguish between different DML operations
  (e.g. INSERT and DELETE) and pre-lock all the tables
  that are used by any trigger defined on the subject table.
  
  The idea of the fix is to extend the pre-locking algorithm to keep track,
  for each table, what DML operation it is used for and not
  load triggers that are known to never be fired.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30825

ChangeSet@1.2517, 2007-07-12 22:26:41+04:00, kostja@bodhi.(none) +18 -0
  A fix and a test case for Bug#26141 mixing table types in trigger 
  causes full table lock on innodb table.
  Also fixes Bug#28502 Triggers that update another innodb table 
  will block on X lock unnecessarily (duplciate).
  Code review fixes.
  
  Both bugs' synopses are misleading: InnoDB table is
  not X locked. The statements, however, cannot proceed concurrently, 
  but this happens due to lock conflicts for tables used in triggers,
  not for the InnoDB table. 
  
  If a user had an InnoDB table, and two triggers, AFTER UPDATE and 
  AFTER INSERT, competing for different resources (e.g. two distinct
  MyISAM tables), then these two triggers would not be able to execute
  concurrently. Moreover, INSERTS/UPDATES of the InnoDB table would
  not be able to run concurrently. 
  The problem had other side-effects (see respective bug reports).
  
  This behavior was a consequence of a shortcoming of the pre-locking
  algorithm, which would not distinguish between different DML operations
  (e.g. INSERT and DELETE) and pre-lock all the tables
  that are used by any trigger defined on the subject table.
  
  The idea of the fix is to extend the pre-locking algorithm to keep track,
  for each table, what DML operation it is used for and not
  load triggers that are known to never be fired.

Alexander reviewed over email.

Queued into 5.0-runtime

Pushed into 5.0.48

Pushed into 5.1.21-beta

Bug#27248 Triggers: error if insert affects temporary table was marked a duplicate of this bug.

Noted in 5.0.48, 5.1.21 changelogs.

If an operation had an InnoDB table, and two triggers, AFTER UPDATE
and AFTER INSERT, competing for different resources (such as two
distinct MyISAM tables), the triggers were unable to execute
concurrently. In addition, INSERTS and UPDATES for the InnoDB table
were unable to run concurrently.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/47413

2645 Konstantin Osipov	2008-06-04
      Fix a code regression (not observable externally) that I introduced
      in the fix for Bug#26141

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/47414

2645 Konstantin Osipov	2008-06-04
      Fix a code regression (not observable externally) that I introduced
      in the fix for Bug#26141

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92661

2980 Konstantin Osipov	2009-12-03
      Backport of:
      ----------------------------------------------------------
      revno: 2630.2.7
      committer: Konstantin Osipov <konstantin@mysql.com>
      branch nick: mysql-6.0-runtime
      timestamp: Wed 2008-06-04 15:18:52 +0400
      message:
        Fix a code regression (not observable externally) that I introduced
        in the fix for Bug#26141
      
      (backporting as part of all patches related to WL#3726)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091203114705-mleuhz52to8e0jpu) (pib:16)

Noted in 6.0.14 changelog.

Setting report to Need Merge pending push of Celosia into release tree.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)

Noted in 5.5.3 changelog.