Debian Codegraph @

mysql-test/suite/ndb/r/ndbinfo_locks_per_fragment.result

raw
1 use ndbinfo;
2 create table test.t1 (a int primary key, b int, c int, unique(b), key(c))
3 engine=ndb
4 partition by key(a) partitions 64;
5 create view frag_locks as
6 select fq_name = 'test/def/t1' as main_tab,
7 type,
8 sum(ex_req) as ex_reqs,
9 sum(ex_imm_ok) as ex_imm_oks,
10 sum(ex_wait_ok) as ex_wait_oks,
11 sum(ex_wait_fail) as ex_wait_fails,
12 sum(sh_req) as sh_reqs,
13 sum(sh_imm_ok) as sh_imm_oks,
14 sum(sh_wait_ok) as sh_wait_oks,
15 sum(sh_wait_fail) as sh_wait_fails
16 from locks_per_fragment
17 where
18 fq_name = 'test/def/t1' or
19 parent_fq_name = 'test/def/t1'
20 group by
21 main_tab, type;
22 No locks taken
23 select * from frag_locks;
24 main_tab type ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fails
25 0 Unique hash index 0 0 0 0 0 0 0 0
26 1 User table 0 0 0 0 0 0 0 0
27 create table baseline as select * from frag_locks;
28 create table current like baseline;
29 create view baseline_diff as
30 select c.ex_reqs - b.ex_reqs as ex_reqs,
31 c.ex_imm_oks - b.ex_imm_oks as ex_imm_oks,
32 c.ex_wait_oks - b.ex_wait_oks as ex_wait_oks,
33 c.ex_wait_fails - b.ex_wait_fails as ex_wait_fails,
34 c.sh_reqs - b.sh_reqs as sh_reqs,
35 c.sh_imm_oks - b.sh_imm_oks as sh_imm_oks,
36 c.sh_wait_oks - b.sh_wait_oks as sh_wait_oks,
37 c.sh_wait_fails - b.sh_wait_fails as sh_wait_fail,
38 c.type,
39 c.main_tab
40 from baseline b join current c on
41 b.type = c.type and
42 b.main_tab = c.main_tab
43 order by main_tab desc;
44 Start testing
45 Insert some data
46 insert into test.t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5), (6,6,6), (7,7,7), (8,8,8);
47 Expect :
48 1. NoOfReplicas(2) * NumRows * Exclusive row locks immediately claimed on user table
49 2. NoOfReplicas(2) * NumRows * Exclusive row locks immediately claimed on unique index table
50 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
51 16 16 0 0 0 0 0 0 User table 1
52 16 16 0 0 0 0 0 0 Unique hash index 0
53 Read committed by PK
54 select * from test.t1 where a=7;
55 a b c
56 7 7 7
57 Expect :
58 1. No increments
59 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
60 0 0 0 0 0 0 0 0 User table 1
61 0 0 0 0 0 0 0 0 Unique hash index 0
62 ****************************************************
63 Read shared by PK
64 select * from test.t1 where a=7 lock in share mode;
65 a b c
66 7 7 7
67 Expect :
68 1. 1 sh lock on TAB
69 2. No effect on UI
70 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
71 0 0 0 0 1 1 0 0 User table 1
72 0 0 0 0 0 0 0 0 Unique hash index 0
73 ****************************************************
74 Read exclusive by PK
75 select * from test.t1 where a=7 for update;
76 a b c
77 7 7 7
78 Expect :
79 1. 1 ex lock on TAB
80 2. No effect on UI
81 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
82 1 1 0 0 0 0 0 0 User table 1
83 0 0 0 0 0 0 0 0 Unique hash index 0
84 ****************************************************
85 Update by PK
86 update test.t1 set c=50 where a=5;
87 Expect :
88 1. 2 * 2 ex locks on TAB
89 2. No effect on UI
90 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
91 2 2 0 0 0 0 0 0 User table 1
92 0 0 0 0 0 0 0 0 Unique hash index 0
93 ****************************************************
94 Update by PK, UK affected
95 update test.t1 set b=50 where a=5;
96 Expect :
97 1. 2 * 1 ex on TAB
98 Extra tab read here?
99 2. 2 * 2 * ex on UI table (Delete old row, Insert new)
100 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
101 3 3 0 0 0 0 0 0 User table 1
102 4 4 0 0 0 0 0 0 Unique hash index 0
103 ****************************************************
104 Delete by PK
105 delete from test.t1 where a=5;
106 Expect :
107 1. 2 * 1 * ex on TAB
108 2. 2 * 1 * ex on UI
109 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
110 2 2 0 0 0 0 0 0 User table 1
111 2 2 0 0 0 0 0 0 Unique hash index 0
112 ****************************************************
113 Read committed by UK
114 select * from test.t1 where b=3;
115 a b c
116 3 3 3
117 Expect:
118 1. 1 * SH on TAB (upgrade)
119 2. 1 * SH on UI (upgrade)
120 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
121 0 0 0 0 1 1 0 0 User table 1
122 0 0 0 0 1 1 0 0 Unique hash index 0
123 ****************************************************
124 Read shared by UK
125 select * from test.t1 where b=3 lock in share mode;
126 a b c
127 3 3 3
128 Expect:
129 1. 1 * SH on TAB
130 2. 1 * SH on TAB
131 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
132 0 0 0 0 1 1 0 0 User table 1
133 0 0 0 0 1 1 0 0 Unique hash index 0
134 ****************************************************
135 Read exclusive by UK
136 select * from test.t1 where b=3 for update;
137 a b c
138 3 3 3
139 Expect:
140 1. 1 * EX on TAB
141 2. 1 * EX on UI
142 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
143 1 1 0 0 0 0 0 0 User table 1
144 1 1 0 0 0 0 0 0 Unique hash index 0
145 ****************************************************
146 Update by UK, not affecting UK value
147 update test.t1 set c=30 where b=3;
148 Expect:
149 1. 2 * 1 EX on TAB
150 2. 1 * EX on UI
151 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
152 2 2 0 0 0 0 0 0 User table 1
153 1 1 0 0 0 0 0 0 Unique hash index 0
154 ****************************************************
155 Update by UK, affecting UK value
156 update test.t1 set b=30 where b=3;
157 Expect:
158 1. 1 + 2 * 1 EX on TAB
159 2. 1 + 2 * 2 EX on UI
160 Due to : Ex read by UK -> Ex read of PK ->
161 Update T by PK (*NR) ->
162 Delete + Insert of UI by PK (*NR)
163 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
164 3 3 0 0 0 0 0 0 User table 1
165 5 5 0 0 0 0 0 0 Unique hash index 0
166 ****************************************************
167 Delete by UK
168 delete from test.t1 where b=30;
169 Expect:
170 1. 1 + 2 * 1 EX on TAB
171 2. 1 + 2 * 1 EX on UI
172 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
173 2 2 0 0 0 0 0 0 User table 1
174 3 3 0 0 0 0 0 0 Unique hash index 0
175 ****************************************************
176 OI scan (Primary) with no locks
177 select * from test.t1 order by a;
178 a b c
179 1 1 1
180 2 2 2
181 4 4 4
182 6 6 6
183 7 7 7
184 8 8 8
185 Expect : No locking
186 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
187 0 0 0 0 0 0 0 0 User table 1
188 0 0 0 0 0 0 0 0 Unique hash index 0
189 ****************************************************
190 OI scan (Primary) with shared lock, held on no rows
191 select * from test.t1 where a%1234 = 1233 lock in share mode;
192 a b c
193 Expect : Lock taken on each row, but not held
194 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
195 0 0 0 0 6 6 0 0 User table 1
196 0 0 0 0 0 0 0 0 Unique hash index 0
197 ****************************************************
198 OI scan (Primary) with shared lock, held on 1 row
199 select * from test.t1 where a%1234 = 8 lock in share mode;
200 a b c
201 8 8 8
202 Expect : Lock taken on each row, one held via takeover
203 x+1
204 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
205 0 0 0 0 7 7 0 0 User table 1
206 0 0 0 0 0 0 0 0 Unique hash index 0
207 ****************************************************
208 OI scan (Primary) with shared lock, held on all rows
209 select * from test.t1 where a%1234 > 0 order by a lock in share mode;
210 a b c
211 1 1 1
212 2 2 2
213 4 4 4
214 6 6 6
215 7 7 7
216 8 8 8
217 Expect : Lock taken on each row, all held via takeover
218 2x
219 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
220 0 0 0 0 12 12 0 0 User table 1
221 0 0 0 0 0 0 0 0 Unique hash index 0
222 ****************************************************
223 OI scan (Primary) with exclusive lock, held on no rows
224 select * from test.t1 where a%1234 = 1233 for update;
225 a b c
226 Expect : Lock taken on each row, but not held
227 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
228 6 6 0 0 0 0 0 0 User table 1
229 0 0 0 0 0 0 0 0 Unique hash index 0
230 ****************************************************
231 OI scan (Primary) with exclusive lock, held on 1 row
232 select * from test.t1 where a%1234 = 8 for update;
233 a b c
234 8 8 8
235 Expect : Lock taken on each row, one held via takeover
236 x+1
237 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
238 7 7 0 0 0 0 0 0 User table 1
239 0 0 0 0 0 0 0 0 Unique hash index 0
240 ****************************************************
241 OI scan (Primary) with exclusive lock, held on all rows
242 select * from test.t1 where a%1234 > 0 order by a for update;
243 a b c
244 1 1 1
245 2 2 2
246 4 4 4
247 6 6 6
248 7 7 7
249 8 8 8
250 Expect : Lock taken on each row, all held via takeover
251 2x
252 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
253 12 12 0 0 0 0 0 0 User table 1
254 0 0 0 0 0 0 0 0 Unique hash index 0
255 ****************************************************
256 Multiple locks from a single transaction on one row
257 begin;
258 select * from test.t1 where a=1 lock in share mode;
259 a b c
260 1 1 1
261 select * from test.t1 where a=1 lock in share mode;
262 a b c
263 1 1 1
264 select * from test.t1 where a=1 lock in share mode;
265 a b c
266 1 1 1
267 Upgrade
268 select * from test.t1 where a=1 for update;
269 a b c
270 1 1 1
271 select * from test.t1 where a=1 for update;
272 a b c
273 1 1 1
274 select * from test.t1 where a=1 for update;
275 a b c
276 1 1 1
277 commit;
278 Expect : 3 * Ex lock + 3 * Sh lock
279 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
280 3 3 0 0 3 3 0 0 User table 1
281 0 0 0 0 0 0 0 0 Unique hash index 0
282 ****************************************************
283 ****************
284 Show lock Delays
285 ****************
286 SH <- EX PK
287 Take and hold shared lock on a row
288 begin;
289 select * from test.t1 where a=1 lock in share mode;
290 a b c
291 1 1 1
292 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
293 0 0 0 0 1 1 0 0 User table 1
294 0 0 0 0 0 0 0 0 Unique hash index 0
295 Attempt to get an exclusive lock from a separate
296 transaction, PK read
297 select * from test.t1 where a=1 for update;;
298 Give request a chance to be registered and wait
299 Rollback lock holder, allowing con2 to get lock
300 rollback;
301 a b c
302 1 1 1
303 Expect :
304 Exclusive lock successfully claimed after some delay
305 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
306 1 0 1 0 0 0 0 0 User table 1
307 0 0 0 0 0 0 0 0 Unique hash index 0
308 ****************************************************
309 SH <- EX SCAN
310 Take and hold shared lock on a row
311 begin;
312 select * from test.t1 where a=1 lock in share mode;
313 a b c
314 1 1 1
315 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
316 0 0 0 0 1 1 0 0 User table 1
317 0 0 0 0 0 0 0 0 Unique hash index 0
318 Attempt to get an exclusive lock from a separate
319 transaction, OI scan
320 select * from test.t1 where a % 1245 = 1 for update;;
321 Give request a chance to be registered and wait
322 Rollback lock holder, allowing con2 to get lock
323 rollback;
324 a b c
325 1 1 1
326 Expect :
327 x - 1 EX locks taken immediately
328 1 EX lock taken after wait
329 1 EX lock taken immediately (Takeover)
330 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
331 7 6 1 0 0 0 0 0 User table 1
332 0 0 0 0 0 0 0 0 Unique hash index 0
333 ****************************************************
334 EX <- SH UI
335 Take and hold exclusive lock on a row
336 begin;
337 select * from test.t1 where a=1 for update;
338 a b c
339 1 1 1
340 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
341 1 1 0 0 0 0 0 0 User table 1
342 0 0 0 0 0 0 0 0 Unique hash index 0
343 Attempt to get a shared lock from a separate
344 transaction, UK read
345 select * from test.t1 where b=1 lock in share mode;;
346 Give request a chance to be registered and wait
347 Rollback lock holder, allowing con2 to get lock
348 rollback;
349 a b c
350 1 1 1
351 Expect :
352 Shared lock immediately claimed on UI
353 Shared lock successfully claimed on TAB after some delay
354 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
355 0 0 0 0 1 0 1 0 User table 1
356 0 0 0 0 1 1 0 0 Unique hash index 0
357 ****************************************************
358 SH <- EX UI
359 Take and hold shared lock on a row
360 begin;
361 select * from test.t1 where a=1 lock in share mode;
362 a b c
363 1 1 1
364 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
365 0 0 0 0 1 1 0 0 User table 1
366 0 0 0 0 0 0 0 0 Unique hash index 0
367 Attempt to get an exclusive lock from a separate
368 transaction, UK read
369 select * from test.t1 where b = 1 for update;;
370 Give request a chance to be registered and wait
371 Rollback lock holder, allowing con2 to get lock
372 rollback;
373 a b c
374 1 1 1
375 Expect :
376 Ex lock immediately claimed on UI
377 Ex lock successfully claimed on TAB after some delay
378 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
379 1 0 1 0 0 0 0 0 User table 1
380 1 1 0 0 0 0 0 0 Unique hash index 0
381 ****************************************************
382 EX <- SH PK
383 Take and hold exclusive lock on a row
384 begin;
385 select * from test.t1 where a=1 for update;
386 a b c
387 1 1 1
388 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
389 1 1 0 0 0 0 0 0 User table 1
390 0 0 0 0 0 0 0 0 Unique hash index 0
391 Attempt to get a shared lock from a separate
392 transaction, PK read
393 select * from test.t1 where a=1 lock in share mode;;
394 Give request a chance to be registered and wait
395 Rollback lock holder, allowing con2 to get lock
396 rollback;
397 a b c
398 1 1 1
399 Expect :
400 Shared lock successfully claimed after some delay
401 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
402 0 0 0 0 1 0 1 0 User table 1
403 0 0 0 0 0 0 0 0 Unique hash index 0
404 ****************************************************
405 EX <- SH SCAN
406 Take and hold exclusive lock on a row
407 begin;
408 select * from test.t1 where a=1 for update;
409 a b c
410 1 1 1
411 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
412 1 1 0 0 0 0 0 0 User table 1
413 0 0 0 0 0 0 0 0 Unique hash index 0
414 Attempt to get a shared lock from a separate
415 transaction, OI scan
416 select * from test.t1 where a % 1245 = 1 lock in share mode;;
417 Give request a chance to be registered and wait
418 Rollback lock holder, allowing con2 to get lock
419 rollback;
420 a b c
421 1 1 1
422 Expect :
423 x - 1 SH locks taken immediately
424 1 SH lock taken after wait
425 1 SH lock taken immediately (Takeover)
426 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
427 0 0 0 0 7 6 1 0 User table 1
428 0 0 0 0 0 0 0 0 Unique hash index 0
429 ****************************************************
430 *******************
431 Lock claim failures
432 *******************
433 Take and hold shared lock on a row
434 begin;
435 select * from test.t1 where a=1 lock in share mode;
436 a b c
437 1 1 1
438 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
439 0 0 0 0 1 1 0 0 User table 1
440 0 0 0 0 0 0 0 0 Unique hash index 0
441 SH <- EX PK
442 Attempt to get an exclusive lock from a separate
443 transaction, PK read
444 select * from test.t1 where a=1 for update;
445 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
446 Expect :
447 Exclusive lock claim failed after some delay
448 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
449 1 0 0 1 0 0 0 0 User table 1
450 0 0 0 0 0 0 0 0 Unique hash index 0
451 ****************************************************
452 SH <- EX UK
453 Attempt to get an exclusive lock from a separate
454 transaction, UK read
455 select * from test.t1 where b=1 for update;
456 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
457 Expect :
458 EX immediate on unique key
459 EX failed on TAB
460 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
461 1 0 0 1 0 0 0 0 User table 1
462 1 1 0 0 0 0 0 0 Unique hash index 0
463 ****************************************************
464 SH <- EX OI Scan
465 Attempt to get an exclusive lock from a separate
466 transaction, OI scan
467 select * from test.t1 where a % 1234 =1 for update;
468 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
469 Expect :
470 x -1 EX immediate on TAB
471 1 EX failed on TAB
472 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
473 6 5 0 1 0 0 0 0 User table 1
474 0 0 0 0 0 0 0 0 Unique hash index 0
475 ****************************************************
476 Escalate 'holding' lock to EX
477 select * from test.t1 where a=1 for update;
478 a b c
479 1 1 1
480 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
481 1 1 0 0 0 0 0 0 User table 1
482 0 0 0 0 0 0 0 0 Unique hash index 0
483 EX <- SH PK
484 Attempt to get a shared lock from a separate
485 transaction, PK read
486 select * from test.t1 where a=1 lock in share mode;
487 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
488 Expect :
489 Shared lock claim failed after some delay
490 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
491 0 0 0 0 1 0 0 1 User table 1
492 0 0 0 0 0 0 0 0 Unique hash index 0
493 ****************************************************
494 EX <- SH UK
495 Attempt to get a shared lock from a separate
496 transaction, UK read
497 select * from test.t1 where b=1 lock in share mode;
498 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
499 Expect :
500 SH immediate on unique key
501 SH failed on TAB
502 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
503 0 0 0 0 1 0 0 1 User table 1
504 0 0 0 0 1 1 0 0 Unique hash index 0
505 ****************************************************
506 EX <- SH OI Scan
507 Attempt to get a shared lock from a separate
508 transaction, OI scan
509 select * from test.t1 where a % 1234 =1 lock in share mode;
510 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
511 Expect :
512 x -1 SH immediate on TAB
513 1 SH failed on TAB
514 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
515 0 0 0 0 6 5 0 1 User table 1
516 0 0 0 0 0 0 0 0 Unique hash index 0
517 ****************************************************
518 Escalate 'holding' lock to EX on UI too
519 select * from test.t1 where b=1 for update;
520 a b c
521 1 1 1
522 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
523 1 1 0 0 0 0 0 0 User table 1
524 1 1 0 0 0 0 0 0 Unique hash index 0
525 EX UK <- SH UK
526 Attempt to get a shared lock from a separate
527 transaction, UK read
528 select * from test.t1 where b=1 lock in share mode;
529 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
530 Expect :
531 SH failed on UK
532 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
533 0 0 0 0 0 0 0 0 User table 1
534 0 0 0 0 1 0 0 1 Unique hash index 0
535 ****************************************************
536 EX UK <- EX UK
537 Attempt to get an exclusive lock from a separate
538 transaction, UK read
539 select * from test.t1 where b=1 for update;
540 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
541 Expect :
542 EX failed on UK
543 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
544 0 0 0 0 0 0 0 0 User table 1
545 1 0 0 1 0 0 0 0 Unique hash index 0
546 ****************************************************
547 rollback;
548 *******************
549 Row existence cases
550 *******************
551 Show table content
552 select * from test.t1 order by a;
553 a b c
554 1 1 1
555 2 2 2
556 4 4 4
557 6 6 6
558 7 7 7
559 8 8 8
560 SH PK read of non existing row
561 select * from test.t1 where a=1234 lock in share mode;
562 a b c
563 Expect : Nothing
564 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
565 0 0 0 0 0 0 0 0 User table 1
566 0 0 0 0 0 0 0 0 Unique hash index 0
567 ****************************************************
568 EX PK read of non existing row
569 select * from test.t1 where a=1234 for update;
570 a b c
571 Expect : Nothing
572 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
573 0 0 0 0 0 0 0 0 User table 1
574 0 0 0 0 0 0 0 0 Unique hash index 0
575 ****************************************************
576 Update of non existing row
577 update test.t1 set c=1234 where a=1234;
578 Expect : Nothing
579 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
580 0 0 0 0 0 0 0 0 User table 1
581 0 0 0 0 0 0 0 0 Unique hash index 0
582 ****************************************************
583 Delete of non existing row
584 delete from test.t1 where a=1234;
585 Expect : Nothing
586 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
587 0 0 0 0 0 0 0 0 User table 1
588 0 0 0 0 0 0 0 0 Unique hash index 0
589 ****************************************************
590 Insert of existing row
591 insert into test.t1 values (1,1,1);
592 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
593 Expect : Nothing
594 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
595 0 0 0 0 0 0 0 0 User table 1
596 0 0 0 0 0 0 0 0 Unique hash index 0
597 ****************************************************
598 *************
599 Special cases
600 *************
601 Existing row is deleted, other transaction waiting
602 for lock...
603 Prepare a delete on a row
604 begin;
605 delete from test.t1 where a=1;
606 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
607 2 2 0 0 0 0 0 0 User table 1
608 2 2 0 0 0 0 0 0 Unique hash index 0
609 Attempt to read the row by PK
610 select * from test.t1 where a =1 lock in share mode;;
611 Give request a chance to be registered and wait
612 Commit the delete
613 commit;
614 a b c
615 Expect 1 SH lock on TAB, after delay
616 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
617 0 0 0 0 1 0 1 0 User table 1
618 0 0 0 0 0 0 0 0 Unique hash index 0
619 ****************************************************
620 insert into test.t1 values (1,1,1);
621 Prepare a delete on a row
622 begin;
623 delete from test.t1 where a=1;
624 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
625 4 4 0 0 0 0 0 0 User table 1
626 4 4 0 0 0 0 0 0 Unique hash index 0
627 Attempt to read the row by UK
628 select * from test.t1 where b=1 lock in share mode;;
629 Give request a chance to be registered and wait
630 Commit the delete
631 commit;
632 a b c
633 Expect 1 SH lock on Unique, after delay
634 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
635 0 0 0 0 0 0 0 0 User table 1
636 0 0 0 0 1 0 1 0 Unique hash index 0
637 ****************************************************
638 insert into test.t1 values (1,1,1);
639 Prepare a delete on a row
640 begin;
641 delete from test.t1 where a=1;
642 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
643 4 4 0 0 0 0 0 0 User table 1
644 4 4 0 0 0 0 0 0 Unique hash index 0
645 Attempt to read the row by OI scan
646 select * from test.t1 where a % 1234 = 1 lock in share mode;;
647 Give request a chance to be registered and wait
648 Commit the delete
649 commit;
650 a b c
651 Expect :
652 x - 1 SH locks immediately claimed
653 1 SH lock claimed after wait
654 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
655 0 0 0 0 6 5 1 0 User table 1
656 0 0 0 0 0 0 0 0 Unique hash index 0
657 ****************************************************
658 insert into test.t1 values (1,1,1);
659 Prepare a delete on a row
660 begin;
661 delete from test.t1 where a=1;
662 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
663 4 4 0 0 0 0 0 0 User table 1
664 4 4 0 0 0 0 0 0 Unique hash index 0
665 Attempt to insert a similar row
666 insert into test.t1 values (1,1,1);;
667 Give request a chance to be registered and wait
668 Commit the delete
669 commit;
670 Expect same as row never existed :
671 1 EX locks on TAB ok after wait (Insert, primary)
672 1 EX lock on TAB ok immediate (replica)
673 or wait @ replica
674 2 EX locks on UI immediate (Insert)
675 delete from current;
676 insert into current select * from frag_locks;
677 expect 2 ex_reqs, either immediate or wait
678 select ex_reqs,
679 (ex_imm_oks + ex_wait_oks),
680 ex_wait_fails,
681 sh_reqs, sh_imm_oks, sh_wait_oks, sh_wait_fail,
682 main_tab, type from baseline_diff order by main_tab desc;
683 ex_reqs (ex_imm_oks + ex_wait_oks) ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail main_tab type
684 2 2 0 0 0 0 0 1 User table
685 2 2 0 0 0 0 0 0 Unique hash index
686 delete from baseline;
687 insert into baseline select * from current;
688 ****************************************************
689 Prepare a delete on a row
690 begin;
691 delete from test.t1 where a=1;
692 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
693 2 2 0 0 0 0 0 0 User table 1
694 2 2 0 0 0 0 0 0 Unique hash index 0
695 Attempt to delete the row from another transaction
696 delete from test.t1 where a=1;;
697 Give request a chance to be registered and wait
698 Commit the first delete
699 commit;
700 Expect :
701 1 EX lock wait ok
702 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
703 1 0 1 0 0 0 0 0 User table 1
704 0 0 0 0 0 0 0 0 Unique hash index 0
705 ****************************************************
706 Prepare to insert a new row
707 begin;
708 insert into test.t1 values (27, 27, 27);
709 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
710 2 2 0 0 0 0 0 0 User table 1
711 2 2 0 0 0 0 0 0 Unique hash index 0
712 Attempt to read the row from another transaction
713 (PK SH)
714 select * from test.t1 where a=27 lock in share mode;;
715 Give request a chance to be registered and wait
716 Abort the insert
717 rollback;
718 a b c
719 Expect :
720 1 SH lock wait ok
721 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
722 0 0 0 0 1 0 1 0 User table 1
723 0 0 0 0 0 0 0 0 Unique hash index 0
724 ****************************************************
725 Prepare to insert a new row
726 begin;
727 insert into test.t1 values (27, 27, 27);
728 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
729 2 2 0 0 0 0 0 0 User table 1
730 2 2 0 0 0 0 0 0 Unique hash index 0
731 Attempt to insert the same row from another transaction
732 (PK SH)
733 insert into test.t1 values (27, 27, 27);;
734 Give request a chance to be registered and wait
735 Abort the first insert
736 rollback;
737 Expect :
738 1 EX lock wait ok on TAB (prim)
739 1 EX lock immed on TAB (backup)
740 2 EX lock immed ok on UI
741 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
742 2 1 1 0 0 0 0 0 User table 1
743 2 2 0 0 0 0 0 0 Unique hash index 0
744 ****************************************************
745 ****************************************************
746 Show elapsed time added to totals
747 ****************************************************
748 Timeout case
749 begin;
750 select * from test.t1 where a=2 lock in share mode;
751 a b c
752 2 2 2
753 begin;
754 select * from test.t1 where a=2 for update;
755 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
756 rollback;
757 rollback;
758 Show no extra 'ok' time, and some extra 'fail' time
759 Expect 0 ok_millis, > 0 fail_millis
760 ok_millis fail_millis
761 0 1
762 ****************************************************
763 Ok wait case
764 ****************************************************
765 Expect > 0 ok_millis, 0 fail_millis
766 ok_millis fail_millis
767 1 0
768 drop table test.t1;
769 delete from current;
770 delete from baseline;
771 *****
772 Blobs
773 *****
774 create table test.t1 (a int primary key, b text, c int) engine=ndb;
775 insert into baseline select * from frag_locks;
776 insert into test.t1 values (1,repeat('BJC', 300), 1);
777 Show insert lock footprint across main and
778 parts tables :
779 - Blob uses extra ops on main table row
780 to set length etc...
781 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
782 4 4 0 0 0 0 0 0 User table 1
783 2 2 0 0 0 0 0 0 User table 0
784 ****************************************************
785 Show Blob read by PK Committed read
786 select a, length(b) from test.t1 where a=1;
787 a length(b)
788 1 900
789 Expect SH on main table (Upgrade)
790 + SH on parts table (SimpleRead)
791 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
792 0 0 0 0 1 1 0 0 User table 1
793 0 0 0 0 1 1 0 0 User table 0
794 ****************************************************
795 Show Blob read by PK Shared
796 select a, length(b) from test.t1 where a=1 lock in share mode;
797 a length(b)
798 1 900
799 Expect SH on main table
800 + SH on parts table (SimpleRead)
801 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
802 0 0 0 0 1 1 0 0 User table 1
803 0 0 0 0 1 1 0 0 User table 0
804 ****************************************************
805 Show Blob read by PK Exclusive
806 select a, length(b) from test.t1 where a=1 for update;
807 a length(b)
808 1 900
809 Expect EX on main table
810 + SH on parts table (SimpleRead)
811 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
812 1 1 0 0 0 0 0 0 User table 1
813 0 0 0 0 1 1 0 0 User table 0
814 ****************************************************
815 Show Blob read by scan Committed read, no rows returned
816 select a, length(b) from test.t1 where c != 1;
817 a length(b)
818 Expect SH on main table
819 + Nothing on parts table
820 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
821 0 0 0 0 1 1 0 0 User table 1
822 0 0 0 0 0 0 0 0 User table 0
823 ****************************************************
824 Show Blob read by scan Committed read, 1 row returned
825 select a, length(b) from test.t1 where c % 1234 = 1;
826 a length(b)
827 1 900
828 Expect SH on main table
829 + SH on parts table (SimpleRead)
830 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
831 0 0 0 0 1 1 0 0 User table 1
832 0 0 0 0 1 1 0 0 User table 0
833 ****************************************************
834 Show Blob read by scan shared, 1 row returned
835 select a, length(b) from test.t1 where c % 1234 = 1 lock in share mode;
836 a length(b)
837 1 900
838 Expect 2* SH on main table
839 + SH on parts table (SimpleRead)
840 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
841 0 0 0 0 2 2 0 0 User table 1
842 0 0 0 0 1 1 0 0 User table 0
843 ****************************************************
844 Show Blob read by scan exlusive, 1 row returned
845 select a, length(b) from test.t1 where c % 1234 = 1 for update;
846 a length(b)
847 1 900
848 Expect 2* EX on main table
849 + SH on parts table (SimpleRead)
850 ex_reqs ex_imm_oks ex_wait_oks ex_wait_fails sh_reqs sh_imm_oks sh_wait_oks sh_wait_fail type main_tab
851 2 2 0 0 0 0 0 0 User table 1
852 0 0 0 0 1 1 0 0 User table 0
853 ****************************************************
854 drop view baseline_diff;
855 drop table current;
856 drop table baseline;
857 drop view frag_locks;
858 drop table test.t1;