Debian Codegraph @

mysql-test/suite/innodb/t/cats-autoinc.test

raw
1 --source include/have_debug_sync.inc
2
3 --echo ################################################################
4 --echo # #
5 --echo # Bug #27944920: INNODB: ASSERTION FAILURE: #
6 --echo # LOCK_GET_TYPE_LOW(LOCK) == 32 IN LOCK0PRIV.IC #
7 --echo # #
8 --echo ################################################################
9
10 # We want a situation in which:
11 # C1 has a rec lock granted
12 # C1 waits for a table lock
13 # C2 waits for a rec lock
14 # and this triggers the bug in proccessing C1.wait_lock of LOCK_TABLE type.
15 # However it turns out it is not so simple to generate a situation in which
16 # C1 waits for a table lock, because most of the time table level conflicts
17 # are handled at the mysql layer, not at the InnoDB layer.
18 # In particular IS and IX table locks do not conflict with each other, and
19 # they conflict with S or X table locks, but to obtain X or S lock you need to
20 # perform LOCK TABLES ..statement which informs the mysql layer about what
21 # you are doing, making it much harder to get trough with conflicting queries
22 # to the InnoDB layer.
23 # The simplest way to slip trough the mysql's checks is to play with LOCK_AUTO_INC
24 # locks, which conflict with each other.
25 # There is a difficulty though: even in the traditional innodb_autoinc_lock_mode=0
26 # such locks are held only for the duration of a query, and not for the whole transaction.
27 # So, if you want to make the LOCK_AUTO_INC to be held long enough to cause another
28 # transaction to conflict with it, you must somehow make the query take longer.
29 # A simple way to do that is to use INSERT ... SELECT ... statement, where
30 # the SELECT has to wait for a lock.
31 # So compiling this all ideas together here is our plan:
32 # 1. C3 obtains an X-lock on a record t2.id=2
33 # (fun fact: it will not work with id=1, because in step 2, we need at least one
34 # row to be successfuly inserted before blocking on the locked row)
35 # 2. C4 performs INSERT INTO t1 (val) SELECT id FROM t2;
36 # and has to wait for C3 to release rec lock,
37 # while C4 is holding LOCK_AUTO_INC
38 # 3. C1 obtains an S-lock on a record t1.id=1
39 # 4. C1 performs INSERT INTO T1 (val) VALUES (7)
40 # and has to wait for C4 to release LOCK_AUTO_INC
41 # 5. C2 tries to SELECT * FROM t1 WHERE id=1 FOR UPDATE
42 # and has to wait for C1 to realse the S-lock
43 # this causes the bug
44
45 # This test requires innodb_autoinc_lock_mode == 0, so we explicitly check it here:
46
47 SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
48
49 # Prepare the tables
50
51 CREATE TABLE t1 (
52 id INT PRIMARY KEY AUTO_INCREMENT,
53 val INT
54 ) Engine=InnoDB;
55
56 CREATE TABLE t2 (
57 id INT PRIMARY KEY
58 ) Engine=InnoDB;
59
60 INSERT INTO t1 (id, val) VALUES (1,1);
61 INSERT INTO t2 (id) VALUES (1),(2),(3);
62
63 # Save the original settings, to be restored at the end of test
64 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
65
66 # Make sure that transactions will not finish prematurely
67 SET @@global.innodb_lock_wait_timeout = 100000;
68
69
70 --connect (C1, localhost, root,,)
71 --connect (C2, localhost, root,,)
72 --connect (C3, localhost, root,,)
73 --connect (C4, localhost, root,,)
74
75 --connection C3
76 BEGIN;
77 SELECT * FROM t2 WHERE id=2 FOR UPDATE;
78
79 --connection C4
80 BEGIN;
81 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL C4_will_wait';
82 --send INSERT INTO t1 (val) SELECT id FROM t2
83
84 --connection C1
85 BEGIN;
86 SELECT * FROM t1 WHERE id=1 FOR SHARE;
87 SET DEBUG_SYNC = 'now WAIT_FOR C4_will_wait';
88 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL C1_will_wait';
89 --send INSERT INTO t1 (val) VALUES (7)
90
91 --connection C2
92 BEGIN;
93 SET DEBUG_SYNC = 'now WAIT_FOR C1_will_wait';
94 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL C2_will_wait';
95 --send SELECT * FROM t1 WHERE id=1 FOR UPDATE
96
97 # The bug if present, will manifest at this moment.
98 --connection default
99 SET DEBUG_SYNC = 'now WAIT_FOR C2_will_wait';
100
101 # Clean up all transactions
102 --connection C3
103 ROLLBACK;
104
105 --connection C4
106 --reap
107 ROLLBACK;
108
109 --connection C1
110 --reap
111 ROLLBACK;
112
113 --connection C2
114 --reap
115 ROLLBACK;
116
117 # Clean up connections
118
119 --connection default
120 --disconnect C1
121 --disconnect C2
122 --disconnect C3
123 --disconnect C4
124
125 # Clean up tables
126
127 DROP TABLE t2;
128 DROP TABLE t1;
129
130 # Restore saved state
131
132 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;
133
134 --echo ########################
135 --echo # #
136 --echo # End of Bug #27944920 #
137 --echo # #
138 --echo ########################
139
140 # Following scenario is intended to cover the rare case of trx being
141 # killed while waiting for a table lock, which excersises the table
142 # lock case in lock_cancel_waiting_and_release function.
143 #
144 # To generate a situation when trx is waiting for a table lock inside
145 # InnoDB we use following scenario:
146 # C1 locks t2.id = 2
147 # C3 locks t2.id = 3
148 # C2 obtains t1.AUTO_INC and waits for C1 t2.id=2 row lock
149 # C3 tries to insert to t1, and has to wait for C2's autoinc lock
150 # C1 rolls back, which unlocks t2.id=2, and C2 proceeds to lock t2.id=3,
151 # and now is blocked by C3, but C3 is already blocked by C2, so we have
152 # a deadlock cycle.
153 # We make C2 heavy to make sure that C3 is chosen as victim, by modyfing
154 # many rows in t3.
155
156 CREATE TABLE t1 (
157 id INT PRIMARY KEY AUTO_INCREMENT,
158 val INT
159 ) Engine=InnoDB;
160
161 CREATE TABLE t2 (
162 id INT PRIMARY KEY
163 ) Engine=InnoDB;
164
165 CREATE TABLE t3 (
166 id INT PRIMARY KEY,
167 val INT
168 ) Engine=InnoDB;
169
170 INSERT INTO t1 (id, val) VALUES (1,1);
171 INSERT INTO t2 (id) VALUES (1),(2),(3);
172 INSERT INTO t3 (id, val) VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
173
174 # Save the original settings, to be restored at the end of test
175 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
176
177 # Make sure that transactions will not finish prematurely
178 SET @@global.innodb_lock_wait_timeout = 100000;
179
180
181 --connect (C1, localhost, root,,)
182 --connect (C2, localhost, root,,)
183 --connect (C3, localhost, root,,)
184
185 --connection C1
186 BEGIN;
187 SELECT * FROM t2 WHERE id=2 FOR UPDATE;
188
189 --connection C3
190 BEGIN;
191 SELECT * FROM t2 WHERE id=3 FOR UPDATE;
192
193 --connection C2
194 BEGIN;
195 UPDATE t3 SET val = 13;
196 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL C2_will_wait';
197 --send INSERT INTO t1 (val) SELECT id FROM t2
198 # C2 --waits-for[t2.id=2]--> C1
199
200 --connection C3
201 SET DEBUG_SYNC = 'now WAIT_FOR C2_will_wait';
202 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL C3_will_wait';
203 --send INSERT INTO t1 (val) VALUES (13);
204 # C3 --waits-for[t1.autoinc]--> C2 --waits-for[t2.id=2]--> C1
205
206 --connection C1
207 SET DEBUG_SYNC = 'now WAIT_FOR C3_will_wait';
208 ROLLBACK;
209 # C3 --waits-for[t1.autoinc]--> C2 --waits-for[t2.id=3]--> C3
210 # this is a deadlock.
211
212 --connection C3
213 --error ER_LOCK_DEADLOCK
214 --reap
215 ROLLBACK;
216
217 --connection C2
218 --reap
219 ROLLBACK;
220
221 --connection default
222 --disconnect C1
223 --disconnect C2
224 --disconnect C3
225
226
227 DROP TABLES t1,t2,t3;
228
229 # Restore saved state
230
231 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;
232