Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 --source include/have_debug.inc
3 --source include/have_debug_sync.inc
4 --source include/count_sessions.inc
5
6 --disable_query_log
7 call mtr.add_suppression("InnoDB: Transaction was aborted due to ");
8 --enable_query_log
9
10 connect stop_purge,localhost,root;
11 START TRANSACTION WITH CONSISTENT SNAPSHOT;
12
13 --connect (con1,localhost,root,,)
14 --connect (con2,localhost,root,,)
15 --connect (con3,localhost,root,,)
16
17 --connection default
18 CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB STATS_PERSISTENT=0;
19 INSERT INTO t1 (id) VALUES (1);
20
21 --echo # Simplest scenario:
22 --echo # <con1, S, granted>,
23 --echo # <con1, S, granted>, <con2, X, waiting for con1>,
24 --echo # Before MDEV-34877:
25 --echo # <con1, S, granted>, <con2, X, waiting for con1>, <con1, X, waiting for con1>
26 --echo # After MDEV-34877:
27 --echo # <con1, S, granted>, <con1, X, granted>, <con2, X, waiting for con1>
28 --echo # Expected: instead of deadlocking, the con1's request should ingore con2's
29
30 --connection con1
31 BEGIN;
32 SELECT * FROM t1 LOCK IN SHARE MODE;
33
34 --connection con2
35 BEGIN;
36 SET DEBUG_SYNC = 'lock_wait_before_suspend SIGNAL con2_will_wait';
37 --send SELECT * FROM t1 FOR UPDATE
38
39 --connection con1
40 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
41 SELECT * FROM t1 FOR UPDATE;
42 COMMIT;
43
44 --connection con2
45 --reap
46 COMMIT;
47
48 --echo # The scenario when we bypass X<-S pair:
49 --echo # <con1, S, granted>,
50 --echo # <con1, S, granted>, <con2, X, waiting for con1>,
51 --echo # <con1, S, granted>, <con2, X, waiting for con1>, <con3, S, waiting for con2>
52 --echo # <con1, S, granted>, <con1, X, granted>, <con2, X, waiting for con1>, <con3, S, waiting for con2>
53
54 --connection con1
55 BEGIN;
56 SELECT * FROM t1 LOCK IN SHARE MODE;
57
58 --connection con2
59 BEGIN;
60 SET DEBUG_SYNC = 'lock_wait_before_suspend SIGNAL con2_will_wait';
61 --send SELECT * FROM t1 FOR UPDATE
62
63 --connection con3
64 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
65 BEGIN;
66 SET DEBUG_SYNC = 'lock_wait_before_suspend SIGNAL con3_will_wait';
67 --send SELECT * FROM t1 LOCK IN SHARE MODE;
68
69 --connection con1
70 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait';
71 SELECT * FROM t1 FOR UPDATE;
72 COMMIT;
73
74 --connection con2
75 --reap
76 COMMIT;
77
78 --connection con3
79 --reap
80 COMMIT;
81
82 #
83 --echo # A variant of the above scenario:
84 --echo # <con1, X REC_NOT_GAP, granted>,
85 --echo # <con1, X REC_NOT_GAP, granted>, <con2, S, waiting for con1>,
86 --echo # <con1, X REC_NOT_GAP, granted>, <con2, S, waiting for con1>, <con1, INSERT INTENTION, waiting for con1>
87 --echo # Expected: a deadlock, as INSERT INTENTION should not overtake locks on gap, to not slice them
88 --connection con1
89 BEGIN;
90 SELECT * FROM t1 WHERE id=1 FOR UPDATE;
91
92 --connection con2
93 BEGIN;
94 SET DEBUG_SYNC = 'lock_wait_start SIGNAL con2_will_wait';
95 --send SELECT * FROM t1 LOCK IN SHARE MODE
96
97 --connection con1
98 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
99 INSERT INTO t1 VALUES (0);
100 ROLLBACK;
101
102 --connection con2
103 --error ER_LOCK_DEADLOCK
104 --reap
105 COMMIT;
106
107 --echo # More complicated scenario:
108 --echo # <con1, S, granted>,
109 --echo # <con1, S, granted>, <con2, S REC_NOT_GAP, granted>,
110 --echo # <con1, S, granted>, <con2, S REC_NOT_GAP, granted>, <con3, X, waiting for con1>
111 --echo # <con1, S, granted>, <con2, S REC_NOT_GAP, granted>, <con3, X, waiting for con1>, <con1, INSERT_INTENTION, waiting for con3>
112 --echo # <con1, S, granted>, <con3, X, waiting for con1>, <con1, INSERT_INTENTION, waiting for con3>
113 --echo # Expected: a deadlock, as INSERT INTENTION should not overtake locks on gap, to not slice them
114
115 --connection con1
116 BEGIN;
117 SELECT * FROM t1 LOCK IN SHARE MODE;
118
119 --connection con2
120 BEGIN;
121 SELECT * FROM t1 WHERE id=1 LOCK IN SHARE MODE;
122
123 --connection con3
124 SET DEBUG_SYNC = 'lock_wait_before_suspend SIGNAL con3_will_wait';
125 --send SELECT * FROM t1 FOR UPDATE
126
127 --connection con1
128 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait';
129 SET DEBUG_SYNC = 'lock_wait_start SIGNAL con1_will_wait';
130 --send INSERT INTO t1 VALUES (0)
131
132 --connection con2
133 SET DEBUG_SYNC = 'now WAIT_FOR con1_will_wait';
134 COMMIT;
135
136 --connection con1
137 --reap
138 ROLLBACK;
139
140 --connection con3
141 --error ER_LOCK_DEADLOCK
142 --reap
143
144 --echo # More complicated scenario.
145 --echo # <con1, S, granted>,
146 --echo # <con1, S, granted>, <con2, S REC_NOT_GAP, granted>,
147 --echo # <con1, S, granted>, <con2, S REC_NOT_GAP, granted>, <con3, X, waiting for con1>
148 --echo # <con1, S, granted>, <con2, S REC_NOT_GAP, granted>, <con3, X, waiting for con1>, <con1, X REC_NOT_GAP, waiting for con2>
149 --echo # Before MDEV-34877:
150 --echo # <con1, S, granted>, <con3, X, waiting for con1>, <con1, X REC_NOT_GAP, waiting for con3>
151 --echo # After MDEV-34877:
152 --echo # <con1, S, granted>, <con1, X REC_NOT_GAP, granted>, <con3, X, waiting for con1>
153
154
155 --connection con1
156 BEGIN;
157 SELECT * FROM t1 LOCK IN SHARE MODE;
158
159 --connection con2
160 BEGIN;
161 SELECT * FROM t1 WHERE id=1 LOCK IN SHARE MODE;
162
163 --connection default
164
165 --connection con3
166 SET DEBUG_SYNC = 'lock_wait_before_suspend SIGNAL con3_will_wait';
167 --send SELECT * FROM t1 FOR UPDATE
168
169 --connection con1
170 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait';
171 SET DEBUG_SYNC = 'lock_wait_before_suspend SIGNAL con1_will_wait';
172 --send SELECT * FROM t1 WHERE id=1 FOR UPDATE
173
174 --connection con2
175 SET DEBUG_SYNC = 'now WAIT_FOR con1_will_wait';
176 COMMIT;
177
178 --connection con1
179 --reap
180 COMMIT;
181
182 --connection con3
183 --reap
184 COMMIT;
185
186 --echo # A secenario, where con1 has to bypass two transactions:
187 --echo # <con1, S, granted>
188 --echo # <con1, S, granted> <con2, X, waiting>
189 --echo # <con1, S, granted> <con2, X, waiting> <con3, X, waiting>
190 --echo # Before MDEV-34877:
191 --echo # <con1, S, granted> <con2, X, waiting> <con3, X, waiting> <con1, X REC_NOT_GAP, waiting for con2>
192 --echo # After MDEV-34877:
193 --echo # <con1, S, granted> <con1, X REC_NOT_GAP, granted> <con2, X, waiting> <con3, X, waiting>
194 --connection con1
195 BEGIN;
196 SELECT * FROM t1 LOCK IN SHARE MODE;
197
198 --connection con2
199 SET DEBUG_SYNC = 'lock_wait_before_suspend SIGNAL con2_will_wait';
200 --send SELECT * FROM t1 FOR UPDATE
201
202 --connection con3
203 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
204 SET DEBUG_SYNC = 'lock_wait_before_suspend SIGNAL con3_will_wait';
205 --send SELECT * FROM t1 FOR UPDATE
206
207 --connection con1
208 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait';
209 SELECT * FROM t1 WHERE id=1 FOR UPDATE;
210 COMMIT;
211
212 --connection con2
213 --reap
214 COMMIT;
215
216 --connection con3
217 --reap
218 COMMIT;
219
220 --connection default
221 --disconnect con1
222 --disconnect con2
223 --disconnect con3
224 --disconnect stop_purge
225
226 DROP TABLE t1;
227
228 --source include/wait_until_count_sessions.inc