maria-developers team mailing list archive

[knielsen@xxxxxxxxxxxxxxx]

#At lp:maria

 2777 knielsen@xxxxxxxxxxxxxxx	2009-10-11
      Bug#37635: Test read_many_rows_innodb is failing w/ wrong error message (lock_wait_timeout)
      
      The test is supposed to provoke a deadlock, to test a crash of the server in
      some deadlock scenarios.
      
      The problem is that in recent version of MySQL, no deadlock occurs. It is not
      clear why a deadlock should be expected. One transaction does an insert+delete
      on primary key value 1 in table t2. The other transaction does an insert on
      primary key value 123. There seems no reason that these should conflict, so
      there is no deadlock.
      
      (Presumably an earlier version did get a deadlock due to maybe taking excessive
      locks on the insert+delete of primary key value 1. The table has only one row,
      so maybe a different locking was used ("Impossible WHERE noticed after reading
      const tables"))
      
      Fix is to have transaction 1 lock PK 1 and transaction 2 lock PK 123. Then have
      transaction 2 access PK 1 (wait) and transaction 1 access PK 123 (deadlock).
      
      It has been checked that this modified test case still crashes the server in
      the same way prior to fix of the original bug (Bug#24989).
      modified:
        mysql-test/include/read_many_rows.inc
        mysql-test/r/read_many_rows_innodb.result

per-file messages:
  mysql-test/include/read_many_rows.inc
    Provoke deadlock on same PK value in the two transactions to ensure that the
    deadlock does occur independently on whatever execution plan is chosen.
  mysql-test/r/read_many_rows_innodb.result
    Result file update.
=== modified file 'mysql-test/include/read_many_rows.inc'
--- a/mysql-test/include/read_many_rows.inc	2007-08-05 05:36:54 +0000
+++ b/mysql-test/include/read_many_rows.inc	2009-10-11 09:06:55 +0000
@@ -71,8 +71,8 @@ CREATE TRIGGER t1_bi before INSERT
 BEGIN
   DECLARE CONTINUE HANDLER FOR SQLSTATE '40001' SET @a:= 'deadlock';
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @a:= 'exception';
-  INSERT INTO t2 (f2) VALUES (1);
-  DELETE FROM t2 WHERE f2 = 1;
+  INSERT INTO t2 (f2) VALUES (NEW.f1);
+  DELETE FROM t2 WHERE f2 = NEW.f1;
 END;|
 
 CREATE PROCEDURE proc24989()
@@ -109,7 +109,7 @@ send insert into t1 values(1);
 
 connection con1;
 --sleep 1
-insert into t1 values(1);
+insert into t1 values(123);
 
 connection con2;
 --error 1213
@@ -130,7 +130,7 @@ send call proc24989();
 
 connection con1;
 --sleep 1
-insert into t1 values(1);
+insert into t1 values(123);
 
 connection con2;
 reap;
@@ -150,7 +150,7 @@ send call proc24989_2();
 
 connection con1;
 --sleep 1
-insert into t1 values(1);
+insert into t1 values(123);
 commit;
 
 connection con2;

=== modified file 'mysql-test/r/read_many_rows_innodb.result'
--- a/mysql-test/r/read_many_rows_innodb.result	2007-08-05 05:36:54 +0000
+++ b/mysql-test/r/read_many_rows_innodb.result	2009-10-11 09:06:55 +0000
@@ -40,8 +40,8 @@ ON t1 FOR EACH ROW
 BEGIN
 DECLARE CONTINUE HANDLER FOR SQLSTATE '40001' SET @a:= 'deadlock';
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @a:= 'exception';
-INSERT INTO t2 (f2) VALUES (1);
-DELETE FROM t2 WHERE f2 = 1;
+INSERT INTO t2 (f2) VALUES (NEW.f1);
+DELETE FROM t2 WHERE f2 = NEW.f1;
 END;|
 CREATE PROCEDURE proc24989()
 BEGIN
@@ -63,7 +63,7 @@ insert into t1 values(1);
 start transaction;
 insert into t2 values(123);
 insert into t1 values(1);
-insert into t1 values(1);
+insert into t1 values(123);
 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
 select @a;
 @a
@@ -76,7 +76,7 @@ insert into t1 values(1);
 start transaction;
 insert into t2 values(123);
 call proc24989();
-insert into t1 values(1);
+insert into t1 values(123);
 select @a,@b;
 @a	@b
 exception	deadlock
@@ -88,7 +88,7 @@ insert into t1 values(1);
 start transaction;
 insert into t2 values(123);
 call proc24989_2();
-insert into t1 values(1);
+insert into t1 values(123);
 commit;
 exception
 Outer handler