Debian Codegraph @

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

raw
1 # Save the initial number of concurrent sessions.
2 --source include/count_sessions.inc
3 --source include/have_innodb.inc
4
5 let $initial_timeout=`select @@innodb_lock_wait_timeout`;
6 set global innodb_lock_wait_timeout=42;
7
8 connect (a,localhost,root,,);
9 connect (b,localhost,root,,);
10
11 connection a;
12 select @@innodb_lock_wait_timeout;
13 set innodb_lock_wait_timeout=1;
14 select @@innodb_lock_wait_timeout;
15
16 connection b;
17 let $connection_b_id=`SELECT CONNECTION_ID()`;
18 select @@innodb_lock_wait_timeout;
19 set global innodb_lock_wait_timeout=347;
20 select @@innodb_lock_wait_timeout;
21 set innodb_lock_wait_timeout=10;
22 select @@innodb_lock_wait_timeout;
23
24 connect (c,localhost,root,,);
25 connection c;
26
27 select @@innodb_lock_wait_timeout;
28
29 disconnect c;
30 --source include/wait_until_disconnected.inc
31
32 connection a;
33 --replace_result $connection_b_id <connection_b_id>
34 eval SET @connection_b_id = $connection_b_id;
35 create table t1(a int primary key)engine=innodb;
36 begin;
37 insert into t1 values(1),(2),(3);
38
39 connection b;
40 --send
41 select * from t1 for update;
42
43 # Observation on information_schema.processlist (2010-12 mysql-5.5)
44 # -----------------------------------------------------------------
45 # As soon as the server started the execution of the
46 # connection a: --send select ... for update
47 # High parallel load could delay this up to two seconds.
48 # and before either
49 # - the innodb_lock_wait_timeout was exceeded
50 # -> connection b reap gets ER_LOCK_WAIT_TIMEOUT
51 # or
52 # - connection a commits, the lock disappears and the statement
53 # of connection b finishes
54 # -> connection b reap gets success + result set
55 # we see within information_schema.processlist for connection b a row
56 # command state info
57 # Query Sending data select * from t1 for update
58 # The highest time value seen was @@innodb_lock_wait_timeout + 1.
59 # Please note that there is unfortunately nothing which says
60 # that we are just waiting for a lock.
61
62 connection a;
63 # In order to ensure that the execution of
64 # connection b: select * from t1 for update
65 # has really started and is most probably waiting for the lock now we poll on
66 # information_schema.processlist.
67 # Also our current session innodb_lock_wait_timeout of 10 seconds should big
68 # enough to prevent that connection b ends up with getting ER_LOCK_WAIT_TIMEOUT.
69 #
70 let $wait_timeout= 10;
71 let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
72 WHERE id = @connection_b_id AND INFO = 'select * from t1 for update';
73 --source include/wait_condition.inc
74 commit;
75
76 connection b;
77 reap;
78
79 connection a;
80 begin;
81 insert into t1 values(4);
82
83 connection b;
84 set innodb_lock_wait_timeout=3;
85 # 3 seconds should be big enough that the wait routine of connection a will
86 # hit the time span where our next statement is visible within the
87 # information_schema.processlist.
88 --send
89 select * from t1 for update;
90
91 connection a;
92 # Wait till the execution of the connection b statement was started.
93 let $wait_timeout= 10;
94 let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
95 WHERE id = @connection_b_id AND INFO = 'select * from t1 for update';
96 --source include/wait_condition.inc
97 # Wait till the execution of the connection b statement has ended.
98 let $wait_timeout= 10;
99 let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
100 WHERE id = @connection_b_id AND INFO IS NULL;
101 --source include/wait_condition.inc
102 # Give "commit" though this must be too late for the statement of connection b.
103 commit;
104
105 connection b;
106 --error ER_LOCK_WAIT_TIMEOUT
107 reap;
108
109 disconnect b;
110 --source include/wait_until_disconnected.inc
111
112 connection a;
113 disconnect a;
114 --source include/wait_until_disconnected.inc
115
116 connection default;
117 drop table t1;
118 --replace_result $initial_timeout <initial_timeout>
119 eval set global innodb_lock_wait_timeout=$initial_timeout;
120
121 --echo #
122 --echo # MDEV-11379 - AliSQL: [Feature] Issue#8: SELECT FOR UPDATE WAIT
123 --echo #
124 CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=InnoDB;
125 INSERT INTO t1 (c1,c2) values (1,1),(2,2),(3,3),(4,4);
126
127 # Not supported in view/sp
128 --error ER_VIEW_SELECT_CLAUSE
129 CREATE VIEW v1 AS SELECT * FROM t1 WHERE c1=4 FOR UPDATE NOWAIT;
130 --error ER_VIEW_SELECT_CLAUSE
131 CREATE VIEW v1 AS SELECT * FROM t1 WHERE c1=4 FOR UPDATE WAIT 0;
132 --error ER_SP_BADSTATEMENT
133 CREATE PROCEDURE p1() SELECT * FROM t1 WHERE c1=4 FOR UPDATE NOWAIT;
134 --error ER_SP_BADSTATEMENT
135 CREATE PROCEDURE p1() SELECT * FROM t1 WHERE c1=4 FOR UPDATE WAIT 0;
136
137 connect(con1,localhost,root,,);
138 LOCK TABLE t1 WRITE;
139
140 connect(con2,localhost,root,,);
141 # The following statement should hang because con1 is locking the table
142 --error ER_LOCK_WAIT_TIMEOUT
143 SELECT * FROM t1 WHERE c1=4 FOR UPDATE NOWAIT;
144 --error ER_LOCK_WAIT_TIMEOUT
145 SELECT * FROM t1 WHERE c1=4 FOR UPDATE WAIT 0;
146 PREPARE stmt FROM 'SELECT * FROM t1 WHERE c1=4 FOR UPDATE NOWAIT';
147 --error ER_LOCK_WAIT_TIMEOUT
148 EXECUTE stmt;
149 DEALLOCATE PREPARE stmt;
150 PREPARE stmt FROM 'SELECT * FROM t1 WHERE c1=4 FOR UPDATE WAIT 0';
151 --error ER_LOCK_WAIT_TIMEOUT
152 EXECUTE stmt;
153 DEALLOCATE PREPARE stmt;
154
155 connection con1;
156 INSERT INTO t1 VALUES(5,5);
157 UNLOCK TABLES;
158 set AUTOCOMMIT=0;
159 --disable_result_log
160 SELECT * FROM t1 WHERE c1=4 FOR UPDATE;
161 --enable_result_log
162
163 connection con2;
164 set AUTOCOMMIT=0;
165 SET INNODB_LOCK_WAIT_TIMEOUT=1;
166 --error ER_LOCK_WAIT_TIMEOUT
167 SELECT * FROM t1 WHERE c1=4 FOR UPDATE;
168 --error ER_LOCK_WAIT_TIMEOUT
169 SELECT * FROM t1 WHERE c1=4 FOR UPDATE NOWAIT;
170 --error ER_LOCK_WAIT_TIMEOUT
171 SELECT * FROM t1 WHERE c1=4 FOR UPDATE WAIT 0;
172
173 connection con1;
174 UPDATE t1 SET c2=5 WHERE c1=4;
175 COMMIT;
176 set AUTOCOMMIT=0;
177 SELECT * FROM t1 WHERE c1=4 FOR UPDATE;
178
179 connection con2;
180 set AUTOCOMMIT=0;
181 SET INNODB_LOCK_WAIT_TIMEOUT=1;
182 --send
183 --disable_result_log
184 SELECT * FROM t1 WHERE c1=4 FOR UPDATE WAIT 10;
185
186 connection con1;
187 COMMIT;
188
189 connection con2;
190 --reap
191
192
193 disconnect con1;
194 disconnect con2;
195
196 # clear
197 connection default;
198 DROP TABLE t1;
199
200 --source include/wait_until_count_sessions.inc