Bug #26141 mixing table types in trigger causes full table lock on innodb table
Submitted: 7 Feb 2007 11:01 Modified: 25 Jul 2007 5:27
Reporter: Shane Bester
Status: Closed
Category:Server: SP Severity:S3 (Non-critical)
Version:5.0.36, 5.1.16 OS:Linux (linux, windows)
Assigned to: Konstantin Osipov Target Version:
Tags: bfsm_2007_03_01, trigger, table lock, innodb, myisam

[7 Feb 2007 11: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 11:42] Valeriy Kravchuk 
Thank you for a problem report. Verified just as described. With two InnoDB tables INSERT
from second connection works immediately.
[15 Feb 2007 11:55] Valeriy Kravchuk 
Bug #26396 looks related (same locking, but for stored functions).
[25 May 2007 10: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 16:52] Dmitri 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 23: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 12: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 20: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 20:30] Konstantin Osipov 
Alexander reviewed over email.
[12 Jul 2007 20:30] Konstantin Osipov 
Queued into 5.0-runtime
[17 Jul 2007 17:30] Bugs System 
Pushed into 5.0.48
[17 Jul 2007 17:31] Bugs System 
Pushed into 5.1.21-beta
[18 Jul 2007 12:43] Konstantin Osipov 
Bug#27248 Triggers: error if insert affects temporary table was marked a duplicate of this
bug.
[25 Jul 2007 5: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 13: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 13: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 12: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)