Debian Codegraph @

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

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