Debian Codegraph @

mysql-test/suite/innodb/r/skip_locked_nowait.result

raw
1 SET SESSION innodb_lock_wait_timeout=1;
2 SET SESSION innodb_lock_wait_timeout=1;
3 # Case 1: Test primary index
4 CREATE TABLE t1(
5 seat_id INT,
6 state INT,
7 PRIMARY KEY(seat_id)
8 ) ENGINE=InnoDB;
9 INSERT INTO t1 VALUES(1,0), (2,0), (3,0), (4,0);
10 BEGIN;
11 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE;
12 seat_id state
13 1 0
14 2 0
15 BEGIN;
16 SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE;
17 seat_id state
18 1 0
19 2 0
20 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE;
21 seat_id state
22 1 0
23 2 0
24 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE NOWAIT;
25 seat_id state
26 1 0
27 2 0
28 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE SKIP LOCKED;
29 seat_id state
30 1 0
31 2 0
32 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;
33 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
34 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE NOWAIT;
35 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
36 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
37 seat_id state
38 3 0
39 4 0
40 SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE NOWAIT;
41 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
42 SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
43 seat_id state
44 3 0
45 4 0
46 COMMIT;
47 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;
48 seat_id state
49 1 0
50 2 0
51 SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE;
52 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
53 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE;
54 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
55 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE NOWAIT;
56 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
57 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE SKIP LOCKED;
58 seat_id state
59 3 0
60 4 0
61 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;
62 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
63 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE NOWAIT;
64 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
65 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
66 seat_id state
67 3 0
68 4 0
69 SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE NOWAIT;
70 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
71 SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
72 seat_id state
73 3 0
74 4 0
75 COMMIT;
76 COMMIT;
77 DROP TABLE t1;
78 # Case 2: Test primary index & secondary index
79 CREATE TABLE t1(
80 seat_id INT,
81 row_id INT,
82 state INT,
83 PRIMARY KEY(seat_id),
84 KEY(row_id)
85 ) ENGINE=InnoDB;
86 INSERT INTO t1 VALUES(1,1,0), (2,1,0), (3,2,0), (4,2,0);
87 # Test secondary key
88 BEGIN;
89 SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE NOWAIT;
90 seat_id row_id state
91 1 1 0
92 BEGIN;
93 SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE NOWAIT;
94 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
95 SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE SKIP LOCKED;
96 seat_id row_id state
97 2 1 0
98 SELECT * FROM t1 WHERE state = 0 AND row_id > 0 LIMIT 1 FOR UPDATE NOWAIT;
99 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
100 SELECT * FROM t1 WHERE state = 0 AND row_id > 0 LIMIT 1 FOR UPDATE SKIP LOCKED;
101 seat_id row_id state
102 2 1 0
103 SELECT * FROM t1 WHERE state = 0 FOR UPDATE NOWAIT;
104 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
105 SELECT * FROM t1 WHERE state = 0 FOR UPDATE SKIP LOCKED;
106 seat_id row_id state
107 2 1 0
108 3 2 0
109 4 2 0
110 COMMIT;
111 COMMIT;
112 BEGIN;
113 SELECT * FROM t1 WHERE seat_id = 1 FOR UPDATE NOWAIT;
114 seat_id row_id state
115 1 1 0
116 BEGIN;
117 SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE NOWAIT;
118 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
119 SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE SKIP LOCKED;
120 seat_id row_id state
121 2 1 0
122 SELECT * FROM t1 WHERE state = 0 FOR UPDATE NOWAIT;
123 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
124 SELECT * FROM t1 WHERE state = 0 FOR UPDATE SKIP LOCKED;
125 seat_id row_id state
126 2 1 0
127 3 2 0
128 4 2 0
129 COMMIT;
130 COMMIT;
131 DROP TABLE t1;
132 # Case 3: Test primary index & spatial index
133 CREATE TABLE t1(
134 seat_id INT,
135 pos POINT NOT NULL SRID 0,
136 state INT,
137 PRIMARY KEY(seat_id),
138 SPATIAL KEY(pos)
139 ) ENGINE=InnoDB;
140 INSERT INTO t1 VALUES
141 (1,ST_PointFromText('POINT(1 0)'),0),
142 (2,ST_PointFromText('POINT(1 1)'),0),
143 (3,ST_PointFromText('POINT(2 0)'),0),
144 (4,ST_PointFromText('POINT(2 1)'),0),
145 (5,ST_PointFromText('POINT(3 0)'),0),
146 (6,ST_PointFromText('POINT(3 1)'),0);
147 BEGIN;
148 SET @g = ST_GeomFromText('POLYGON((0 0,0 2,2 2,0 2,0 0))');
149 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
150 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE NOWAIT;
151 seat_id state ST_AsText(pos)
152 2 0 POINT(1 1)
153 BEGIN;
154 SET @g = ST_GeomFromText('POLYGON((0 0,0 4,4 4,0 4,0 0))');
155 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
156 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE NOWAIT;
157 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
158 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
159 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE SKIP LOCKED;
160 seat_id state ST_AsText(pos)
161 6 0 POINT(3 1)
162 SELECT seat_id, state, ST_AsText(pos) FROM t1
163 WHERE state = 0 FOR UPDATE NOWAIT;
164 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
165 SELECT seat_id, state, ST_AsText(pos) FROM t1
166 WHERE state = 0 FOR UPDATE SKIP LOCKED;
167 seat_id state ST_AsText(pos)
168 5 0 POINT(3 0)
169 6 0 POINT(3 1)
170 COMMIT;
171 COMMIT;
172 SET @g = ST_GeomFromText('POLYGON((0 0,0 3,3 3,0 3,0 0))');
173 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
174 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE;
175 seat_id state ST_AsText(pos)
176 4 0 POINT(2 1)
177 2 0 POINT(1 1)
178 BEGIN;
179 SELECT seat_id, state, ST_AsText(pos) FROM t1
180 WHERE seat_id = 4 FOR UPDATE NOWAIT;
181 seat_id state ST_AsText(pos)
182 4 0 POINT(2 1)
183 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
184 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE NOWAIT;
185 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
186 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
187 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE SKIP LOCKED;
188 seat_id state ST_AsText(pos)
189 2 0 POINT(1 1)
190 SELECT seat_id, state, ST_AsText(pos) FROM t1
191 WHERE state = 0 FOR UPDATE NOWAIT;
192 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
193 SELECT seat_id, state, ST_AsText(pos) FROM t1
194 WHERE state = 0 FOR UPDATE SKIP LOCKED;
195 seat_id state ST_AsText(pos)
196 1 0 POINT(1 0)
197 2 0 POINT(1 1)
198 3 0 POINT(2 0)
199 5 0 POINT(3 0)
200 6 0 POINT(3 1)
201 COMMIT;
202 DROP TABLE t1;
203 # Case 4: Test high priority transaction
204 CREATE TABLE t1(
205 seat_id INT,
206 state INT,
207 PRIMARY KEY(seat_id)
208 ) ENGINE=InnoDB;
209 INSERT INTO t1 VALUES(1,0), (2,0), (3,0), (4,0);
210 BEGIN;
211 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;
212 seat_id state
213 1 0
214 2 0
215 include/start_transaction_high_prio.inc
216 START TRANSACTION /* HIGH PRIORITY */;
217 SELECT * FROM t1 WHERE seat_id = 1 AND state = 0 FOR UPDATE NOWAIT;
218 seat_id state
219 1 0
220 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
221 seat_id state
222 1 0
223 2 0
224 COMMIT;
225 COMMIT;
226 ERROR HY000: Got error 149 - 'Lock deadlock; Retry transaction' during COMMIT
227 ROLLBACK;
228 DROP TABLE t1;