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