Debian Codegraph @

mysql-test/suite/innodb/r/innodb-lock-inherit-read_commited.result

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