bzr commit into mysql-5.5-bugteam branch (matthias.leich:3173) Bug#47745

[Matthias Leich]

#At file:///work2/repo/mysql-5.5-bugteam/ based on revid:matthias.leich@stripped

 3173 Matthias Leich	2010-12-10
      Fix for Bug#47745 innodb.innodb-timeout fails sporadically
      - Second scenario checked:
        Ensure via wait routines that the commit comes after the
        processing of the statement which should get finally
        the ER_LOCK_WAIT_TIMEOUT
        --> This should prevent the current bug.
      - First scenario checked:
        Ensure via wait routines that the statement is already waiting
        for getting the lock before the commit is given.
        --> No effect on the current bug, but ensure that the right
            scenario is reached.
      - Take care that disconnects are finished before the test ends.
        --> Reduce the potential to harm succeeding tests.
      - "Mangle" the printout of the current default innodb_lock_wait_timeout value
        --> No need to adjust the test in case the default gets changed in future.

    modified:
      mysql-test/suite/innodb/r/innodb-timeout.result
      mysql-test/suite/innodb/t/innodb-timeout.test
=== modified file 'mysql-test/suite/innodb/r/innodb-timeout.result'
--- a/mysql-test/suite/innodb/r/innodb-timeout.result	2010-04-16 16:19:07 +0000
+++ b/mysql-test/suite/innodb/r/innodb-timeout.result	2010-12-10 14:12:23 +0000
@@ -13,13 +13,14 @@ set global innodb_lock_wait_timeout=347;
 select @@innodb_lock_wait_timeout;
 @@innodb_lock_wait_timeout
 42
-set innodb_lock_wait_timeout=1;
+set innodb_lock_wait_timeout=3;
 select @@innodb_lock_wait_timeout;
 @@innodb_lock_wait_timeout
-1
+3
 select @@innodb_lock_wait_timeout;
 @@innodb_lock_wait_timeout
 347
+SET @connection_b_id = <connection_b_id>;
 create table t1(a int primary key)engine=innodb;
 begin;
 insert into t1 values(1),(2),(3);
@@ -35,4 +36,4 @@ select * from t1 for update;
 commit;
 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
 drop table t1;
-set global innodb_lock_wait_timeout=50;
+set global innodb_lock_wait_timeout=<initial_timeout>;

=== modified file 'mysql-test/suite/innodb/t/innodb-timeout.test'
--- a/mysql-test/suite/innodb/t/innodb-timeout.test	2010-04-16 16:19:07 +0000
+++ b/mysql-test/suite/innodb/t/innodb-timeout.test	2010-12-10 14:12:23 +0000
@@ -1,6 +1,6 @@
 -- source include/have_innodb.inc
 
-let $timeout=`select @@innodb_lock_wait_timeout`;
+let $initial_timeout=`select @@innodb_lock_wait_timeout`;
 set global innodb_lock_wait_timeout=42;
 
 connect (a,localhost,root,,);
@@ -12,19 +12,24 @@ set innodb_lock_wait_timeout=1;
 select @@innodb_lock_wait_timeout;
 
 connection b;
+let $connection_b_id=`SELECT CONNECTION_ID()`;
 select @@innodb_lock_wait_timeout;
 set global innodb_lock_wait_timeout=347;
 select @@innodb_lock_wait_timeout;
-set innodb_lock_wait_timeout=1;
+set innodb_lock_wait_timeout=3;
 select @@innodb_lock_wait_timeout;
 
 connect (c,localhost,root,,);
 connection c;
+
 select @@innodb_lock_wait_timeout;
-connection default;
+
 disconnect c;
+--source include/wait_until_disconnected.inc
 
 connection a;
+--replace_result $connection_b_id <connection_b_id>
+eval SET @connection_b_id = $connection_b_id;
 create table t1(a int primary key)engine=innodb;
 begin;
 insert into t1 values(1),(2),(3);
@@ -33,7 +38,37 @@ connection b;
 --send
 select * from t1 for update;
 
+# Observation on information_schema.processlist (2010-12 mysql-5.5)
+# -----------------------------------------------------------------
+# As soon as the server started the execution of the
+#   connection a: --send   select ... for update
+#   High parallel load could delay this up to 2 seconds.
+# and before either
+# - the innodb_lock_wait_timeout was exceeded
+#   -> connection b reap gets ER_LOCK_WAIT_TIMEOUT
+# or
+# - connection a commits, the lock disappears and the statement
+#   of connection b finishes
+#   -> connection b reap gets success + result set
+# we see within information_schema.processlist for connection b a row
+#    command  state        info
+#    Query    Sending data select * from t1 for update
+# The highest time value seen was @@innodb_lock_wait_timeout + 1.
+# Please note that there is unfortunately nothing which says
+# that we are just waiting for a lock.
+
 connection a;
+# High parallel load on the testing box can delay the
+# - start the execution of connection b   select * from t1 for update
+# - start to wait for the lock held by connection a
+# about up to two seconds.
+# In order to ensure that the execution of connection b ... has really started
+# (-> INFO = ...) and is most probably waiting for the lock now we poll on
+# information_schema.processlist.
+let $wait_timeout= 10;
+let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
+WHERE id = @connection_b_id AND INFO = 'select * from t1 for update';
+--source include/wait_condition.inc
 commit;
 
 connection b;
@@ -48,17 +83,31 @@ connection b;
 select * from t1 for update;
 
 connection a;
-sleep 2;
+# Wait till the execution of the connection b statement was started.
+let $wait_timeout= 10;
+let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
+WHERE id = @connection_b_id AND INFO = 'select * from t1 for update';
+--source include/wait_condition.inc
+# Wait till the execution of the connection b statement has ended.
+let $wait_timeout= 10;
+let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
+WHERE id = @connection_b_id AND INFO IS NULL;
+--source include/wait_condition.inc
+# Give "commit" though this must be too late for the statement of connection b.
 commit;
 
 connection b;
 --error ER_LOCK_WAIT_TIMEOUT
 reap;
-drop table t1;
 
-connection default;
+disconnect b;
+--source include/wait_until_disconnected.inc
 
+connection a;
 disconnect a;
-disconnect b;
+--source include/wait_until_disconnected.inc
 
-eval set global innodb_lock_wait_timeout=$timeout;
+connection default;
+drop table t1;
+--replace_result $initial_timeout <initial_timeout>
+eval set global innodb_lock_wait_timeout=$initial_timeout;


Attachment: [text/bzr-bundle] bzr/[e-mail]