Debian Codegraph @

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

raw
1 connect con1,localhost,root,,;
2 SET SESSION innodb_lock_wait_timeout=1;
3 connection default;
4 SET SESSION innodb_lock_wait_timeout=1;
5 # Case 1: Test primary index
6 CREATE TABLE t1(
7 seat_id INT,
8 state INT,
9 PRIMARY KEY(seat_id)
10 ) ENGINE=InnoDB;
11 INSERT INTO t1 VALUES(1,0), (2,0), (3,0), (4,0);
12 BEGIN;
13 SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE;
14 seat_id state
15 1 0
16 2 0
17 connection con1;
18 BEGIN;
19 SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE;
20 seat_id state
21 1 0
22 2 0
23 SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE SKIP LOCKED;
24 seat_id state
25 1 0
26 2 0
27 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;
28 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
29 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE NOWAIT;
30 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
31 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
32 seat_id state
33 3 0
34 4 0
35 SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE NOWAIT;
36 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
37 SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
38 seat_id state
39 3 0
40 4 0
41 COMMIT;
42 connection default;
43 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;
44 seat_id state
45 1 0
46 2 0
47 connection con1;
48 SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE;
49 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
50 SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE NOWAIT;
51 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
52 SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE SKIP LOCKED;
53 seat_id state
54 3 0
55 4 0
56 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;
57 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
58 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE NOWAIT;
59 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
60 SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
61 seat_id state
62 3 0
63 4 0
64 SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE NOWAIT;
65 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
66 SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
67 seat_id state
68 3 0
69 4 0
70 COMMIT;
71 connection default;
72 COMMIT;
73 DROP TABLE t1;
74 # 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 INSERT INTO t1 VALUES(1,1,0), (2,1,0), (3,2,0), (4,2,0);
83 # Test secondary key
84 BEGIN;
85 SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE NOWAIT;
86 seat_id row_id state
87 1 1 0
88 connection con1;
89 BEGIN;
90 SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE NOWAIT;
91 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
92 SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE SKIP LOCKED;
93 seat_id row_id state
94 2 1 0
95 SELECT * FROM t1 WHERE state = 0 AND row_id > 0 LIMIT 1 FOR UPDATE NOWAIT;
96 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
97 SELECT * FROM t1 WHERE state = 0 AND row_id > 0 LIMIT 1 FOR UPDATE SKIP LOCKED;
98 seat_id row_id state
99 2 1 0
100 SELECT * FROM t1 WHERE state = 0 FOR UPDATE NOWAIT;
101 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
102 SELECT * FROM t1 WHERE state = 0 FOR UPDATE SKIP LOCKED;
103 seat_id row_id state
104 2 1 0
105 3 2 0
106 4 2 0
107 COMMIT;
108 connection default;
109 COMMIT;
110 BEGIN;
111 SELECT * FROM t1 WHERE seat_id = 1 FOR UPDATE NOWAIT;
112 seat_id row_id state
113 1 1 0
114 connection con1;
115 BEGIN;
116 SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE NOWAIT;
117 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
118 SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE SKIP LOCKED;
119 seat_id row_id state
120 2 1 0
121 SELECT * FROM t1 WHERE state = 0 FOR UPDATE NOWAIT;
122 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
123 SELECT * FROM t1 WHERE state = 0 FOR UPDATE SKIP LOCKED;
124 seat_id row_id state
125 2 1 0
126 3 2 0
127 4 2 0
128 COMMIT;
129 connection default;
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,
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 connection con1;
154 BEGIN;
155 SET @g = ST_GeomFromText('POLYGON((0 0,0 4,4 4,0 4,0 0))');
156 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
157 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE NOWAIT;
158 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
159 SELECT seat_id, state, ST_AsText(pos) FROM t1
160 WHERE state = 0 FOR UPDATE NOWAIT;
161 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
162 SELECT seat_id, state, ST_AsText(pos) FROM t1
163 WHERE state = 0 FOR UPDATE SKIP LOCKED;
164 seat_id state ST_AsText(pos)
165 5 0 POINT(3 0)
166 6 0 POINT(3 1)
167 COMMIT;
168 connection default;
169 COMMIT;
170 connection con1;
171 SET @g = ST_GeomFromText('POLYGON((0 0,0 3,3 3,0 3,0 0))');
172 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
173 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE;
174 seat_id state ST_AsText(pos)
175 4 0 POINT(2 1)
176 2 0 POINT(1 1)
177 connection default;
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 connection con1;
184 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
185 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE NOWAIT;
186 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
187 SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
188 WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE SKIP LOCKED;
189 seat_id state ST_AsText(pos)
190 2 0 POINT(1 1)
191 SELECT seat_id, state, ST_AsText(pos) FROM t1
192 WHERE state = 0 FOR UPDATE NOWAIT;
193 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
194 SELECT seat_id, state, ST_AsText(pos) FROM t1
195 WHERE state = 0 FOR UPDATE SKIP LOCKED;
196 seat_id state ST_AsText(pos)
197 1 0 POINT(1 0)
198 2 0 POINT(1 1)
199 3 0 POINT(2 0)
200 5 0 POINT(3 0)
201 6 0 POINT(3 1)
202 connection default;
203 COMMIT;
204 DROP TABLE t1;
205 disconnect con1;