Debian Codegraph @

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

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