Debian Codegraph @

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

raw
1 CREATE TABLE connections (
2 trx_mysql_thread_id BIGINT PRIMARY KEY,
3 name VARCHAR(200)
4 ) ENGINE=InnoDB;
5 CREATE PROCEDURE show_locks ()
6 BEGIN
7 # this is needed because INFORMATION_SCHEMA.INNODB_TRX is refreshed with debounce 100ms
8 SELECT SLEEP(1);
9 SELECT
10 connections.name, object_name, index_name, lock_type, lock_mode, lock_status, lock_data
11 FROM connections
12 JOIN INFORMATION_SCHEMA.INNODB_TRX USING (trx_mysql_thread_id)
13 JOIN performance_schema.data_locks ON (trx_id=engine_transaction_id)
14 ORDER BY 1,2,3,4,5,6,7;
15 END //
16 CREATE PROCEDURE register_connection (name VARCHAR(200))
17 BEGIN
18 INSERT INTO connections VALUES (CONNECTION_ID(), name);
19 END //
20 CALL register_connection("con1");
21 CALL register_connection("con2");
22 CALL register_connection("con3");
23 CREATE TABLE t1 (id INT PRIMARY KEY);
24 INSERT INTO t1 (id) VALUES (1);
25 BEGIN;
26 SELECT * FROM t1 FOR SHARE;
27 id
28 1
29 BEGIN;
30 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait';
31 SELECT * FROM t1 FOR UPDATE;
32 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
33 SELECT * FROM t1 FOR UPDATE;
34 id
35 1
36 COMMIT;
37 id
38 1
39 COMMIT;
40 BEGIN;
41 SELECT * FROM t1 WHERE id=1 FOR UPDATE;
42 id
43 1
44 BEGIN;
45 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait';
46 SELECT * FROM t1 FOR SHARE;
47 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
48 CALL show_locks();
49 SLEEP(1)
50 0
51 name object_name index_name lock_type lock_mode lock_status lock_data
52 con1 t1 NULL TABLE IX GRANTED NULL
53 con1 t1 PRIMARY RECORD X,REC_NOT_GAP GRANTED 1
54 con2 t1 NULL TABLE IS GRANTED NULL
55 con2 t1 PRIMARY RECORD S WAITING 1
56 INSERT INTO t1 VALUES (0);
57 CALL show_locks();
58 SLEEP(1)
59 0
60 name object_name index_name lock_type lock_mode lock_status lock_data
61 con1 t1 NULL TABLE IX GRANTED NULL
62 con1 t1 PRIMARY RECORD X,GAP,INSERT_INTENTION GRANTED 1
63 con1 t1 PRIMARY RECORD X,REC_NOT_GAP GRANTED 1
64 ROLLBACK;
65 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
66 COMMIT;
67 BEGIN;
68 SELECT * FROM t1 FOR SHARE;
69 id
70 1
71 BEGIN;
72 SELECT * FROM t1 WHERE id=1 FOR SHARE;
73 id
74 1
75 CALL show_locks();
76 SLEEP(1)
77 0
78 name object_name index_name lock_type lock_mode lock_status lock_data
79 con1 t1 NULL TABLE IS GRANTED NULL
80 con1 t1 PRIMARY RECORD S GRANTED 1
81 con1 t1 PRIMARY RECORD S GRANTED supremum pseudo-record
82 con2 t1 NULL TABLE IS GRANTED NULL
83 con2 t1 PRIMARY RECORD S,REC_NOT_GAP GRANTED 1
84 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con3_will_wait';
85 SELECT * FROM t1 FOR UPDATE;
86 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait';
87 CALL show_locks();
88 SLEEP(1)
89 0
90 name object_name index_name lock_type lock_mode lock_status lock_data
91 con1 t1 NULL TABLE IS GRANTED NULL
92 con1 t1 PRIMARY RECORD S GRANTED 1
93 con1 t1 PRIMARY RECORD S GRANTED supremum pseudo-record
94 con2 t1 NULL TABLE IS GRANTED NULL
95 con2 t1 PRIMARY RECORD S,REC_NOT_GAP GRANTED 1
96 con3 t1 NULL TABLE IX GRANTED NULL
97 con3 t1 PRIMARY RECORD X WAITING 1
98 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con1_will_wait';
99 INSERT INTO t1 VALUES (0);
100 SET DEBUG_SYNC = 'now WAIT_FOR con1_will_wait';
101 CALL show_locks();
102 SLEEP(1)
103 0
104 name object_name index_name lock_type lock_mode lock_status lock_data
105 con1 t1 NULL TABLE IS GRANTED NULL
106 con1 t1 NULL TABLE IX GRANTED NULL
107 con1 t1 PRIMARY RECORD S GRANTED 1
108 con1 t1 PRIMARY RECORD S GRANTED supremum pseudo-record
109 con1 t1 PRIMARY RECORD X,GAP,INSERT_INTENTION WAITING 1
110 con2 t1 NULL TABLE IS GRANTED NULL
111 con2 t1 PRIMARY RECORD S,REC_NOT_GAP GRANTED 1
112 con3 t1 NULL TABLE IX GRANTED NULL
113 con3 t1 PRIMARY RECORD X WAITING 1
114 COMMIT;
115 CALL show_locks();
116 SLEEP(1)
117 0
118 name object_name index_name lock_type lock_mode lock_status lock_data
119 con1 t1 NULL TABLE IS GRANTED NULL
120 con1 t1 NULL TABLE IX GRANTED NULL
121 con1 t1 PRIMARY RECORD S GRANTED 1
122 con1 t1 PRIMARY RECORD S GRANTED supremum pseudo-record
123 con1 t1 PRIMARY RECORD S,GAP GRANTED 0
124 con1 t1 PRIMARY RECORD X,GAP,INSERT_INTENTION GRANTED 1
125 ROLLBACK;
126 CALL show_locks();
127 SLEEP(1)
128 0
129 name object_name index_name lock_type lock_mode lock_status lock_data
130 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
131 BEGIN;
132 SELECT * FROM t1 FOR SHARE;
133 id
134 1
135 BEGIN;
136 SELECT * FROM t1 WHERE id=1 FOR SHARE;
137 id
138 1
139 CALL show_locks();
140 SLEEP(1)
141 0
142 name object_name index_name lock_type lock_mode lock_status lock_data
143 con1 t1 NULL TABLE IS GRANTED NULL
144 con1 t1 PRIMARY RECORD S GRANTED 1
145 con1 t1 PRIMARY RECORD S GRANTED supremum pseudo-record
146 con2 t1 NULL TABLE IS GRANTED NULL
147 con2 t1 PRIMARY RECORD S,REC_NOT_GAP GRANTED 1
148 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con3_will_wait';
149 SELECT * FROM t1 FOR UPDATE;
150 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait';
151 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con1_will_wait';
152 SELECT * FROM t1 WHERE id=1 FOR UPDATE;
153 SET DEBUG_SYNC = 'now WAIT_FOR con1_will_wait';
154 CALL show_locks();
155 SLEEP(1)
156 0
157 name object_name index_name lock_type lock_mode lock_status lock_data
158 con1 t1 NULL TABLE IS GRANTED NULL
159 con1 t1 NULL TABLE IX GRANTED NULL
160 con1 t1 PRIMARY RECORD S GRANTED 1
161 con1 t1 PRIMARY RECORD S GRANTED supremum pseudo-record
162 con1 t1 PRIMARY RECORD X,REC_NOT_GAP WAITING 1
163 con2 t1 NULL TABLE IS GRANTED NULL
164 con2 t1 PRIMARY RECORD S,REC_NOT_GAP GRANTED 1
165 con3 t1 NULL TABLE IX GRANTED NULL
166 con3 t1 PRIMARY RECORD X WAITING 1
167 COMMIT;
168 CALL show_locks();
169 SLEEP(1)
170 0
171 name object_name index_name lock_type lock_mode lock_status lock_data
172 con1 t1 NULL TABLE IS GRANTED NULL
173 con1 t1 NULL TABLE IX GRANTED NULL
174 con1 t1 PRIMARY RECORD S GRANTED 1
175 con1 t1 PRIMARY RECORD S GRANTED supremum pseudo-record
176 con1 t1 PRIMARY RECORD X,REC_NOT_GAP GRANTED 1
177 con3 t1 NULL TABLE IX GRANTED NULL
178 con3 t1 PRIMARY RECORD X WAITING 1
179 id
180 1
181 COMMIT;
182 CALL show_locks();
183 SLEEP(1)
184 0
185 name object_name index_name lock_type lock_mode lock_status lock_data
186 id
187 1
188 COMMIT;
189 BEGIN;
190 SELECT * FROM t1 FOR SHARE;
191 id
192 1
193 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait';
194 SELECT * FROM t1 FOR UPDATE;
195 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
196 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con3_will_wait';
197 SELECT * FROM t1 FOR UPDATE;
198 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait';
199 CALL show_locks();
200 SLEEP(1)
201 0
202 name object_name index_name lock_type lock_mode lock_status lock_data
203 con1 t1 NULL TABLE IS GRANTED NULL
204 con1 t1 PRIMARY RECORD S GRANTED 1
205 con1 t1 PRIMARY RECORD S GRANTED supremum pseudo-record
206 con2 t1 NULL TABLE IX GRANTED NULL
207 con2 t1 PRIMARY RECORD X WAITING 1
208 con3 t1 NULL TABLE IX GRANTED NULL
209 con3 t1 PRIMARY RECORD X WAITING 1
210 SELECT * FROM t1 WHERE id=1 FOR UPDATE;
211 id
212 1
213 CALL show_locks();
214 SLEEP(1)
215 0
216 name object_name index_name lock_type lock_mode lock_status lock_data
217 con1 t1 NULL TABLE IS GRANTED NULL
218 con1 t1 NULL TABLE IX GRANTED NULL
219 con1 t1 PRIMARY RECORD S GRANTED 1
220 con1 t1 PRIMARY RECORD S GRANTED supremum pseudo-record
221 con1 t1 PRIMARY RECORD X,REC_NOT_GAP GRANTED 1
222 con2 t1 NULL TABLE IX GRANTED NULL
223 con2 t1 PRIMARY RECORD X WAITING 1
224 con3 t1 NULL TABLE IX GRANTED NULL
225 con3 t1 PRIMARY RECORD X WAITING 1
226 COMMIT;
227 id
228 1
229 COMMIT;
230 id
231 1
232 COMMIT;
233 DROP TABLE t1;
234 DROP PROCEDURE show_locks;
235 DROP PROCEDURE register_connection;
236 DROP TABLE connections;