Debian Codegraph @

mysql-test/suite/innodb/t/innodb-timeout.test

raw
1
2 let $initial_timeout=`select @@innodb_lock_wait_timeout`;
3 set global innodb_lock_wait_timeout=42;
4
5 connect (a,localhost,root,,);
6 connect (b,localhost,root,,);
7
8 connection a;
9 select @@innodb_lock_wait_timeout;
10 set innodb_lock_wait_timeout=1;
11 select @@innodb_lock_wait_timeout;
12
13 connection b;
14 let $connection_b_id=`SELECT CONNECTION_ID()`;
15 select @@innodb_lock_wait_timeout;
16 set global innodb_lock_wait_timeout=347;
17 select @@innodb_lock_wait_timeout;
18 set innodb_lock_wait_timeout=10;
19 select @@innodb_lock_wait_timeout;
20
21 connect (c,localhost,root,,);
22 connection c;
23
24 select @@innodb_lock_wait_timeout;
25
26 disconnect c;
27 --source include/wait_until_disconnected.inc
28
29 connection a;
30 --replace_result $connection_b_id <connection_b_id>
31 eval SET @connection_b_id = $connection_b_id;
32 create table t1(a int primary key)engine=innodb;
33 begin;
34 insert into t1 values(1),(2),(3);
35
36 connection b;
37 --send
38 select * from t1 for update;
39
40 # Observation on information_schema.processlist (2010-12 mysql-5.5)
41 # -----------------------------------------------------------------
42 # As soon as the server started the execution of the
43 # connection a: --send select ... for update
44 # High parallel load could delay this up to two seconds.
45 # and before either
46 # - the innodb_lock_wait_timeout was exceeded
47 # -> connection b reap gets ER_LOCK_WAIT_TIMEOUT
48 # or
49 # - connection a commits, the lock disappears and the statement
50 # of connection b finishes
51 # -> connection b reap gets success + result set
52 # we see within information_schema.processlist for connection b a row
53 # command state info
54 # Query Sending data select * from t1 for update
55 # The highest time value seen was @@innodb_lock_wait_timeout + 1.
56 # Please note that there is unfortunately nothing which says
57 # that we are just waiting for a lock.
58
59 connection a;
60 # In order to ensure that the execution of
61 # connection b: select * from t1 for update
62 # has really started and is most probably waiting for the lock now we poll on
63 # information_schema.processlist.
64 # Also our current session innodb_lock_wait_timeout of 10 seconds should big
65 # enough to prevent that connection b ends up with getting ER_LOCK_WAIT_TIMEOUT.
66 #
67 let $wait_timeout= 10;
68 let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
69 WHERE id = @connection_b_id AND INFO = 'select * from t1 for update';
70 --source include/wait_condition.inc
71 commit;
72
73 connection b;
74 reap;
75
76 connection a;
77 begin;
78 insert into t1 values(4);
79
80 connection b;
81 set innodb_lock_wait_timeout=3;
82 # 3 seconds should be big enough that the wait routine of connection a will
83 # hit the time span where our next statement is visible within the
84 # information_schema.processlist.
85 --send
86 select * from t1 for update;
87
88 connection a;
89 # Wait till the execution of the connection b statement was started.
90 let $wait_timeout= 10;
91 let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
92 WHERE id = @connection_b_id AND INFO = 'select * from t1 for update';
93 --source include/wait_condition.inc
94 # Wait till the execution of the connection b statement has ended.
95 let $wait_timeout= 10;
96 let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
97 WHERE id = @connection_b_id AND INFO IS NULL;
98 --source include/wait_condition.inc
99 # Give "commit" though this must be too late for the statement of connection b.
100 commit;
101
102 connection b;
103 --error ER_LOCK_WAIT_TIMEOUT
104 reap;
105
106 disconnect b;
107 --source include/wait_until_disconnected.inc
108
109 connection a;
110 disconnect a;
111 --source include/wait_until_disconnected.inc
112
113 connection default;
114 drop table t1;
115 --replace_result $initial_timeout <initial_timeout>
116 eval set global innodb_lock_wait_timeout=$initial_timeout;