Debian Codegraph @

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

raw
1 --source include/have_debug_sync.inc
2 --source include/count_sessions.inc
3
4 --source ../include/prepare_show_locks.inc
5 --connect (con1,localhost,root,,)
6 CALL register_connection("con1");
7 --connect (con2,localhost,root,,)
8 CALL register_connection("con2");
9 --connect (con3,localhost,root,,)
10 CALL register_connection("con3");
11
12 --connection default
13 CREATE TABLE t1 (id INT PRIMARY KEY);
14 INSERT INTO t1 (id) VALUES (1);
15 # Simplest scenario:
16 # <con1, S, granted>,
17 # <con1, S, granted>, <con2, X, waiting for con1>,
18 # <con1, S, granted>, <con2, X, waiting for con1>, <con1, X, waiting for con1>
19 # Expected: instead of deadlocking, the con1's request should ingore con2's
20
21 --connection con1
22 BEGIN;
23 SELECT * FROM t1 FOR SHARE;
24
25 --connection con2
26 BEGIN;
27 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait';
28 --send SELECT * FROM t1 FOR UPDATE
29
30 --connection con1
31 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
32 SELECT * FROM t1 FOR UPDATE;
33 COMMIT;
34
35 --connection con2
36 --reap
37 COMMIT;
38
39 # A variant of the above scenario:
40 # <con1, X REC_NOT_GAP, granted>,
41 # <con1, X REC_NOT_GAP, granted>, <con2, S, waiting for con1>,
42 # <con1, X REC_NOT_GAP, granted>, <con2, S, waiting for con1>, <con1, INSERT INTENTION, waiting for con1>
43 # Expected: a deadlock, as INSERT INTENTION should not overtake locks on gap, to not slice them
44 --connection con1
45 BEGIN;
46 SELECT * FROM t1 WHERE id=1 FOR UPDATE;
47
48 --connection con2
49 BEGIN;
50 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait';
51 --send SELECT * FROM t1 FOR SHARE
52
53 --connection con1
54 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
55 CALL show_locks();
56 INSERT INTO t1 VALUES (0);
57 CALL show_locks();
58 ROLLBACK;
59
60 --connection con2
61 --error ER_LOCK_DEADLOCK
62 --reap
63 COMMIT;
64
65
66 # More complicated scenario:
67 # <con1, S, granted>,
68 # <con2, S REC_NOT_GAP, granted>, <con1, S, granted>,
69 # <con2, S REC_NOT_GAP, granted>, <con1, S, granted>, <con3, X, waiting for con2>
70 # <con2, S REC_NOT_GAP, granted>, <con1, S, granted>, <con3, X, waiting for con2>, <con1, INSERT_INTENTION, waiting for con3>
71 # <con1, S, granted>, <con3, X, waiting for con1>, <con1, INSERT_INTENTION, waiting for con3>
72 # Expected: a deadlock, as INSERT INTENTION should not overtake locks on gap, to not slice them
73
74 --connection con1
75 BEGIN;
76 SELECT * FROM t1 FOR SHARE;
77
78 --connection con2
79 BEGIN;
80 SELECT * FROM t1 WHERE id=1 FOR SHARE;
81
82 --connection default
83 CALL show_locks();
84
85 --connection con3
86 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con3_will_wait';
87 --send SELECT * FROM t1 FOR UPDATE
88
89 --connection con1
90 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait';
91 CALL show_locks();
92 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con1_will_wait';
93 --send INSERT INTO t1 VALUES (0)
94
95 --connection con2
96 SET DEBUG_SYNC = 'now WAIT_FOR con1_will_wait';
97 CALL show_locks();
98 COMMIT;
99 CALL show_locks();
100
101 --connection con1
102 --reap
103 ROLLBACK;
104 CALL show_locks();
105
106
107 --connection con3
108 --error ER_LOCK_DEADLOCK
109 --reap
110
111 # More complicated scenario. It's not ideal. Ideally, we'd like con1's X REC_NOT_GAP request to be
112 # considered blocked by con3, and then to bypass it exceptionally because of con3's being blocked by con1.
113 # What happens in reality is that con1's request will get blocked by con2, not con3, because we search for
114 # a blocker in the queue starting from the granted locks. And then, when con2 releases locks, both con3 and con1
115 # are candidates to grant the lock, and con3 can't be granted the lock because it's blocked by con1's S lock,
116 # and con1 can be granted the X REC_NOT_GAP lock, because CATS algorithm doesn't check for conflicts with waiting
117 # locks during release induced granting. Thus the heuristic "to bypass waiters blocked by us" doesn't really take
118 # part in this scenario. Still, it's good to verify that it works without a deadlock.
119 # <con1, S, granted>,
120 # <con2, S REC_NOT_GAP, granted>, <con1, S, granted>,
121 # <con2, S REC_NOT_GAP, granted>, <con1, S, granted>, <con3, X, waiting for con2>
122 # <con2, S REC_NOT_GAP, granted>, <con1, S, granted>, <con3, X, waiting for con2>, <con1, X REC_NOT_GAP, waiting for con2>
123 # <con1, X REC_NOT_GAP, granted>, <con1, S, granted>, <con3, X, waiting for con1>,
124 # <con3, X, granted>,
125 # done.
126
127 --connection con1
128 BEGIN;
129 SELECT * FROM t1 FOR SHARE;
130
131 --connection con2
132 BEGIN;
133 SELECT * FROM t1 WHERE id=1 FOR SHARE;
134
135 --connection default
136 CALL show_locks();
137
138 --connection con3
139 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con3_will_wait';
140 --send SELECT * FROM t1 FOR UPDATE
141
142 --connection con1
143 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait';
144 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con1_will_wait';
145 --send SELECT * FROM t1 WHERE id=1 FOR UPDATE
146
147 --connection con2
148 SET DEBUG_SYNC = 'now WAIT_FOR con1_will_wait';
149 CALL show_locks();
150 COMMIT;
151 CALL show_locks();
152
153 --connection con1
154 --reap
155 COMMIT;
156 CALL show_locks();
157
158 --connection con3
159 --reap
160 COMMIT;
161 # A secenario, where con1 has to bypass two transactions, thus testing proper caching:
162 # <con1, S, granted>
163 # <con1, S, granted> <con2, X, waiting>
164 # <con1, S, granted> <con2, X, waiting> <con3, X, waiting>
165 # <con1, X REC_NOT_GAP, granted> <con1, S, granted> <con2, X, waiting> <con3, X, waiting>
166
167 --connection con1
168 BEGIN;
169 SELECT * FROM t1 FOR SHARE;
170
171 --connection con2
172 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait';
173 --send SELECT * FROM t1 FOR UPDATE
174
175 --connection con3
176 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
177 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con3_will_wait';
178 --send SELECT * FROM t1 FOR UPDATE
179
180 --connection con1
181 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait';
182 CALL show_locks();
183 SELECT * FROM t1 WHERE id=1 FOR UPDATE;
184 CALL show_locks();
185 COMMIT;
186
187 --connection con2
188 --reap
189 COMMIT;
190
191 --connection con3
192 --reap
193 COMMIT;
194
195 --connection default
196 --disconnect con1
197 --disconnect con2
198 --disconnect con3
199
200 DROP TABLE t1;
201
202 --source ../include/cleanup_show_locks.inc
203 --source include/wait_until_count_sessions.inc