Debian Codegraph @

mysql-test/suite/innodb/t/innodb-lock-inherit-read_commited.test

raw
1 --echo #
2 --echo # Bug #21025880 DUPLICATE UK VALUES IN READ-COMMITTED(AGAIN)
3 --echo #
4
5 --source include/have_debug.inc
6 --source include/have_debug_sync.inc
7
8 let i=0;
9
10 while ($i <=2 )
11 {
12
13 CREATE TABLE t1 (
14 a INT NOT NULL,
15 b INT NOT NULL,
16 PRIMARY KEY(b),
17 UNIQUE KEY(a))
18 ENGINE=INNODB;
19
20 SET @old_innodb_stats_auto_recalc = @@innodb_stats_auto_recalc;
21 SET GLOBAL innodb_stats_auto_recalc = OFF;
22
23 # Block purge
24 SET GLOBAL innodb_purge_stop_now = ON;
25
26 SET @old_transaction_isolation = @@transaction_isolation;
27 SET GLOBAL transaction_isolation = 'READ-COMMITTED';
28
29 SET @old_innodb_lock_wait_timeout = @@innodb_lock_wait_timeout;
30 SET GLOBAL innodb_lock_wait_timeout = 1;
31
32 --connect(con1,localhost,root,,)
33 --connect(con2,localhost,root,,)
34
35 --connection con1
36
37 # Create and delete-mark an index record
38
39 INSERT INTO t1 VALUES (1,1),(2,2);
40 DELETE FROM t1;
41
42
43 SET debug_sync = 'row_ins_sec_index_entry_dup_locks_created SIGNAL
44 con1_locks_done WAIT_FOR con1_go';
45 SET debug_sync = 'ha_commit_trans_after_acquire_commit_lock SIGNAL
46 con1_insert_done WAIT_FOR con1_finish';
47 --send
48
49 if ( $i == 0 )
50 {
51 REPLACE INTO t1 VALUES (1,2);
52 }
53
54 if ( $i == 1 )
55 {
56 INSERT INTO t1 values (1,2) ON DUPLICATE KEY UPDATE a=2;
57 }
58
59 if ( $i == 2 )
60 {
61 INSERT INTO t1 VALUES (1,2);
62 }
63
64
65 --connection con2
66
67 SET debug_sync = 'now WAIT_FOR con1_locks_done';
68 SET debug_sync = 'lock_wait_suspend_thread_enter SIGNAL con2_blocked
69 WAIT_FOR con2_go';
70 SET debug_sync = 'ha_commit_trans_after_acquire_commit_lock SIGNAL
71 con2_insert_done WAIT_FOR con2_finish';
72 SET debug_sync = 'ib_after_row_insert SIGNAL con2_insert_done';
73
74 --send
75 REPLACE INTO t1 VALUES (1,3);
76
77 --connection default
78 SET debug_sync = 'now WAIT_FOR con2_blocked';
79
80 SET GLOBAL innodb_purge_run_now=ON;
81
82 # Wait for purge to delete the delete-marked record
83 --source include/wait_innodb_all_purged.inc
84
85 SET debug_sync = 'now SIGNAL con2_go WAIT_FOR con2_insert_done';
86 SET debug_sync = 'now SIGNAL con1_go WAIT_FOR con1_insert_done';
87 SET debug_sync = 'now SIGNAL con1_finish';
88 SET debug_sync = 'now SIGNAL con2_finish';
89
90 --connection con1
91 --reap
92
93 --connection con2
94 --error 0,ER_LOCK_WAIT_TIMEOUT
95 --reap
96
97 --connection default
98
99 --disconnect con1
100 --disconnect con2
101
102 SELECT * FROM t1;
103 CHECK TABLE t1;
104
105 DROP TABLE t1;
106
107 SET GLOBAL innodb_stats_auto_recalc = @old_innodb_stats_auto_recalc;
108 SET GLOBAL transaction_isolation = @old_transaction_isolation;
109 SET GLOBAL innodb_lock_wait_timeout = @old_innodb_lock_wait_timeout;
110
111 # Clean up resources used in this test case.
112 --disable_warnings
113 SET DEBUG_SYNC= 'RESET';
114 --enable_warnings
115
116 --inc $i
117 }
118
119
120
121
122
123 --echo #
124 --echo # Bug#24344131 ASSERT !OTHER_LOCK IN LOCK_REC_ADD_TO_QUEUE AT LOCK0LOCK.CC:1641
125 --echo #
126
127 connect (con1,localhost,root,,);
128 connect (con2,localhost,root,,);
129 connect (con3,localhost,root,,);
130
131 connection con1;
132 CREATE TABLE t1(a INT, b INT, primary key(a));
133 INSERT INTO t1 VALUES(101, 20);
134 SET DEBUG_SYNC="before_lock_trx_release_locks SIGNAL s3 WAIT_FOR s5";
135 BEGIN;
136 INSERT INTO t1 VALUES(10, 20);
137
138 connection con2;
139 SET DEBUG_SYNC="before_lock_rec_convert_impl_to_expl_for_trx SIGNAL s2 WAIT_FOR s4";
140 SET DEBUG_SYNC="before_lock_wait_suspend SIGNAL s5 WAIT_FOR s7";
141 --send UPDATE t1 SET b=25 WHERE a=10
142
143 connection con1;
144 SET DEBUG_SYNC="now WAIT_FOR s2";
145 --send COMMIT
146
147 connection con3;
148 SET DEBUG_SYNC="now WAIT_FOR s3";
149 SET DEBUG_SYNC="after_lock_clust_rec_read_check_and_lock SIGNAL s4 WAIT_FOR s6";
150 --send UPDATE t1 SET b=30 WHERE a=10
151
152 connection con1;
153 --reap
154 SET DEBUG_SYNC="now SIGNAL s6";
155
156 connection con3;
157 --reap
158 SET DEBUG_SYNC="now SIGNAL s7";
159
160 connection con2;
161 --reap
162
163 connection default;
164
165 DROP TABLE t1;
166
167 --disconnect con1
168 --disconnect con2
169
170
171 --echo #
172 --echo # Bug #29004362 LOCK->TRX->DUPLICATES CAN NOT BE USED TO DETERMINE IF
173 --echo # LOCK IS CREATED BY IODKU
174 --echo #
175
176 CREATE TABLE t1 (
177 a INT NOT NULL,
178 b INT NOT NULL,
179 c INT NOT NULL,
180 PRIMARY KEY(b),
181 UNIQUE KEY(a),
182 UNIQUE KEY(c))
183 ENGINE=INNODB;
184
185 SET @old_innodb_stats_auto_recalc = @@innodb_stats_auto_recalc;
186 SET GLOBAL innodb_stats_auto_recalc = OFF;
187
188 # Block purge
189 SET GLOBAL innodb_purge_stop_now = ON;
190
191 SET @old_transaction_isolation = @@transaction_isolation;
192 SET GLOBAL transaction_isolation = 'READ-COMMITTED';
193
194 SET @old_innodb_lock_wait_timeout = @@innodb_lock_wait_timeout;
195 SET GLOBAL innodb_lock_wait_timeout = 1;
196
197 # Create and delete-mark an index record
198 INSERT INTO t1 VALUES (1,1,1),(2,2,2);
199 DELETE FROM t1;
200 INSERT INTO t1 VALUES (5,5,5),(7,7,7);
201
202 --connect(con1,localhost,root,,)
203 --connect(con2,localhost,root,,)
204
205 --connection con1
206
207 BEGIN;
208 INSERT INTO t1 VALUES (1,4,7) ON DUPLICATE KEY UPDATE b=6;
209
210 --connection default
211 SELECT MAX(engine_transaction_id) INTO @con1_trx_id FROM performance_schema.data_locks WHERE object_schema='test' AND object_name = 't1';
212 --echo # LOCKS AFTER IODKU
213 SELECT IF(engine_transaction_id=@con1_trx_id, "con1", "other") as con,
214 index_name,lock_type,lock_mode,lock_status,lock_data
215 FROM performance_schema.data_locks
216 WHERE object_schema='test' AND object_name = 't1'
217 ORDER BY 1,2,3,4,5,6;
218
219 --connection con1
220 SET debug_sync = 'row_ins_sec_index_entry_dup_locks_created SIGNAL
221 con1_locks_done WAIT_FOR con1_go';
222
223 --send INSERT INTO t1 VALUES (1,2,3);COMMIT
224
225 --connection default
226 SET debug_sync = 'now WAIT_FOR con1_locks_done';
227 --echo # LOCKS AFTER con1_locks_done
228 SELECT IF(engine_transaction_id=@con1_trx_id, "con1", "other") as con,
229 index_name,lock_type,lock_mode,lock_status,lock_data
230 FROM performance_schema.data_locks
231 WHERE object_schema='test' AND object_name = 't1'
232 ORDER BY 1,2,3,4,5,6;
233
234 SELECT COUNT(*) FROM performance_schema.data_locks
235 WHERE object_schema='test'
236 AND object_name='t1'
237 AND index_name='a'
238 AND lock_type='RECORD'
239 AND lock_data='5, 5';
240
241 SET GLOBAL innodb_purge_run_now=ON;
242
243 # Wait for purge to delete the delete-marked record
244 let $wait_condition=
245 SELECT COUNT(*) = 1 FROM performance_schema.data_locks
246 WHERE object_schema='test'
247 AND object_name='t1'
248 AND index_name='a'
249 AND lock_type='RECORD'
250 AND lock_data='5, 5';
251 --source include/wait_condition.inc
252
253 --echo # LOCKS AFTER purge
254 SELECT IF(engine_transaction_id=@con1_trx_id, "con1", "other") as con,
255 index_name,lock_type,lock_mode,lock_status,lock_data
256 FROM performance_schema.data_locks
257 WHERE object_schema='test' AND object_name = 't1'
258 ORDER BY 1,2,3,4,5,6;
259
260 --connection con2
261 # We actually expect ER_LOCK_WAIT_TIMEOUT, but we let a buggy implementation
262 # continue, so that we can capture more information about locks and rows,
263 # and in particular if there are two rows with a=1 in the end.
264 --error 0, ER_LOCK_WAIT_TIMEOUT
265 INSERT t1 VALUES (1,10,4);
266
267 --connection default
268
269 --echo # LOCKS AFTER con2
270 SELECT IF(engine_transaction_id=@con1_trx_id, "con1", "other") as con,
271 index_name,lock_type,lock_mode,lock_status,lock_data
272 FROM performance_schema.data_locks
273 WHERE object_schema='test' AND object_name = 't1'
274 ORDER BY 1,2,3,4,5,6;
275
276 SET debug_sync = 'now SIGNAL con1_go';
277
278
279 --connection con1
280 --reap
281
282 --connection default
283
284 --disconnect con1
285 --disconnect con2
286
287 SELECT * FROM t1;
288 CHECK TABLE t1;
289
290 DROP TABLE t1;
291
292 SET GLOBAL innodb_stats_auto_recalc = @old_innodb_stats_auto_recalc;
293 SET GLOBAL transaction_isolation = @old_transaction_isolation;
294 SET GLOBAL innodb_lock_wait_timeout = @old_innodb_lock_wait_timeout;
295
296 # Clean up resources used in this test case.
297 --disable_warnings
298 SET DEBUG_SYNC= 'RESET';
299 --enable_warnings