Debian Codegraph @

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

raw
1 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) Engine=InnoDB;
2 INSERT INTO t1 (id) VALUES (1),(2),(3),(4),(5);
3 BEGIN;
4 SELECT * FROM t1 WHERE id = 1 FOR UPDATE;
5 id
6 1
7 BEGIN;
8 SELECT * FROM t1 WHERE id = 2 FOR UPDATE;
9 id
10 2
11 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait';
12 SELECT * FROM t1 WHERE id = 1 FOR UPDATE; ROLLBACK;
13 BEGIN;
14 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
15 SELECT * FROM t1 WHERE id = 3 FOR UPDATE;
16 id
17 3
18 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con3_will_wait';
19 SELECT * FROM t1 WHERE id = 2 FOR UPDATE; ROLLBACK;
20 BEGIN;
21 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait';
22 SELECT * FROM t1 WHERE id = 4 FOR UPDATE;
23 id
24 4
25 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con4_will_wait';
26 SELECT * FROM t1 WHERE id = 3 FOR UPDATE; ROLLBACK;
27 SET DEBUG_SYNC = 'now WAIT_FOR con4_will_wait';
28 SELECT * FROM t1 WHERE id = 4 FOR UPDATE;
29 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
30 id
31 1
32 id
33 2
34 id
35 3
36 SHOW ENGINE INNODB STATUS;
37 Type Name Status
38 InnoDB
39 ------------------------
40 %DATE% %THREAD_ID%
41 *** (1) TRANSACTION:
42 TRANSACTION %NUMBER%, ACTIVE %NUMBER% sec starting index read
43 mysql tables in use 1, locked 1
44 LOCK WAIT 3 lock struct(s), heap size %NUMBER%, 2 row lock(s)
45 MySQL thread id %NUMBER%, OS thread handle %NUMBER%, query id %NUMBER% %ADDRESS% %USER% statistics
46 SELECT * FROM t1 WHERE id = 1 FOR UPDATE
47
48 *** (1) HOLDS THE LOCK(S):
49 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap
50 Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
51 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
52 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
53 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
54
55
56 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
57 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap waiting
58 Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
59 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
60 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
61 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
62
63
64 *** (2) TRANSACTION:
65 TRANSACTION %NUMBER%, ACTIVE %NUMBER% sec starting index read
66 mysql tables in use 1, locked 1
67 LOCK WAIT 3 lock struct(s), heap size %NUMBER%, 2 row lock(s)
68 MySQL thread id %NUMBER%, OS thread handle %NUMBER%, query id %NUMBER% %ADDRESS% %USER% statistics
69 SELECT * FROM t1 WHERE id = 4 FOR UPDATE
70
71 *** (2) HOLDS THE LOCK(S):
72 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap
73 Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
74 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
75 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
76 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
77
78
79 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
80 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap waiting
81 Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
82 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
83 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
84 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
85
86
87 *** (3) TRANSACTION:
88 TRANSACTION %NUMBER%, ACTIVE %NUMBER% sec starting index read
89 mysql tables in use 1, locked 1
90 LOCK WAIT 3 lock struct(s), heap size %NUMBER%, 2 row lock(s)
91 MySQL thread id %NUMBER%, OS thread handle %NUMBER%, query id %NUMBER% %ADDRESS% %USER% statistics
92 SELECT * FROM t1 WHERE id = 3 FOR UPDATE
93
94 *** (3) HOLDS THE LOCK(S):
95 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap
96 Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
97 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
98 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
99 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
100
101
102 *** (3) WAITING FOR THIS LOCK TO BE GRANTED:
103 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap waiting
104 Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
105 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
106 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
107 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
108
109
110 *** (4) TRANSACTION:
111 TRANSACTION %NUMBER%, ACTIVE %NUMBER% sec starting index read
112 mysql tables in use 1, locked 1
113 LOCK WAIT 3 lock struct(s), heap size %NUMBER%, 2 row lock(s)
114 MySQL thread id %NUMBER%, OS thread handle %NUMBER%, query id %NUMBER% %ADDRESS% %USER% statistics
115 SELECT * FROM t1 WHERE id = 2 FOR UPDATE
116
117 *** (4) HOLDS THE LOCK(S):
118 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap
119 Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
120 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
121 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
122 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
123
124
125 *** (4) WAITING FOR THIS LOCK TO BE GRANTED:
126 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap waiting
127 Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
128 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
129 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
130 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
131
132 *** WE ROLL BACK TRANSACTION (2)
133 ------------
134
135 CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY) Engine=InnoDB;
136 INSERT INTO t2 (id) VALUES (1),(2),(3),(4),(5);
137 BEGIN;
138 SELECT * FROM t2 FOR UPDATE;
139 id
140 1
141 2
142 3
143 4
144 5
145 SELECT * FROM t1 WHERE id = 1 FOR UPDATE;
146 id
147 1
148 BEGIN;
149 SELECT * FROM t1 WHERE id = 2 FOR UPDATE;
150 id
151 2
152 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait';
153 SELECT * FROM t1 WHERE id = 1 FOR UPDATE; ROLLBACK;
154 BEGIN;
155 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
156 SELECT * FROM t1 WHERE id = 3 FOR UPDATE;
157 id
158 3
159 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con3_will_wait';
160 SELECT * FROM t1 WHERE id = 2 FOR UPDATE; ROLLBACK;
161 BEGIN;
162 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait';
163 SELECT * FROM t1 WHERE id = 4 FOR UPDATE;
164 id
165 4
166 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con4_will_wait';
167 SELECT * FROM t1 WHERE id = 3 FOR UPDATE; ROLLBACK;
168 SET DEBUG_SYNC = 'now WAIT_FOR con4_will_wait';
169 SELECT * FROM t1 WHERE id = 4 FOR UPDATE;
170 id
171 4
172 ROLLBACK;
173 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
174 id
175 2
176 id
177 3
178 SHOW ENGINE INNODB STATUS;
179 Type Name Status
180 InnoDB
181 ------------------------
182 %DATE% %THREAD_ID%
183 *** (1) TRANSACTION:
184 TRANSACTION %NUMBER%, ACTIVE %NUMBER% sec starting index read
185 mysql tables in use 1, locked 1
186 LOCK WAIT 3 lock struct(s), heap size %NUMBER%, 2 row lock(s)
187 MySQL thread id %NUMBER%, OS thread handle %NUMBER%, query id %NUMBER% %ADDRESS% %USER% statistics
188 SELECT * FROM t1 WHERE id = 1 FOR UPDATE
189
190 *** (1) HOLDS THE LOCK(S):
191 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap
192 Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
193 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
194 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
195 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
196
197
198 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
199 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap waiting
200 Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
201 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
202 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
203 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
204
205
206 *** (2) TRANSACTION:
207 TRANSACTION %NUMBER%, ACTIVE %NUMBER% sec starting index read
208 mysql tables in use 1, locked 1
209 LOCK WAIT 5 lock struct(s), heap size %NUMBER%, 8 row lock(s)
210 MySQL thread id %NUMBER%, OS thread handle %NUMBER%, query id %NUMBER% %ADDRESS% %USER% statistics
211 SELECT * FROM t1 WHERE id = 4 FOR UPDATE
212
213 *** (2) HOLDS THE LOCK(S):
214 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap
215 Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
216 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
217 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
218 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
219
220
221 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
222 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap waiting
223 Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
224 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
225 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
226 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
227
228
229 *** (3) TRANSACTION:
230 TRANSACTION %NUMBER%, ACTIVE %NUMBER% sec starting index read
231 mysql tables in use 1, locked 1
232 LOCK WAIT 3 lock struct(s), heap size %NUMBER%, 2 row lock(s)
233 MySQL thread id %NUMBER%, OS thread handle %NUMBER%, query id %NUMBER% %ADDRESS% %USER% statistics
234 SELECT * FROM t1 WHERE id = 3 FOR UPDATE
235
236 *** (3) HOLDS THE LOCK(S):
237 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap
238 Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
239 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
240 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
241 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
242
243
244 *** (3) WAITING FOR THIS LOCK TO BE GRANTED:
245 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap waiting
246 Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
247 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
248 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
249 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
250
251
252 *** (4) TRANSACTION:
253 TRANSACTION %NUMBER%, ACTIVE %NUMBER% sec starting index read
254 mysql tables in use 1, locked 1
255 LOCK WAIT 3 lock struct(s), heap size %NUMBER%, 2 row lock(s)
256 MySQL thread id %NUMBER%, OS thread handle %NUMBER%, query id %NUMBER% %ADDRESS% %USER% statistics
257 SELECT * FROM t1 WHERE id = 2 FOR UPDATE
258
259 *** (4) HOLDS THE LOCK(S):
260 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap
261 Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
262 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
263 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
264 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
265
266
267 *** (4) WAITING FOR THIS LOCK TO BE GRANTED:
268 RECORD LOCKS space id %NUMBER% page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id %NUMBER% lock_mode X locks rec but not gap waiting
269 Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
270 0: len 4; hex %HEXADECIMAL%; asc %ASC%;;
271 1: len 6; hex %HEXADECIMAL%; asc %ASC%;;
272 2: len 7; hex %HEXADECIMAL%; asc %ASC%;;
273
274 *** WE ROLL BACK TRANSACTION (1)
275 ------------
276
277 DROP TABLE t2;
278 DROP TABLE t1;