Debian Codegraph @

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

raw
1 #
2 # wl#8919 Implement NOWAIT and SKIP LOCKED
3 #
4
5 --source include/count_sessions.inc
6 --source include/not_hypergraph.inc # Depends on indexes being used, which the hypergraph optimizer does not support.
7
8 connect (con1,localhost,root,,);
9 SET SESSION innodb_lock_wait_timeout=1;
10
11 connection default;
12 SET SESSION innodb_lock_wait_timeout=1;
13
14 --echo # Case 1: Test primary index
15 CREATE TABLE t1(
16 seat_id INT,
17 state INT,
18 PRIMARY KEY(seat_id)
19 ) ENGINE=InnoDB;
20
21 INSERT INTO t1 VALUES(1,0), (2,0), (3,0), (4,0);
22
23 BEGIN;
24 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE;
25
26 connection con1;
27 BEGIN;
28
29 SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE;
30 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE;
31
32 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE NOWAIT;
33 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE SKIP LOCKED;
34
35 --error ER_LOCK_WAIT_TIMEOUT
36 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;
37
38 --error ER_LOCK_NOWAIT
39 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE NOWAIT;
40 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
41
42 --error ER_LOCK_NOWAIT
43 SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE NOWAIT;
44 SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
45
46 COMMIT;
47
48 connection default;
49 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;
50
51 connection con1;
52 --error ER_LOCK_WAIT_TIMEOUT
53 SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE;
54
55 --error ER_LOCK_WAIT_TIMEOUT
56 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE;
57
58 --error ER_LOCK_NOWAIT
59 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE NOWAIT;
60 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE SKIP LOCKED;
61
62 --error ER_LOCK_WAIT_TIMEOUT
63 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;
64
65 --error ER_LOCK_NOWAIT
66 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE NOWAIT;
67 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
68
69 --error ER_LOCK_NOWAIT
70 SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE NOWAIT;
71 SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
72
73 COMMIT;
74
75 connection default;
76 COMMIT;
77
78 DROP TABLE t1;
79
80 --echo # Case 2: Test primary index & secondary index
81 CREATE TABLE t1(
82 seat_id INT,
83 row_id INT,
84 state INT,
85 PRIMARY KEY(seat_id),
86 KEY(row_id)
87 ) ENGINE=InnoDB;
88
89 INSERT INTO t1 VALUES(1,1,0), (2,1,0), (3,2,0), (4,2,0);
90
91 --echo # Test secondary key
92 # Case 2a: secondary blocks secondary/primary
93 BEGIN;
94 SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE NOWAIT;
95
96 connection con1;
97 BEGIN;
98 --error ER_LOCK_NOWAIT
99 SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE NOWAIT;
100 SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE SKIP LOCKED;
101
102 --error ER_LOCK_NOWAIT
103 SELECT * FROM t1 WHERE state = 0 AND row_id > 0 LIMIT 1 FOR UPDATE NOWAIT;
104 SELECT * FROM t1 WHERE state = 0 AND row_id > 0 LIMIT 1 FOR UPDATE SKIP LOCKED;
105
106 --error ER_LOCK_NOWAIT
107 SELECT * FROM t1 WHERE state = 0 FOR UPDATE NOWAIT;
108 SELECT * FROM t1 WHERE state = 0 FOR UPDATE SKIP LOCKED;
109
110 COMMIT;
111
112 connection default;
113 COMMIT;
114
115 # Case 2b: primary blocks secondary/primary
116 BEGIN;
117 SELECT * FROM t1 WHERE seat_id = 1 FOR UPDATE NOWAIT;
118
119 connection con1;
120 BEGIN;
121 --error ER_LOCK_NOWAIT
122 SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE NOWAIT;
123 SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE SKIP LOCKED;
124
125 --error ER_LOCK_NOWAIT
126 SELECT * FROM t1 WHERE state = 0 FOR UPDATE NOWAIT;
127 SELECT * FROM t1 WHERE state = 0 FOR UPDATE SKIP LOCKED;
128
129 COMMIT;
130
131 connection default;
132 COMMIT;
133
134 DROP TABLE t1;
135
136 --echo # Case 3: Test primary index & spatial index
137 CREATE TABLE t1(
138 seat_id INT,
139 pos POINT NOT NULL SRID 0,
140 state INT,
141 PRIMARY KEY(seat_id),
142 SPATIAL KEY(pos)
143 ) ENGINE=InnoDB;
144
145 INSERT INTO t1 VALUES
146 (1,ST_PointFromText('POINT(1 0)'),0),
147 (2,ST_PointFromText('POINT(1 1)'),0),
148 (3,ST_PointFromText('POINT(2 0)'),0),
149 (4,ST_PointFromText('POINT(2 1)'),0),
150 (5,ST_PointFromText('POINT(3 0)'),0),
151 (6,ST_PointFromText('POINT(3 1)'),0);
152
153 # Case 3a: secondary blocks secondary/primary
154 BEGIN;
155 SET @g = ST_GeomFromText('POLYGON((0 0,0 2,2 2,0 2,0 0))');
156 # the first 4 records in the rtree index page are locked
157 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
158 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE NOWAIT;
159
160 connection con1;
161 BEGIN;
162 SET @g = ST_GeomFromText('POLYGON((0 0,0 4,4 4,0 4,0 0))');
163 --error ER_LOCK_NOWAIT
164 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
165 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE NOWAIT;
166
167 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
168 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE SKIP LOCKED;
169
170 --error ER_LOCK_NOWAIT
171 SELECT seat_id, state, ST_AsText(pos) FROM t1
172 WHERE state = 0 FOR UPDATE NOWAIT;
173
174 SELECT seat_id, state, ST_AsText(pos) FROM t1
175 WHERE state = 0 FOR UPDATE SKIP LOCKED;
176
177 COMMIT;
178
179 connection default;
180 COMMIT;
181
182 # Case 3b: primary blockes secondary/primary
183 connection con1;
184 SET @g = ST_GeomFromText('POLYGON((0 0,0 3,3 3,0 3,0 0))');
185 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
186 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE;
187
188 connection default;
189 BEGIN;
190 SELECT seat_id, state, ST_AsText(pos) FROM t1
191 WHERE seat_id = 4 FOR UPDATE NOWAIT;
192
193 connection con1;
194 --error ER_LOCK_NOWAIT
195 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
196 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE NOWAIT;
197
198 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
199 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE SKIP LOCKED;
200
201 --error ER_LOCK_NOWAIT
202 SELECT seat_id, state, ST_AsText(pos) FROM t1
203 WHERE state = 0 FOR UPDATE NOWAIT;
204
205 SELECT seat_id, state, ST_AsText(pos) FROM t1
206 WHERE state = 0 FOR UPDATE SKIP LOCKED;
207
208 connection default;
209 COMMIT;
210
211 DROP TABLE t1;
212
213 --echo # Case 4: Test high priority transaction
214 CREATE TABLE t1(
215 seat_id INT,
216 state INT,
217 PRIMARY KEY(seat_id)
218 ) ENGINE=InnoDB;
219
220 INSERT INTO t1 VALUES(1,0), (2,0), (3,0), (4,0);
221
222 BEGIN;
223 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;
224
225 connection con1;
226
227 --source include/start_transaction_high_prio.inc
228
229 SELECT * FROM t1 WHERE seat_id = 1 AND state = 0 FOR UPDATE NOWAIT;
230
231 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
232
233 COMMIT;
234
235 connection default;
236 --error ER_ERROR_DURING_COMMIT
237 COMMIT;
238 ROLLBACK;
239
240 DROP TABLE t1;
241
242 disconnect con1;
243
244 --source include/wait_until_count_sessions.inc