Debian Codegraph @

mysql-test/suite/innodb/t/innodb_cats.test

raw
1 --source include/have_debug_sync.inc
2
3 # Verify that CATS algorithm favors transactions with higher weight, and that
4 # ties are broken by first-comes-first-served rule.
5 # We generate a queue of transactions interested in row of table t1:
6 # c1 GRANTED, c2 WAITING, c3 WAITING, c4 WAITING, c5 WAITING, c6 WAITING
7 # Where the weights are:
8 # c1 - n/a
9 # c2 - 1
10 # c3 - 1
11 # c4 - 2
12 # c5 - 2
13 # c6 - 3
14 # So the granting order should be: c6, c4, c5, c2, c3.
15 # We use auxiliary connections c7,c8,c9,c10 to increase weights of
16 # c4 (blocking c7 because of locks on t2)
17 # c5 (blocking c8 because of locks on t3)
18 # c6 (blocking c9 and c10 because of locks on t4)
19
20 # A helper table to map c1,...,c10 to their connection ids
21 CREATE TABLE t0 (
22 id INT PRIMARY KEY,
23 trx_mysql_thread_id BIGINT
24 ) ENGINE=InnoDB;
25
26 # Tables used to generate congestion.
27 # We use several tables instead of several rows in single table, to make the
28 # test independent of optimizer strategy for selects.
29
30 --let i=1
31 while ($i<=4) {
32 eval
33 CREATE TABLE t$i (
34 id INT PRIMARY KEY
35 ) ENGINE=InnoDB;
36
37 --eval INSERT INTO t$i VALUES (1)
38
39 --inc $i
40 }
41
42 # The connection which is granted the lock:
43 --connect (c1, localhost, root,,)
44 INSERT INTO t0 VALUES (1, CONNECTION_ID());
45 BEGIN;
46 SELECT * FROM t1 FOR UPDATE;
47
48 # The waiters:
49 --let i=1
50 while ($i<10) {
51 --let prev=$i
52 --inc $i
53
54 --connect (c$i, localhost, root,,)
55 --eval INSERT INTO t0 VALUES ($i, CONNECTION_ID())
56 if ($i>2) {
57 --eval SET DEBUG_SYNC = 'now WAIT_FOR will_wait_c$prev'
58 }
59 BEGIN;
60 if ($i==4) {
61 SELECT * FROM t2 FOR UPDATE;
62 }
63 if ($i==5) {
64 SELECT * FROM t3 FOR UPDATE;
65 }
66 if ($i==6) {
67 SELECT * FROM t4 FOR UPDATE;
68 }
69 --eval SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL will_wait_c$i'
70 if ($i<7) {
71 --send SELECT * FROM t1 FOR UPDATE
72 }
73 if ($i==7) {
74 --send SELECT * FROM t2 FOR UPDATE
75 }
76 if ($i==8) {
77 --send SELECT * FROM t3 FOR UPDATE
78 }
79 if ($i>=9) {
80 --send SELECT * FROM t4 FOR UPDATE
81 }
82 }
83
84 # Demonstrate that the cats weights are as expected
85 --connection default
86 SET DEBUG_SYNC = 'now WAIT_FOR will_wait_c10';
87
88 # There might be a slight delay between going to sleep and updating
89 # cats weights, so we wait for the computation result explicitly.
90 let $wait_condition=
91 SELECT COUNT(*) = 9
92 FROM t0
93 JOIN INFORMATION_SCHEMA.INNODB_TRX USING (trx_mysql_thread_id)
94 WHERE trx_schedule_weight > 0;
95 --source include/wait_condition.inc
96
97 SELECT t0.id, trx_state, trx_schedule_weight
98 FROM t0
99 JOIN INFORMATION_SCHEMA.INNODB_TRX USING (trx_mysql_thread_id)
100 ORDER BY t0.id;
101
102 # Release the lock:
103 --connection c1
104 COMMIT;
105
106 # Verify the CATS order of scheduling:
107 --connection c6
108 --reap
109 COMMIT;
110 --connection c4
111 --reap
112 COMMIT;
113 --connection c5
114 --reap
115 COMMIT;
116 --connection c2
117 --reap
118 COMMIT;
119 --connection c3
120 --reap
121 COMMIT;
122
123 # clean up helpers
124 --let i=7
125 while ($i<=10) {
126 --connection c$i
127 --reap
128 COMMIT;
129 --inc $i
130 }
131
132 --connection default
133
134 --let i=1
135 while ($i<=10) {
136 --disconnect c$i
137 --inc $i
138 }
139
140 --let i=0
141 while ($i<=4) {
142 --eval DROP TABLE t$i
143 --inc $i
144 }
145
146 --echo ################################################################
147 --echo # #
148 --echo # Bug #89829: lock_rec_has_to_wait_vats ignores locks held by #
149 --echo # transactions being rolled back #
150 --echo # #
151 --echo ################################################################
152
153 # This test case aims to expose the problem in the lock_rec_has_to_wait_cats
154 # namely, that it ignores locks held by a transaction which is a victim of
155 # a deadlock.
156 # This test case shows, that by ignoring these locks, another transaction,
157 # (named "seer") can see modifications of rows made by "victim".
158 # In particular the "victim" will UPDATE t1 SET val = 200 WHERE id =3;
159 # and "seer" will UPDATE t1 SET val = val + 10 WHERE id = 3
160 # which in a correct implementation should lead to the end value being 13,
161 # but in this faulty implementation leads to val = 210, despite "victim"
162 # being rolled back.
163 # The scenario requires one more actor, called "too_big_to_fail", which is a
164 # concurrent transaction, which causes deadlock with "victim", yet, as its
165 # name suggests, is too big to fail, so "victim" gets sacrificed instead.
166 # To resolve the deadlock, one of the locks held by victim is released,
167 # and it so happens, that it is a lock for a gap before row 3,
168 # which causes CATS implementation to consider all transactions waiting for
169 # a lock on row 3. In particular "seer" waits for X lock for row 3,
170 # and is granted this lock, despite "victim" holding another X lock for the
171 # same row. This leads to "seer" seeing the val = 200, and proceeding
172 # with its +10, and results in 210 to be permanently stored.
173 # The timeline is as follows:
174 # 1. too_big_to_fail becomes heavy, by creating a few rows
175 # 2. too_big_to_fail obtains a lock for the gap before 3
176 # 3. victim obtains a lock for the gap before 5
177 # 4. victim updates row 3, and thus obtains X lock for row 3
178 # 5. victim starts insert into the gap before 3, and waits for too_big_to_fail
179 # 6. seer starts update of row 3, but has to wait for victim
180 # 7. too_big_to_fail inserts into gap before 5, which causes deadlock
181 # 8. the deadlock is resolved by removing the waiting insert intention lock,
182 # which was enqueued by the victim for the insert into gap before row 3
183 # 9. the CATS algorithm grants lock for row 3 to seer
184 # 10. seer finishes the update, with value 210
185
186 # Prepare the table
187
188 CREATE TABLE t1 (
189 id INT PRIMARY KEY,
190 val INT NOT NULL
191 ) ENGINE=InnoDB;
192
193
194 INSERT INTO t1 (id, val) VALUES (1,1), (3,3), (5,5);
195
196 # Save the original settings, to be restored at the end of test
197 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
198
199 # Make sure that transactions will not finish prematurely
200 SET @@global.innodb_lock_wait_timeout = 100000;
201
202 # Generate a transaction which is "too big to fail"
203 --connect (too_big_to_fail, localhost, root,,)
204 --echo # too_big_to_fail:
205 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
206 BEGIN;
207 # 1. too_big_to_fail becomes heavy, by creating a few rows
208 INSERT INTO t1 (id, val)
209 VALUES (100, 100), (200, 200), (300, 300), (400, 400), (500, 500);
210 # 2. too_big_to_fail obtains a lock for the gap before 3
211 SELECT * FROM t1 WHERE id = 2 FOR UPDATE;
212
213 # Generate a transaction which will be the victim of deadlock resolution
214 --connect (victim, localhost, root,,)
215 --echo # victim:
216 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
217 BEGIN;
218 # 3. victim obtains a lock for the gap before 5
219 SELECT * FROM t1 WHERE id = 4 FOR UPDATE;
220 # 4. victim updates row 3, and thus obtains X lock for row 3
221 UPDATE t1 SET val = 200 WHERE id = 3;
222 # The victim will wait with insert into the gap before 3.
223 # It will wake up due to being a deadlock victim, and we want to slow the
224 # rollback procedure just a little bit, to expose the bug, so we add
225 # synchronization request to wait for a signal before rolling back.
226 SET DEBUG_SYNC =
227 'lock_wait_has_finished_waiting WAIT_FOR victim_can_rollback';
228 # 5. victim starts insert into the gap before 3, and waits for
229 # too_big_to_fail
230 --send INSERT INTO t1 (id, val) VALUES (2,2)
231
232 # Generate a transaction which will see changes of victim
233 --connect (seer, localhost, root,,)
234 --echo # seer:
235 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
236 BEGIN;
237 # In correct implementation seer should wait until rollback of victim
238 # completes, then perform update and thus, the end result should be
239 # 3 + 10 = 13. It for sure should not be 200 + 10 = 210, unless the victim
240 # commits. The whole purpose of this test, is to demonstrate, that it will
241 # be 210.
242 # Since the next line blocks, we need to use --send, but we also want the
243 # further lines of the test to execute only after seer is waiting for the
244 # lock, thus we add synchronization:
245 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL seer_will_wait';
246 # 6. seer starts update of row 3, but has to wait for victim
247 --send UPDATE t1 SET val = val + 10 WHERE id = 3
248
249 # Now, too big to fail, will cause deadlock, which kills victim.
250 # The reason for deadlock is that victim holds lock for gap before 5,
251 # into which too_big_to_fail tries to insert, but at the same time
252 # too_big_to_fail holds a lock for a gap before 3, into which
253 # the victim tries to insert.
254 # Since too_big_to_fail is heavier, it is victim that will be sacrificed.
255 # In particular, the wait lock for gap before 3 will be removed immediately.
256 # This in turn will cause a CATS implementation to check if there are any
257 # transactions waiting for a lock on record with id 3 to be granted, and grant
258 # them if possible.
259 # Recall, that seer waits for X lock for record with id 3, to perform UPDATE,
260 # and that victim holds X lock for the same record because of val = 200.
261 # The bug in CATS will ignore the victim's X-lock (because victim transaction
262 # was_chosen_as_deadlock_victim) and will grant the lock to seer, which will
263 # proceed.
264 --connection too_big_to_fail
265 --echo # too_big_to_fail:
266 # We have to wait for seer's UPDATE being processed at least as far as to
267 # enqueue its lock request for row 3 - otherwise (if we not wait for that)
268 # we can resolve deadlock too soon to make seer a beneficent of this
269 # resolution.
270 SET DEBUG_SYNC = 'now WAIT_FOR seer_will_wait';
271 # 7. too_big_to_fail inserts into gap before 5, which causes deadlock
272 # Even though too_big_to_fail's INSERT will not fail, we still have to use
273 # async --send, because to insert into gap before 5, this gap has to be
274 # released by victim, and this will not happen until we let it clean up
275 # other locks, be emitting the victim_can_rollback SIGNAL.
276 # We also want to be notified when too_big_to_fail starts waiting, because
277 # this means that any deadlock resolution performed in its thread, has
278 # finished, and we will be able to observe any faulty behavior (such as
279 # two X locks being granted at the same time).
280 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL too_big_to_fail_will_wait';
281 --send INSERT INTO t1 (id, val) VALUES (4, 4)
282
283 # Before proceeding further let's check if the state is already corrupted:
284 --connection default
285 --echo # default:
286 SET DEBUG_SYNC = 'now WAIT_FOR too_big_to_fail_will_wait';
287 # If there is a bug in CATS this will report two different transactions
288 # having RECORD X,REC_NOT_GAP GRANTED 3 (one for seer, one for victim).
289 # If there is no bug, then only one lock should be visible (the one of the
290 # victim).
291 let $x_locks_count = `SELECT COUNT(*)
292 FROM performance_schema.data_locks
293 WHERE object_name = 't1'
294 AND lock_type = 'RECORD'
295 AND lock_mode = 'X,REC_NOT_GAP'
296 AND lock_data = 3
297 AND lock_status = 'GRANTED'`;
298 --echo # Number of X locks granted: $x_locks_count
299 # In case the code is correct only the victim will hold the X lock.
300 # To make the rest of the test case progress, we have to let the victim
301 # perform rollback. Otherwise, seer will never get its X lock granted and
302 # the test will stall.
303 # I know this is a bit ugly that the test semantic depends on the
304 # behavior of the program
305 # being tested, but this is the best solution I could think of.
306 if($x_locks_count == 1)
307 {
308 SET DEBUG_SYNC = 'now SIGNAL victim_can_rollback';
309 }
310
311
312 # Let's go back to seer, to see the result
313 --connection seer
314 --echo # seer:
315 # 8. the deadlock is resolved by removing the waiting insert intention
316 # lock, which was enqueued by the victim for the insert into gap before
317 # row 3
318 # 9. the CATS algorithm grants lock for row 3 to seer
319 # 10. seer finishes the update, with value 210
320 --reap
321 # This should return 3 + 10 = 13, but it will return 200 + 10 = 210!
322 SELECT * FROM t1 WHERE id = 3 FOR UPDATE;
323 # The bug has been exposed, we can now allow the victim to proceed
324 SET DEBUG_SYNC = 'now SIGNAL victim_can_rollback';
325 COMMIT;
326
327 # Let's see the result on victim
328 --connection victim
329 --echo # victim:
330 --error ER_LOCK_DEADLOCK
331 --reap
332 ROLLBACK;
333
334 # Let's go back to too big to fail, and roll it back as well.
335 --connection too_big_to_fail
336 --echo # too_big_to_fail:
337 --reap
338 ROLLBACK;
339
340 # So, now we have a situation, where all transactions except for seer
341 # were rolled back. So, val should be 3 + 10 = 13, yet it is 210!
342 --connection default
343 --echo # default:
344 SELECT * FROM t1;
345
346 # Restore original state
347 --connection default
348 --disconnect too_big_to_fail
349 --disconnect victim
350 --disconnect seer
351
352 DROP TABLE t1;
353 SET DEBUG_SYNC = 'RESET';
354 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;
355
356
357 --echo #####################
358 --echo # #
359 --echo # End of Bug #89829 #
360 --echo # #
361 --echo #####################
362
363 --echo #################################################################
364 --echo # #
365 --echo # Bug #89932: RecLock::add_to_waitq calls lock_update_age on #
366 --echo # DB_DEADLOCK inflating trx->age transactions being #
367 --echo # rolled back #
368 --echo # #
369 --echo #################################################################
370
371 --echo ##############
372 --echo # #
373 --echo # Scenario 1 #
374 --echo # #
375 --echo ##############
376
377 # This test exposes the problem of double-counting trx->age in case of a
378 # deadlock. The test requires DBUG_SYNC.
379 # The timeline is as follows:
380 # 0. Create table t1 with $N records with id=0,...,$N-1
381 # 1. too_big_to_fail becomes heavy, by creating a few rows
382 # 2. now in the loop for $i=0,...,$N-1
383 # 2.1. victim obtains X lock on row $i
384 # 2.2. too_big_to_fail waits for the X lock on row $i
385 # 2.3. victim waits for too_big_to_fail because it tries to read one of rows
386 # inserted by too_big_to_fail
387 # 2.4. this causes a deadlock, and victim is chosen as a victim
388 # 2.5. too_big_to_fail->age doubles, and victim rollbacks
389
390
391 # 0. Create table t1 with $N records with id=0,...,$N-1
392
393 CREATE TABLE t1 (
394 id INT PRIMARY KEY
395 ) ENGINE=InnoDB;
396
397 # We use N = 2 which should be enough to notice that trx_schedule_weight doubles
398 # in each iteration (in case of double-counting bug) or remains constant
399 # (in case it works correctly)
400 --let $N = 2
401 --let $i = 0
402 while($i < $N) {
403 --eval INSERT INTO t1 (id) VALUES ($i)
404 --inc $i
405 }
406
407 # Save the original settings, to be restored at the end of test
408 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
409
410 # Make sure that transactions will not finish prematurely
411 SET @@global.innodb_lock_wait_timeout = 100000;
412
413 # Generate a transaction which is "too big to fail"
414 --connect (too_big_to_fail, localhost, root,,)
415 --echo # too_big_to_fail:
416 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
417 BEGIN;
418 # 1. too_big_to_fail becomes heavy, by creating a few rows
419 INSERT INTO t1 (id) VALUES (-100), (-200), (-300), (-400), (-500);
420 --let $too_big_to_fail_id = `SELECT CONNECTION_ID()`
421
422 --connection default
423 --disable_query_log
424 --eval SET @too_big_to_fail_id = $too_big_to_fail_id;
425 --enable_query_log
426
427
428 --connect (victim, localhost, root,,)
429
430 # 2. now in the loop for $i=0,...,$N-1
431 --let $i = 0
432 while($i < $N) {
433 --connection victim
434 --echo #connection victim
435 BEGIN;
436 # 2.1. victim obtains X lock on row $i
437 --eval SELECT * FROM t1 WHERE id = $i FOR UPDATE
438
439 --connection too_big_to_fail
440 --echo #connection too_big_to_fail
441 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL too_big_to_fail_waits';
442 # 2.2. too_big_to_fail waits for the X lock on row $i
443 --send_eval SELECT * FROM t1 WHERE id = $i FOR UPDATE
444
445 --connection default
446 let $wait_condition=
447 SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_TRX
448 WHERE trx_mysql_thread_id=@too_big_to_fail_id
449 AND trx_schedule_weight > 0;
450 --source include/wait_condition.inc
451
452 SELECT trx_state, trx_schedule_weight
453 FROM INFORMATION_SCHEMA.INNODB_TRX
454 WHERE trx_mysql_thread_id=@too_big_to_fail_id;
455
456 --connection victim
457 --echo #connection victim
458 SET DEBUG_SYNC = 'now WAIT_FOR too_big_to_fail_waits';
459 --error ER_LOCK_DEADLOCK
460 # 2.3. victim waits for too_big_to_fail because it tries to read one of rows
461 # inserted by too_big_to_fail
462 SELECT * FROM t1 WHERE id = -100 FOR UPDATE;
463 # 2.4. this causes a deadlock, and victim is chosen as a victim
464 # 2.5. too_big_to_fail->age doubles, and victim rollbacks
465
466 --connection too_big_to_fail
467 --echo #connection too_big_to_fail
468 --reap
469
470 --inc $i
471 }
472
473
474 # Clean up
475 --connection too_big_to_fail
476 ROLLBACK;
477 --connection default
478 SET GLOBAL DEBUG = '-d,lock_update_trx_age_check_age_limit';
479 DROP TABLE t1;
480 SET DEBUG_SYNC = 'RESET';
481 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;
482 --disconnect victim
483 --disconnect too_big_to_fail
484
485 --echo ##############
486 --echo # #
487 --echo # Scenario 2 #
488 --echo # #
489 --echo ##############
490
491 # This test exposes the problem of inflating trx->age in case of a
492 # deadlock. The test requires DBUG_SYNC.
493 # The timeline is as follows:
494 # 0. Create table t1 with two records with id=0,1
495 # 1. beneficiary obtains an S lock on row with id 0
496 # 2. now in the loop for $i=0,...,$N-1
497 # 2.1. too_big_to_fail becomes heavy, by creating a few rows
498 # 2.2. too_big_to_fail obtains an S lock on row with id 0
499 # 2.3. victim obtains X lock on row with id 1
500 # 2.4. too_big_to_fail waits for the X lock on row 1
501 # 2.5. victim waits for both beneficiary and too_big_to_fail because it tries
502 # to exclusively read row with id 0
503 # 2.6. this causes a deadlock, and victim is chosen as a victim
504 # 2.7. beneficiary->age increases, and victim rollbacks
505 # 2.8. too_big_to_fail rollbacks
506
507 # 0. Create table t1 with two records with id=0,1
508
509 CREATE TABLE t1 (
510 id INT PRIMARY KEY
511 ) ENGINE=InnoDB;
512
513 INSERT INTO t1 (id) VALUES (0),(1);
514
515 # Save the original settings, to be restored at the end of test
516 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
517
518 # Make sure that transactions will not finish prematurely
519 SET @@global.innodb_lock_wait_timeout = 100000;
520
521 # Beneficiary just sits on row 0
522 --connect (beneficiary, localhost, root,,)
523 --echo # beneficiary:
524 BEGIN;
525 # 1. beneficiary obtains an S lock on row with id 0
526 SELECT * FROM t1 WHERE id = 0 FOR SHARE;
527
528 # Generate a transaction which is "too big to fail"
529 --connect (too_big_to_fail, localhost, root,,)
530 --let $too_big_to_fail_id = `SELECT CONNECTION_ID()`
531
532 --connection default
533 --disable_query_log
534 --eval SET @too_big_to_fail_id = $too_big_to_fail_id;
535 --enable_query_log
536
537 # Generate a victim which will always be sacraficed
538 --connect (victim, localhost, root,,)
539
540
541 # 2. now in the loop for $i=0,...,$N-1
542 # We use N = 2 which should be enough to notice that trx_schedule_weight increases
543 # in each iteration (in case of deadlock handling bug) or remains constant
544 # (in case it works correctly)
545 --let $N = 2
546 --let $i = 0
547 while($i < $N) {
548 # Generate a transaction which is "too big to fail"
549 --connection too_big_to_fail
550 --echo # too_big_to_fail:
551 BEGIN;
552 # 2.1. too_big_to_fail becomes heavy, by creating a few rows
553 INSERT INTO t1 (id) VALUES (-100), (-200), (-300), (-400), (-500);
554 # 2.2. too_big_to_fail obtains an S lock on row with id 0
555 SELECT * FROM t1 WHERE id = 0 FOR SHARE;
556
557 --connection victim
558 --echo #connection victim
559 BEGIN;
560 # 2.3. victim obtains X lock on row with id 1
561 SELECT * FROM t1 WHERE id = 1 FOR UPDATE;
562
563 --connection too_big_to_fail
564 --echo #connection too_big_to_fail
565 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL too_big_to_fail_waits';
566 # 2.4. too_big_to_fail waits for the X lock on row 1
567 --send SELECT * FROM t1 WHERE id = 1 FOR UPDATE
568
569 --connection default
570 let $wait_condition=
571 SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_TRX
572 WHERE trx_mysql_thread_id=@too_big_to_fail_id
573 AND trx_schedule_weight > 0;
574 --source include/wait_condition.inc
575
576 SELECT trx_state, trx_schedule_weight
577 FROM INFORMATION_SCHEMA.INNODB_TRX
578 WHERE trx_mysql_thread_id=@too_big_to_fail_id;
579
580 --connection victim
581 --echo #connection victim
582 SET DEBUG_SYNC = 'now WAIT_FOR too_big_to_fail_waits';
583 --error ER_LOCK_DEADLOCK
584 # 2.5. victim waits for both beneficiary and too_big_to_fail because it
585 # tries to exclusively read row with id 0
586 SELECT * FROM t1 WHERE id = 0 FOR UPDATE;
587 # 2.6. this causes a deadlock, and victim is chosen as a victim
588 # 2.7. beneficiary->age increases, and victim rollbacks
589
590 --connection too_big_to_fail
591 --echo #connection too_big_to_fail
592 --reap
593 # 2.8. too_big_to_fail rollbacks
594 ROLLBACK;
595
596 --inc $i
597 }
598
599
600 # Clean up
601 --connection beneficiary
602 ROLLBACK;
603 --connection default
604 SET GLOBAL DEBUG = '-d,lock_update_trx_age_check_age_limit';
605 DROP TABLE t1;
606 SET DEBUG_SYNC = 'RESET';
607 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;
608 --disconnect beneficiary
609 --disconnect victim
610 --disconnect too_big_to_fail
611
612 --echo #####################
613 --echo # #
614 --echo # End of Bug #89932 #
615 --echo # #
616 --echo #####################
617
618 --echo #####################################################################
619 --echo # #
620 --echo # Bug #89737: Using VATS with spatial index can lead to transaction #
621 --echo # never being woken up #
622 --echo # #
623 --echo #####################################################################
624
625 # This test tries to expose a bug in the way we handle predicate locks held by
626 # a transaction which commits or rollbacks. In theory it should release such
627 # locks, and this is what happens in FCFS mode. However a bug in CATS mode,
628 # causes the LOCK_PREDICATE to be not released at all. This may cause other
629 # transaction waiting for the lock to be released to starve.
630 # The scenario of this test is:
631 # 1. selecting_thread SELECTs points from a rectangle (2,2)-(4,4) FOR UPDATE
632 # 2. inserting_thread starts INSERT of point (3,3) and must wait
633 # 3. selecting_thread COMMITs
634 # 4. inserting_thread either waits forever of succeeds with the INSERT
635
636
637 # Prepare the table
638
639 CREATE TABLE t1 (
640 id INT PRIMARY KEY,
641 p GEOMETRY NOT NULL SRID 0,
642 SPATIAL KEY p_idx(p)
643 ) ENGINE=InnoDB;
644
645 INSERT INTO t1 (id, p) VALUES
646 (1,POINT(1,1)),
647 (2,POINT(2,2)),
648 (3,POINT(3,3)),
649 (4,POINT(4,4));
650
651 # Save the original settings, to be restored at the end of test
652 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
653
654 # Make sure that transactions will not finish prematurely
655 SET @@global.innodb_lock_wait_timeout = 10;
656
657
658 # Prepare two connections
659 --connect (selecting_thread, localhost, root,,)
660 --connect (inserting_thread, localhost, root,,)
661
662
663 --echo # Using CATS
664
665 # selecting_thread selects a rectangle for update
666
667 --connection selecting_thread
668 --echo # in selecting_thread
669
670 USE test;
671 BEGIN;
672 SELECT id
673 FROM t1
674 WHERE MBRContains(
675 ST_GeomFromText('Polygon((2 2, 2 4, 4 4, 4 2, 2 2))'),
676 p
677 )
678 FOR UPDATE;
679
680 # inserting_thread tries to insert a point into the same rectangle and thus
681 # must wait
682
683 --connection inserting_thread
684 --echo # in inserting_thread
685 USE test;
686 BEGIN;
687 SET DEBUG_SYNC =
688 'lock_wait_will_wait SIGNAL inserting_thread_waits';
689
690 --send INSERT INTO t1 (id, p) VALUES (0, POINT (3, 3));
691
692 # selecting_thread commits
693
694 --connection selecting_thread
695 --echo # in selecting_thread
696 SET DEBUG_SYNC = 'now WAIT_FOR inserting_thread_waits';
697 COMMIT;
698
699 # inserting_thread should become unlocked and continue
700
701 --connection inserting_thread
702 --echo # in inserting_thread
703 # The next --reap will block forever, unless the LOCK_PREDICATE held by
704 # the selecting_thread is correctly released, and the LOCK_PREDICATE
705 # insert intetion that inserting_thread waits for is correctly granted.
706 --reap
707 ROLLBACK;
708
709
710 # Restore original state
711 --connection default
712 --disconnect selecting_thread
713 --disconnect inserting_thread
714
715 DROP TABLE t1;
716 SET DEBUG_SYNC = 'RESET';
717 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;
718
719 --echo #####################
720 --echo # #
721 --echo # End of Bug #89737 #
722 --echo # #
723 --echo #####################