Debian Codegraph @

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

raw
1 DROP TABLE IF EXISTS t1;
2 SET GLOBAL innodb_adaptive_hash_index = false;
3 SET GLOBAL innodb_stats_persistent = false;
4 CREATE TABLE t1 (
5 a00 CHAR(255) NOT NULL DEFAULT 'a',
6 a01 CHAR(255) NOT NULL DEFAULT 'a',
7 a02 CHAR(255) NOT NULL DEFAULT 'a',
8 a03 CHAR(255) NOT NULL DEFAULT 'a',
9 a04 CHAR(255) NOT NULL DEFAULT 'a',
10 a05 CHAR(255) NOT NULL DEFAULT 'a',
11 a06 CHAR(255) NOT NULL DEFAULT 'a',
12 b INT NOT NULL DEFAULT 0
13 ) charset latin1 ENGINE = InnoDB;
14 ALTER TABLE t1 ADD CONSTRAINT pkey PRIMARY KEY(
15 a00,
16 a01,
17 a02,
18 a03,
19 a04,
20 a05,
21 a06
22 );
23 ANALYZE TABLE t1;
24 Table Op Msg_type Msg_text
25 test.t1 analyze status OK
26 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
27 CLUST_INDEX_SIZE
28 1
29 SET GLOBAL innodb_limit_optimistic_insert_debug = 7;
30 INSERT INTO t1 (a00) VALUES ('aa');
31 INSERT INTO t1 (a00) VALUES ('ab');
32 INSERT INTO t1 (a00) VALUES ('ac');
33 INSERT INTO t1 (a00) VALUES ('ad');
34 INSERT INTO t1 (a00) VALUES ('ae');
35 INSERT INTO t1 (a00) VALUES ('af');
36 INSERT INTO t1 (a00) VALUES ('ag');
37 INSERT INTO t1 (a00) VALUES ('ah');
38 ANALYZE TABLE t1;
39 Table Op Msg_type Msg_text
40 test.t1 analyze status OK
41 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
42 CLUST_INDEX_SIZE
43 3
44 INSERT INTO t1 (a00) VALUES ('ai');
45 INSERT INTO t1 (a00) VALUES ('aj');
46 INSERT INTO t1 (a00) VALUES ('ak');
47 ANALYZE TABLE t1;
48 Table Op Msg_type Msg_text
49 test.t1 analyze status OK
50 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
51 CLUST_INDEX_SIZE
52 4
53 INSERT INTO t1 (a00) VALUES ('al');
54 INSERT INTO t1 (a00) VALUES ('am');
55 INSERT INTO t1 (a00) VALUES ('an');
56 INSERT INTO t1 (a00) VALUES ('ao');
57 INSERT INTO t1 (a00) VALUES ('ap');
58 INSERT INTO t1 (a00) VALUES ('aq');
59 INSERT INTO t1 (a00) VALUES ('ar');
60 ANALYZE TABLE t1;
61 Table Op Msg_type Msg_text
62 test.t1 analyze status OK
63 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
64 CLUST_INDEX_SIZE
65 5
66 INSERT INTO t1 (a00) VALUES ('as');
67 INSERT INTO t1 (a00) VALUES ('at');
68 INSERT INTO t1 (a00) VALUES ('au');
69 INSERT INTO t1 (a00) VALUES ('av');
70 INSERT INTO t1 (a00) VALUES ('aw');
71 INSERT INTO t1 (a00) VALUES ('ax');
72 INSERT INTO t1 (a00) VALUES ('ay');
73 ANALYZE TABLE t1;
74 Table Op Msg_type Msg_text
75 test.t1 analyze status OK
76 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
77 CLUST_INDEX_SIZE
78 6
79 INSERT INTO t1 (a00) VALUES ('az');
80 INSERT INTO t1 (a00) VALUES ('ba');
81 INSERT INTO t1 (a00) VALUES ('bb');
82 INSERT INTO t1 (a00) VALUES ('bc');
83 INSERT INTO t1 (a00) VALUES ('bd');
84 INSERT INTO t1 (a00) VALUES ('be');
85 INSERT INTO t1 (a00) VALUES ('bf');
86 ANALYZE TABLE t1;
87 Table Op Msg_type Msg_text
88 test.t1 analyze status OK
89 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
90 CLUST_INDEX_SIZE
91 7
92 INSERT INTO t1 (a00) VALUES ('bg');
93 INSERT INTO t1 (a00) VALUES ('bh');
94 INSERT INTO t1 (a00) VALUES ('bi');
95 INSERT INTO t1 (a00) VALUES ('bj');
96 INSERT INTO t1 (a00) VALUES ('bk');
97 INSERT INTO t1 (a00) VALUES ('bl');
98 INSERT INTO t1 (a00) VALUES ('bm');
99 ANALYZE TABLE t1;
100 Table Op Msg_type Msg_text
101 test.t1 analyze status OK
102 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
103 CLUST_INDEX_SIZE
104 8
105 INSERT INTO t1 (a00) VALUES ('bn');
106 INSERT INTO t1 (a00) VALUES ('bo');
107 INSERT INTO t1 (a00) VALUES ('bp');
108 INSERT INTO t1 (a00) VALUES ('bq');
109 INSERT INTO t1 (a00) VALUES ('br');
110 INSERT INTO t1 (a00) VALUES ('bs');
111 INSERT INTO t1 (a00) VALUES ('bt');
112 ANALYZE TABLE t1;
113 Table Op Msg_type Msg_text
114 test.t1 analyze status OK
115 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
116 CLUST_INDEX_SIZE
117 11
118 INSERT INTO t1 (a00) VALUES ('bu');
119 INSERT INTO t1 (a00) VALUES ('bv');
120 INSERT INTO t1 (a00) VALUES ('bw');
121 INSERT INTO t1 (a00) VALUES ('bx');
122 INSERT INTO t1 (a00) VALUES ('by');
123 INSERT INTO t1 (a00) VALUES ('bz');
124 INSERT INTO t1 (a00) VALUES ('ca');
125 INSERT INTO t1 (a00) VALUES ('cb');
126 INSERT INTO t1 (a00) VALUES ('cc');
127 INSERT INTO t1 (a00) VALUES ('cd');
128 INSERT INTO t1 (a00) VALUES ('ce');
129 INSERT INTO t1 (a00) VALUES ('cf');
130 INSERT INTO t1 (a00) VALUES ('cg');
131 INSERT INTO t1 (a00) VALUES ('ch');
132 INSERT INTO t1 (a00) VALUES ('ci');
133 INSERT INTO t1 (a00) VALUES ('cj');
134 INSERT INTO t1 (a00) VALUES ('ck');
135 INSERT INTO t1 (a00) VALUES ('cl');
136 INSERT INTO t1 (a00) VALUES ('cm');
137 INSERT INTO t1 (a00) VALUES ('cn');
138 INSERT INTO t1 (a00) VALUES ('co');
139 ANALYZE TABLE t1;
140 Table Op Msg_type Msg_text
141 test.t1 analyze status OK
142 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
143 CLUST_INDEX_SIZE
144 15
145 INSERT INTO t1 (a00) VALUES ('cp');
146 INSERT INTO t1 (a00) VALUES ('cq');
147 INSERT INTO t1 (a00) VALUES ('cr');
148 INSERT INTO t1 (a00) VALUES ('cs');
149 INSERT INTO t1 (a00) VALUES ('ct');
150 INSERT INTO t1 (a00) VALUES ('cu');
151 INSERT INTO t1 (a00) VALUES ('cv');
152 INSERT INTO t1 (a00) VALUES ('cw');
153 INSERT INTO t1 (a00) VALUES ('cx');
154 INSERT INTO t1 (a00) VALUES ('cy');
155 INSERT INTO t1 (a00) VALUES ('cz');
156 INSERT INTO t1 (a00) VALUES ('da');
157 INSERT INTO t1 (a00) VALUES ('db');
158 INSERT INTO t1 (a00) VALUES ('dc');
159 INSERT INTO t1 (a00) VALUES ('dd');
160 INSERT INTO t1 (a00) VALUES ('de');
161 INSERT INTO t1 (a00) VALUES ('df');
162 INSERT INTO t1 (a00) VALUES ('dg');
163 INSERT INTO t1 (a00) VALUES ('dh');
164 INSERT INTO t1 (a00) VALUES ('di');
165 INSERT INTO t1 (a00) VALUES ('dj');
166 INSERT INTO t1 (a00) VALUES ('dk');
167 INSERT INTO t1 (a00) VALUES ('dl');
168 INSERT INTO t1 (a00) VALUES ('dm');
169 INSERT INTO t1 (a00) VALUES ('dn');
170 INSERT INTO t1 (a00) VALUES ('do');
171 INSERT INTO t1 (a00) VALUES ('dp');
172 INSERT INTO t1 (a00) VALUES ('dq');
173 INSERT INTO t1 (a00) VALUES ('dr');
174 INSERT INTO t1 (a00) VALUES ('ds');
175 INSERT INTO t1 (a00) VALUES ('dt');
176 INSERT INTO t1 (a00) VALUES ('du');
177 INSERT INTO t1 (a00) VALUES ('dv');
178 INSERT INTO t1 (a00) VALUES ('dw');
179 INSERT INTO t1 (a00) VALUES ('dx');
180 INSERT INTO t1 (a00) VALUES ('dy');
181 INSERT INTO t1 (a00) VALUES ('dz');
182 INSERT INTO t1 (a00) VALUES ('ea');
183 INSERT INTO t1 (a00) VALUES ('eb');
184 INSERT INTO t1 (a00) VALUES ('ec');
185 INSERT INTO t1 (a00) VALUES ('ed');
186 INSERT INTO t1 (a00) VALUES ('ee');
187 INSERT INTO t1 (a00) VALUES ('ef');
188 INSERT INTO t1 (a00) VALUES ('eg');
189 INSERT INTO t1 (a00) VALUES ('eh');
190 INSERT INTO t1 (a00) VALUES ('ei');
191 INSERT INTO t1 (a00) VALUES ('ej');
192 INSERT INTO t1 (a00) VALUES ('ek');
193 INSERT INTO t1 (a00) VALUES ('el');
194 ANALYZE TABLE t1;
195 Table Op Msg_type Msg_text
196 test.t1 analyze status OK
197 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
198 CLUST_INDEX_SIZE
199 23
200 INSERT INTO t1 (a00) VALUES ('em');
201 INSERT INTO t1 (a00) VALUES ('en');
202 INSERT INTO t1 (a00) VALUES ('eo');
203 INSERT INTO t1 (a00) VALUES ('ep');
204 INSERT INTO t1 (a00) VALUES ('eq');
205 INSERT INTO t1 (a00) VALUES ('er');
206 INSERT INTO t1 (a00) VALUES ('es');
207 INSERT INTO t1 (a00) VALUES ('et');
208 INSERT INTO t1 (a00) VALUES ('eu');
209 INSERT INTO t1 (a00) VALUES ('ev');
210 INSERT INTO t1 (a00) VALUES ('ew');
211 INSERT INTO t1 (a00) VALUES ('ex');
212 INSERT INTO t1 (a00) VALUES ('ey');
213 INSERT INTO t1 (a00) VALUES ('ez');
214 INSERT INTO t1 (a00) VALUES ('fa');
215 INSERT INTO t1 (a00) VALUES ('fb');
216 INSERT INTO t1 (a00) VALUES ('fc');
217 INSERT INTO t1 (a00) VALUES ('fd');
218 INSERT INTO t1 (a00) VALUES ('fe');
219 INSERT INTO t1 (a00) VALUES ('ff');
220 INSERT INTO t1 (a00) VALUES ('fg');
221 INSERT INTO t1 (a00) VALUES ('fh');
222 INSERT INTO t1 (a00) VALUES ('fi');
223 INSERT INTO t1 (a00) VALUES ('fj');
224 INSERT INTO t1 (a00) VALUES ('fk');
225 INSERT INTO t1 (a00) VALUES ('fl');
226 INSERT INTO t1 (a00) VALUES ('fm');
227 INSERT INTO t1 (a00) VALUES ('fn');
228 INSERT INTO t1 (a00) VALUES ('fo');
229 INSERT INTO t1 (a00) VALUES ('fp');
230 INSERT INTO t1 (a00) VALUES ('fq');
231 INSERT INTO t1 (a00) VALUES ('fr');
232 INSERT INTO t1 (a00) VALUES ('fs');
233 INSERT INTO t1 (a00) VALUES ('ft');
234 INSERT INTO t1 (a00) VALUES ('fu');
235 INSERT INTO t1 (a00) VALUES ('fv');
236 INSERT INTO t1 (a00) VALUES ('fw');
237 INSERT INTO t1 (a00) VALUES ('fx');
238 INSERT INTO t1 (a00) VALUES ('fy');
239 INSERT INTO t1 (a00) VALUES ('fz');
240 INSERT INTO t1 (a00) VALUES ('ga');
241 INSERT INTO t1 (a00) VALUES ('gb');
242 INSERT INTO t1 (a00) VALUES ('gc');
243 INSERT INTO t1 (a00) VALUES ('gd');
244 INSERT INTO t1 (a00) VALUES ('ge');
245 INSERT INTO t1 (a00) VALUES ('gf');
246 INSERT INTO t1 (a00) VALUES ('gg');
247 INSERT INTO t1 (a00) VALUES ('gh');
248 ANALYZE TABLE t1;
249 Table Op Msg_type Msg_text
250 test.t1 analyze status OK
251 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
252 CLUST_INDEX_SIZE
253 29
254 SET GLOBAL innodb_limit_optimistic_insert_debug = 0;
255 # Test start
256 SET DEBUG_SYNC = 'RESET';
257 INSERT INTO t1 (a00) VALUES ('bfa');
258 SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue';
259 INSERT INTO t1 (a00) VALUES ('bfb');
260 SET DEBUG_SYNC = 'now WAIT_FOR reached';
261 SELECT a00,a01 FROM t1 WHERE a00 = 'aa';
262 a00 a01
263 aa a
264 SELECT a00,a01 FROM t1 WHERE a00 = 'aq';
265 a00 a01
266 aq a
267 SELECT a00,a01 FROM t1 WHERE a00 = 'cp';
268 a00 a01
269 cp a
270 SELECT a00,a01 FROM t1 WHERE a00 = 'el';
271 a00 a01
272 el a
273 SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1';
274 SELECT a00,a01 FROM t1 WHERE a00 = 'ar';
275 SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2';
276 SELECT a00,a01 FROM t1 WHERE a00 = 'cn';
277 SET DEBUG_SYNC = 'now WAIT_FOR lockwait1';
278 SET DEBUG_SYNC = 'now WAIT_FOR lockwait2';
279 SET DEBUG_SYNC = 'now SIGNAL continue';
280 a00 a01
281 ar a
282 a00 a01
283 cn a
284 ANALYZE TABLE t1;
285 Table Op Msg_type Msg_text
286 test.t1 analyze status OK
287 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
288 CLUST_INDEX_SIZE
289 30
290 SET DEBUG_SYNC = 'RESET';
291 INSERT INTO t1 (a00) VALUES ('coa');
292 SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue';
293 INSERT INTO t1 (a00) VALUES ('cob');
294 SET DEBUG_SYNC = 'now WAIT_FOR reached';
295 SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1';
296 SELECT a00,a01 FROM t1 WHERE a00 = 'aa';
297 SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2';
298 SELECT a00,a01 FROM t1 WHERE a00 = 'el';
299 SET DEBUG_SYNC = 'now WAIT_FOR lockwait1';
300 SET DEBUG_SYNC = 'now WAIT_FOR lockwait2';
301 SET DEBUG_SYNC = 'now SIGNAL continue';
302 a00 a01
303 aa a
304 a00 a01
305 el a
306 ANALYZE TABLE t1;
307 Table Op Msg_type Msg_text
308 test.t1 analyze status OK
309 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
310 CLUST_INDEX_SIZE
311 31
312 SET DEBUG_SYNC = 'RESET';
313 INSERT INTO t1 (a00) VALUES ('gba');
314 SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue';
315 INSERT INTO t1 (a00) VALUES ('gbb');
316 SET DEBUG_SYNC = 'now WAIT_FOR reached';
317 SELECT a00,a01 FROM t1 WHERE a00 = 'aa';
318 a00 a01
319 aa a
320 SELECT a00,a01 FROM t1 WHERE a00 = 'ek';
321 a00 a01
322 ek a
323 SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1';
324 SELECT a00,a01 FROM t1 WHERE a00 = 'el';
325 SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2';
326 SELECT a00,a01 FROM t1 WHERE a00 = 'gb';
327 SET DEBUG_SYNC = 'now WAIT_FOR lockwait1';
328 SET DEBUG_SYNC = 'now WAIT_FOR lockwait2';
329 SET DEBUG_SYNC = 'now SIGNAL continue';
330 a00 a01
331 el a
332 a00 a01
333 gb a
334 ANALYZE TABLE t1;
335 Table Op Msg_type Msg_text
336 test.t1 analyze status OK
337 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
338 CLUST_INDEX_SIZE
339 32
340 SET DEBUG_SYNC = 'RESET';
341 DROP TABLE t1;