Bug #11896 Partial locking in case of recursive trigger definittions
Submitted: 13 Jul 2005 2:08 Modified: 26 Aug 2005 20:17
Reporter: Omer Barnir (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.10 OS:Linux (Linux / All)
Assigned to: Dmitry Lenev CPU Architecture:Any

[13 Jul 2005 2:08] Omer Barnir
Description:
Table locking in not handled correctly and a wrong error messageis returned when triggers are defined in a recursive manner, ie 
 - a  before insert trigger is set on table 1 and inserts on table 2
 - a  before insert trigger is set on table 2 and inserts on table 3
 - a  before insert trigger is set on table 3 and inserts on table 4
 - a  before insert trigger is set on table 4 and inserts on table 1

What is observed is that on insert to table 1, the triggers are fired and rows are inserted to tables 2,3,4 and back at one, but at that point a locking error is returned on table 2.
- It seems that 'recursive locking is only partly implemented (one level for 
  table '1' -and not more)
- If recursive locking is not supported it should have failed on 
  the second insert to table '1' (not table 2) 
- A proper error message needs to be returned

As described below:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.10-beta-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t2;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t3;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t4;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (f1 integer) engine = myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 (f2 integer) engine = myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t3 (f3 integer) engine = myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t4 (f4 integer) engine = myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> create trigger tr1 after insert on t1
    -> for each row insert into t2 (f2) values (new.f1+1);
Query OK, 0 rows affected (0.00 sec)

mysql> create trigger tr2 after insert on t2
    -> for each row insert into t3 (f3) values (new.f2+1);
Query OK, 0 rows affected (0.00 sec)

mysql> create trigger tr3 after insert on t3
    -> for each row insert into t4 (f4) values (new.f3+1);
Query OK, 0 rows affected (0.00 sec)

mysql> create trigger tr4 after insert on t4
    -> for each row insert into t1 (f1) values (new.f4+1);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1);
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES
mysql> select * from t1;
+------+
| f1   |
+------+
|    1 |
|    5 |
+------+
2 rows in set (0.00 sec)
OBN> The second insert to t1 happend (value '5').

mysql> select * from t2;
+------+
| f2   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> select * from t3;
+------+
| f3   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

mysql> select * from t4;
+------+
| f4   |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

mysql>

Note: That in the case of innodb, the same error is observed but the selects
         return nothing since the whole transaction is rolled back

How to repeat:
use test;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
create table t1 (f1 integer) engine = myisam;
create table t2 (f2 integer) engine = myisam;
create table t3 (f3 integer) engine = myisam;
create table t4 (f4 integer) engine = myisam;
create trigger tr1 after insert on t1
    for each row insert into t2 (f2) values (new.f1+1);
create trigger tr2 after insert on t2
    for each row insert into t3 (f3) values (new.f2+1);
create trigger tr3 after insert on t3
    for each row insert into t4 (f4) values (new.f3+1);
create trigger tr4 after insert on t4
    for each row insert into t1 (f1) values (new.f4+1);
insert into t1 values (1);

select * from t1;
select * from t2;
select * from t3;
select * from t4;
[18 Aug 2005 15:06] 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/internals/28462
[23 Aug 2005 6:27] Dmitry Lenev
Fixed in 5.0.12

When we are executing stored function or trigger we should ensure that we won't change table that is already used by statement which invoked this function/trigger (this can damage table or easily cause infinite loops).
Particularly this means that recursive triggers are disallowed.
[26 Aug 2005 20:17] Paul Dubois
Noted in 5.0.12 changelog.
[24 Jul 2006 15:38] Tomash Brechko
Bug#12604 is a duplicate of this bug.