--source include/have_debug_sync.inc # Verify that CATS algorithm favors transactions with higher weight, and that # ties are broken by first-comes-first-served rule. # We generate a queue of transactions interested in row of table t1: # c1 GRANTED, c2 WAITING, c3 WAITING, c4 WAITING, c5 WAITING, c6 WAITING # Where the weights are: # c1 - n/a # c2 - 1 # c3 - 1 # c4 - 2 # c5 - 2 # c6 - 3 # So the granting order should be: c6, c4, c5, c2, c3. # We use auxiliary connections c7,c8,c9,c10 to increase weights of # c4 (blocking c7 because of locks on t2) # c5 (blocking c8 because of locks on t3) # c6 (blocking c9 and c10 because of locks on t4) # A helper table to map c1,...,c10 to their connection ids CREATE TABLE t0 ( id INT PRIMARY KEY, trx_mysql_thread_id BIGINT ) ENGINE=InnoDB; # Tables used to generate congestion. # We use several tables instead of several rows in single table, to make the # test independent of optimizer strategy for selects. --let i=1 while ($i<=4) { eval CREATE TABLE t$i ( id INT PRIMARY KEY ) ENGINE=InnoDB; --eval INSERT INTO t$i VALUES (1) --inc $i } # The connection which is granted the lock: --connect (c1, localhost, root,,) INSERT INTO t0 VALUES (1, CONNECTION_ID()); BEGIN; SELECT * FROM t1 FOR UPDATE; # The waiters: --let i=1 while ($i<10) { --let prev=$i --inc $i --connect (c$i, localhost, root,,) --eval INSERT INTO t0 VALUES ($i, CONNECTION_ID()) if ($i>2) { --eval SET DEBUG_SYNC = 'now WAIT_FOR will_wait_c$prev' } BEGIN; if ($i==4) { SELECT * FROM t2 FOR UPDATE; } if ($i==5) { SELECT * FROM t3 FOR UPDATE; } if ($i==6) { SELECT * FROM t4 FOR UPDATE; } --eval SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL will_wait_c$i' if ($i<7) { --send SELECT * FROM t1 FOR UPDATE } if ($i==7) { --send SELECT * FROM t2 FOR UPDATE } if ($i==8) { --send SELECT * FROM t3 FOR UPDATE } if ($i>=9) { --send SELECT * FROM t4 FOR UPDATE } } # Demonstrate that the cats weights are as expected --connection default SET DEBUG_SYNC = 'now WAIT_FOR will_wait_c10'; # There might be a slight delay between going to sleep and updating # cats weights, so we wait for the computation result explicitly. let $wait_condition= SELECT COUNT(*) = 9 FROM t0 JOIN INFORMATION_SCHEMA.INNODB_TRX USING (trx_mysql_thread_id) WHERE trx_schedule_weight > 0; --source include/wait_condition.inc SELECT t0.id, trx_state, trx_schedule_weight FROM t0 JOIN INFORMATION_SCHEMA.INNODB_TRX USING (trx_mysql_thread_id) ORDER BY t0.id; # Release the lock: --connection c1 COMMIT; # Verify the CATS order of scheduling: --connection c6 --reap COMMIT; --connection c4 --reap COMMIT; --connection c5 --reap COMMIT; --connection c2 --reap COMMIT; --connection c3 --reap COMMIT; # clean up helpers --let i=7 while ($i<=10) { --connection c$i --reap COMMIT; --inc $i } --connection default --let i=1 while ($i<=10) { --disconnect c$i --inc $i } --let i=0 while ($i<=4) { --eval DROP TABLE t$i --inc $i } --echo ################################################################ --echo # # --echo # Bug #89829: lock_rec_has_to_wait_vats ignores locks held by # --echo # transactions being rolled back # --echo # # --echo ################################################################ # This test case aims to expose the problem in the lock_rec_has_to_wait_cats # namely, that it ignores locks held by a transaction which is a victim of # a deadlock. # This test case shows, that by ignoring these locks, another transaction, # (named "seer") can see modifications of rows made by "victim". # In particular the "victim" will UPDATE t1 SET val = 200 WHERE id =3; # and "seer" will UPDATE t1 SET val = val + 10 WHERE id = 3 # which in a correct implementation should lead to the end value being 13, # but in this faulty implementation leads to val = 210, despite "victim" # being rolled back. # The scenario requires one more actor, called "too_big_to_fail", which is a # concurrent transaction, which causes deadlock with "victim", yet, as its # name suggests, is too big to fail, so "victim" gets sacrificed instead. # To resolve the deadlock, one of the locks held by victim is released, # and it so happens, that it is a lock for a gap before row 3, # which causes CATS implementation to consider all transactions waiting for # a lock on row 3. In particular "seer" waits for X lock for row 3, # and is granted this lock, despite "victim" holding another X lock for the # same row. This leads to "seer" seeing the val = 200, and proceeding # with its +10, and results in 210 to be permanently stored. # The timeline is as follows: # 1. too_big_to_fail becomes heavy, by creating a few rows # 2. too_big_to_fail obtains a lock for the gap before 3 # 3. victim obtains a lock for the gap before 5 # 4. victim updates row 3, and thus obtains X lock for row 3 # 5. victim starts insert into the gap before 3, and waits for too_big_to_fail # 6. seer starts update of row 3, but has to wait for victim # 7. too_big_to_fail inserts into gap before 5, which causes deadlock # 8. the deadlock is resolved by removing the waiting insert intention lock, # which was enqueued by the victim for the insert into gap before row 3 # 9. the CATS algorithm grants lock for row 3 to seer # 10. seer finishes the update, with value 210 # Prepare the table CREATE TABLE t1 ( id INT PRIMARY KEY, val INT NOT NULL ) ENGINE=InnoDB; INSERT INTO t1 (id, val) VALUES (1,1), (3,3), (5,5); # Save the original settings, to be restored at the end of test SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout; # Make sure that transactions will not finish prematurely SET @@global.innodb_lock_wait_timeout = 100000; # Generate a transaction which is "too big to fail" --connect (too_big_to_fail, localhost, root,,) --echo # too_big_to_fail: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; # 1. too_big_to_fail becomes heavy, by creating a few rows INSERT INTO t1 (id, val) VALUES (100, 100), (200, 200), (300, 300), (400, 400), (500, 500); # 2. too_big_to_fail obtains a lock for the gap before 3 SELECT * FROM t1 WHERE id = 2 FOR UPDATE; # Generate a transaction which will be the victim of deadlock resolution --connect (victim, localhost, root,,) --echo # victim: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; # 3. victim obtains a lock for the gap before 5 SELECT * FROM t1 WHERE id = 4 FOR UPDATE; # 4. victim updates row 3, and thus obtains X lock for row 3 UPDATE t1 SET val = 200 WHERE id = 3; # The victim will wait with insert into the gap before 3. # It will wake up due to being a deadlock victim, and we want to slow the # rollback procedure just a little bit, to expose the bug, so we add # synchronization request to wait for a signal before rolling back. SET DEBUG_SYNC = 'lock_wait_has_finished_waiting WAIT_FOR victim_can_rollback'; # 5. victim starts insert into the gap before 3, and waits for # too_big_to_fail --send INSERT INTO t1 (id, val) VALUES (2,2) # Generate a transaction which will see changes of victim --connect (seer, localhost, root,,) --echo # seer: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; # In correct implementation seer should wait until rollback of victim # completes, then perform update and thus, the end result should be # 3 + 10 = 13. It for sure should not be 200 + 10 = 210, unless the victim # commits. The whole purpose of this test, is to demonstrate, that it will # be 210. # Since the next line blocks, we need to use --send, but we also want the # further lines of the test to execute only after seer is waiting for the # lock, thus we add synchronization: SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL seer_will_wait'; # 6. seer starts update of row 3, but has to wait for victim --send UPDATE t1 SET val = val + 10 WHERE id = 3 # Now, too big to fail, will cause deadlock, which kills victim. # The reason for deadlock is that victim holds lock for gap before 5, # into which too_big_to_fail tries to insert, but at the same time # too_big_to_fail holds a lock for a gap before 3, into which # the victim tries to insert. # Since too_big_to_fail is heavier, it is victim that will be sacrificed. # In particular, the wait lock for gap before 3 will be removed immediately. # This in turn will cause a CATS implementation to check if there are any # transactions waiting for a lock on record with id 3 to be granted, and grant # them if possible. # Recall, that seer waits for X lock for record with id 3, to perform UPDATE, # and that victim holds X lock for the same record because of val = 200. # The bug in CATS will ignore the victim's X-lock (because victim transaction # was_chosen_as_deadlock_victim) and will grant the lock to seer, which will # proceed. --connection too_big_to_fail --echo # too_big_to_fail: # We have to wait for seer's UPDATE being processed at least as far as to # enqueue its lock request for row 3 - otherwise (if we not wait for that) # we can resolve deadlock too soon to make seer a beneficent of this # resolution. SET DEBUG_SYNC = 'now WAIT_FOR seer_will_wait'; # 7. too_big_to_fail inserts into gap before 5, which causes deadlock # Even though too_big_to_fail's INSERT will not fail, we still have to use # async --send, because to insert into gap before 5, this gap has to be # released by victim, and this will not happen until we let it clean up # other locks, be emitting the victim_can_rollback SIGNAL. # We also want to be notified when too_big_to_fail starts waiting, because # this means that any deadlock resolution performed in its thread, has # finished, and we will be able to observe any faulty behavior (such as # two X locks being granted at the same time). SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL too_big_to_fail_will_wait'; --send INSERT INTO t1 (id, val) VALUES (4, 4) # Before proceeding further let's check if the state is already corrupted: --connection default --echo # default: SET DEBUG_SYNC = 'now WAIT_FOR too_big_to_fail_will_wait'; # If there is a bug in CATS this will report two different transactions # having RECORD X,REC_NOT_GAP GRANTED 3 (one for seer, one for victim). # If there is no bug, then only one lock should be visible (the one of the # victim). let $x_locks_count = `SELECT COUNT(*) FROM performance_schema.data_locks WHERE object_name = 't1' AND lock_type = 'RECORD' AND lock_mode = 'X,REC_NOT_GAP' AND lock_data = 3 AND lock_status = 'GRANTED'`; --echo # Number of X locks granted: $x_locks_count # In case the code is correct only the victim will hold the X lock. # To make the rest of the test case progress, we have to let the victim # perform rollback. Otherwise, seer will never get its X lock granted and # the test will stall. # I know this is a bit ugly that the test semantic depends on the # behavior of the program # being tested, but this is the best solution I could think of. if($x_locks_count == 1) { SET DEBUG_SYNC = 'now SIGNAL victim_can_rollback'; } # Let's go back to seer, to see the result --connection seer --echo # seer: # 8. the deadlock is resolved by removing the waiting insert intention # lock, which was enqueued by the victim for the insert into gap before # row 3 # 9. the CATS algorithm grants lock for row 3 to seer # 10. seer finishes the update, with value 210 --reap # This should return 3 + 10 = 13, but it will return 200 + 10 = 210! SELECT * FROM t1 WHERE id = 3 FOR UPDATE; # The bug has been exposed, we can now allow the victim to proceed SET DEBUG_SYNC = 'now SIGNAL victim_can_rollback'; COMMIT; # Let's see the result on victim --connection victim --echo # victim: --error ER_LOCK_DEADLOCK --reap ROLLBACK; # Let's go back to too big to fail, and roll it back as well. --connection too_big_to_fail --echo # too_big_to_fail: --reap ROLLBACK; # So, now we have a situation, where all transactions except for seer # were rolled back. So, val should be 3 + 10 = 13, yet it is 210! --connection default --echo # default: SELECT * FROM t1; # Restore original state --connection default --disconnect too_big_to_fail --disconnect victim --disconnect seer DROP TABLE t1; SET DEBUG_SYNC = 'RESET'; SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved; --echo ##################### --echo # # --echo # End of Bug #89829 # --echo # # --echo ##################### --echo ################################################################# --echo # # --echo # Bug #89932: RecLock::add_to_waitq calls lock_update_age on # --echo # DB_DEADLOCK inflating trx->age transactions being # --echo # rolled back # --echo # # --echo ################################################################# --echo ############## --echo # # --echo # Scenario 1 # --echo # # --echo ############## # This test exposes the problem of double-counting trx->age in case of a # deadlock. The test requires DBUG_SYNC. # The timeline is as follows: # 0. Create table t1 with $N records with id=0,...,$N-1 # 1. too_big_to_fail becomes heavy, by creating a few rows # 2. now in the loop for $i=0,...,$N-1 # 2.1. victim obtains X lock on row $i # 2.2. too_big_to_fail waits for the X lock on row $i # 2.3. victim waits for too_big_to_fail because it tries to read one of rows # inserted by too_big_to_fail # 2.4. this causes a deadlock, and victim is chosen as a victim # 2.5. too_big_to_fail->age doubles, and victim rollbacks # 0. Create table t1 with $N records with id=0,...,$N-1 CREATE TABLE t1 ( id INT PRIMARY KEY ) ENGINE=InnoDB; # We use N = 2 which should be enough to notice that trx_schedule_weight doubles # in each iteration (in case of double-counting bug) or remains constant # (in case it works correctly) --let $N = 2 --let $i = 0 while($i < $N) { --eval INSERT INTO t1 (id) VALUES ($i) --inc $i } # Save the original settings, to be restored at the end of test SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout; # Make sure that transactions will not finish prematurely SET @@global.innodb_lock_wait_timeout = 100000; # Generate a transaction which is "too big to fail" --connect (too_big_to_fail, localhost, root,,) --echo # too_big_to_fail: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; # 1. too_big_to_fail becomes heavy, by creating a few rows INSERT INTO t1 (id) VALUES (-100), (-200), (-300), (-400), (-500); --let $too_big_to_fail_id = `SELECT CONNECTION_ID()` --connection default --disable_query_log --eval SET @too_big_to_fail_id = $too_big_to_fail_id; --enable_query_log --connect (victim, localhost, root,,) # 2. now in the loop for $i=0,...,$N-1 --let $i = 0 while($i < $N) { --connection victim --echo #connection victim BEGIN; # 2.1. victim obtains X lock on row $i --eval SELECT * FROM t1 WHERE id = $i FOR UPDATE --connection too_big_to_fail --echo #connection too_big_to_fail SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL too_big_to_fail_waits'; # 2.2. too_big_to_fail waits for the X lock on row $i --send_eval SELECT * FROM t1 WHERE id = $i FOR UPDATE --connection default let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_mysql_thread_id=@too_big_to_fail_id AND trx_schedule_weight > 0; --source include/wait_condition.inc SELECT trx_state, trx_schedule_weight FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_mysql_thread_id=@too_big_to_fail_id; --connection victim --echo #connection victim SET DEBUG_SYNC = 'now WAIT_FOR too_big_to_fail_waits'; --error ER_LOCK_DEADLOCK # 2.3. victim waits for too_big_to_fail because it tries to read one of rows # inserted by too_big_to_fail SELECT * FROM t1 WHERE id = -100 FOR UPDATE; # 2.4. this causes a deadlock, and victim is chosen as a victim # 2.5. too_big_to_fail->age doubles, and victim rollbacks --connection too_big_to_fail --echo #connection too_big_to_fail --reap --inc $i } # Clean up --connection too_big_to_fail ROLLBACK; --connection default SET GLOBAL DEBUG = '-d,lock_update_trx_age_check_age_limit'; DROP TABLE t1; SET DEBUG_SYNC = 'RESET'; SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved; --disconnect victim --disconnect too_big_to_fail --echo ############## --echo # # --echo # Scenario 2 # --echo # # --echo ############## # This test exposes the problem of inflating trx->age in case of a # deadlock. The test requires DBUG_SYNC. # The timeline is as follows: # 0. Create table t1 with two records with id=0,1 # 1. beneficiary obtains an S lock on row with id 0 # 2. now in the loop for $i=0,...,$N-1 # 2.1. too_big_to_fail becomes heavy, by creating a few rows # 2.2. too_big_to_fail obtains an S lock on row with id 0 # 2.3. victim obtains X lock on row with id 1 # 2.4. too_big_to_fail waits for the X lock on row 1 # 2.5. victim waits for both beneficiary and too_big_to_fail because it tries # to exclusively read row with id 0 # 2.6. this causes a deadlock, and victim is chosen as a victim # 2.7. beneficiary->age increases, and victim rollbacks # 2.8. too_big_to_fail rollbacks # 0. Create table t1 with two records with id=0,1 CREATE TABLE t1 ( id INT PRIMARY KEY ) ENGINE=InnoDB; INSERT INTO t1 (id) VALUES (0),(1); # Save the original settings, to be restored at the end of test SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout; # Make sure that transactions will not finish prematurely SET @@global.innodb_lock_wait_timeout = 100000; # Beneficiary just sits on row 0 --connect (beneficiary, localhost, root,,) --echo # beneficiary: BEGIN; # 1. beneficiary obtains an S lock on row with id 0 SELECT * FROM t1 WHERE id = 0 FOR SHARE; # Generate a transaction which is "too big to fail" --connect (too_big_to_fail, localhost, root,,) --let $too_big_to_fail_id = `SELECT CONNECTION_ID()` --connection default --disable_query_log --eval SET @too_big_to_fail_id = $too_big_to_fail_id; --enable_query_log # Generate a victim which will always be sacraficed --connect (victim, localhost, root,,) # 2. now in the loop for $i=0,...,$N-1 # We use N = 2 which should be enough to notice that trx_schedule_weight increases # in each iteration (in case of deadlock handling bug) or remains constant # (in case it works correctly) --let $N = 2 --let $i = 0 while($i < $N) { # Generate a transaction which is "too big to fail" --connection too_big_to_fail --echo # too_big_to_fail: BEGIN; # 2.1. too_big_to_fail becomes heavy, by creating a few rows INSERT INTO t1 (id) VALUES (-100), (-200), (-300), (-400), (-500); # 2.2. too_big_to_fail obtains an S lock on row with id 0 SELECT * FROM t1 WHERE id = 0 FOR SHARE; --connection victim --echo #connection victim BEGIN; # 2.3. victim obtains X lock on row with id 1 SELECT * FROM t1 WHERE id = 1 FOR UPDATE; --connection too_big_to_fail --echo #connection too_big_to_fail SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL too_big_to_fail_waits'; # 2.4. too_big_to_fail waits for the X lock on row 1 --send SELECT * FROM t1 WHERE id = 1 FOR UPDATE --connection default let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_mysql_thread_id=@too_big_to_fail_id AND trx_schedule_weight > 0; --source include/wait_condition.inc SELECT trx_state, trx_schedule_weight FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_mysql_thread_id=@too_big_to_fail_id; --connection victim --echo #connection victim SET DEBUG_SYNC = 'now WAIT_FOR too_big_to_fail_waits'; --error ER_LOCK_DEADLOCK # 2.5. victim waits for both beneficiary and too_big_to_fail because it # tries to exclusively read row with id 0 SELECT * FROM t1 WHERE id = 0 FOR UPDATE; # 2.6. this causes a deadlock, and victim is chosen as a victim # 2.7. beneficiary->age increases, and victim rollbacks --connection too_big_to_fail --echo #connection too_big_to_fail --reap # 2.8. too_big_to_fail rollbacks ROLLBACK; --inc $i } # Clean up --connection beneficiary ROLLBACK; --connection default SET GLOBAL DEBUG = '-d,lock_update_trx_age_check_age_limit'; DROP TABLE t1; SET DEBUG_SYNC = 'RESET'; SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved; --disconnect beneficiary --disconnect victim --disconnect too_big_to_fail --echo ##################### --echo # # --echo # End of Bug #89932 # --echo # # --echo ##################### --echo ##################################################################### --echo # # --echo # Bug #89737: Using VATS with spatial index can lead to transaction # --echo # never being woken up # --echo # # --echo ##################################################################### # This test tries to expose a bug in the way we handle predicate locks held by # a transaction which commits or rollbacks. In theory it should release such # locks, and this is what happens in FCFS mode. However a bug in CATS mode, # causes the LOCK_PREDICATE to be not released at all. This may cause other # transaction waiting for the lock to be released to starve. # The scenario of this test is: # 1. selecting_thread SELECTs points from a rectangle (2,2)-(4,4) FOR UPDATE # 2. inserting_thread starts INSERT of point (3,3) and must wait # 3. selecting_thread COMMITs # 4. inserting_thread either waits forever of succeeds with the INSERT # Prepare the table CREATE TABLE t1 ( id INT PRIMARY KEY, p GEOMETRY NOT NULL SRID 0, SPATIAL KEY p_idx(p) ) ENGINE=InnoDB; INSERT INTO t1 (id, p) VALUES (1,POINT(1,1)), (2,POINT(2,2)), (3,POINT(3,3)), (4,POINT(4,4)); # Save the original settings, to be restored at the end of test SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout; # Make sure that transactions will not finish prematurely SET @@global.innodb_lock_wait_timeout = 10; # Prepare two connections --connect (selecting_thread, localhost, root,,) --connect (inserting_thread, localhost, root,,) --echo # Using CATS # selecting_thread selects a rectangle for update --connection selecting_thread --echo # in selecting_thread USE test; BEGIN; SELECT id FROM t1 WHERE MBRContains( ST_GeomFromText('Polygon((2 2, 2 4, 4 4, 4 2, 2 2))'), p ) FOR UPDATE; # inserting_thread tries to insert a point into the same rectangle and thus # must wait --connection inserting_thread --echo # in inserting_thread USE test; BEGIN; SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL inserting_thread_waits'; --send INSERT INTO t1 (id, p) VALUES (0, POINT (3, 3)); # selecting_thread commits --connection selecting_thread --echo # in selecting_thread SET DEBUG_SYNC = 'now WAIT_FOR inserting_thread_waits'; COMMIT; # inserting_thread should become unlocked and continue --connection inserting_thread --echo # in inserting_thread # The next --reap will block forever, unless the LOCK_PREDICATE held by # the selecting_thread is correctly released, and the LOCK_PREDICATE # insert intetion that inserting_thread waits for is correctly granted. --reap ROLLBACK; # Restore original state --connection default --disconnect selecting_thread --disconnect inserting_thread DROP TABLE t1; SET DEBUG_SYNC = 'RESET'; SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved; --echo ##################### --echo # # --echo # End of Bug #89737 # --echo # # --echo #####################