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
Tags: bfsm_2007_03_01, innodb, myisam, table lock, trigger

[7 Feb 2007 10:01] Shane Bester
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.
[7 Feb 2007 10:42] Valerii Kravchuk
Thank you for a problem report. Verified just as described. With two InnoDB tables INSERT from second connection works immediately.
[15 Feb 2007 10:55] Valerii Kravchuk
Bug #26396 looks related (same locking, but for stored functions).
[25 May 2007 8:01] Konstantin Osipov
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).
[7 Jun 2007 14:52] Dmitry Lenev
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.
[9 Jul 2007 21:50] Bugs System
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.
[10 Jul 2007 10:51] Bugs System
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.
[12 Jul 2007 18:28] Bugs System
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.
[12 Jul 2007 18:30] Konstantin Osipov
Alexander reviewed over email.
[12 Jul 2007 18:30] Konstantin Osipov
Queued into 5.0-runtime
[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
[18 Jul 2007 10:43] Konstantin Osipov
Bug#27248 Triggers: error if insert affects temporary table was marked a duplicate of this bug.
[25 Jul 2007 3:27] Paul Dubois
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.
[4 Jun 2008 11:16] Bugs System
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
[4 Jun 2008 11:19] Bugs System
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
[3 Dec 2009 11:48] Bugs System
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)
[16 Feb 2010 16:49] Bugs System
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)
[16 Feb 2010 16:59] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091203114705-mleuhz52to8e0jpu) (pib:16)
[17 Feb 2010 0:30] Paul Dubois
Noted in 6.0.14 changelog.

Setting report to Need Merge pending push of Celosia into release tree.
[6 Mar 2010 10:58] Bugs System
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)
[6 Mar 2010 20:16] Paul Dubois
Noted in 5.5.3 changelog.