--source include/have_debug_sync.inc --source include/count_sessions.inc --source ../include/prepare_show_locks.inc --connect (con1,localhost,root,,) CALL register_connection("con1"); --connect (con2,localhost,root,,) CALL register_connection("con2"); --connect (con3,localhost,root,,) CALL register_connection("con3"); --connection default CREATE TABLE t1 (id INT PRIMARY KEY); INSERT INTO t1 (id) VALUES (1); # Simplest scenario: # , # , , # , , # Expected: instead of deadlocking, the con1's request should ingore con2's --connection con1 BEGIN; SELECT * FROM t1 FOR SHARE; --connection con2 BEGIN; SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait'; --send SELECT * FROM t1 FOR UPDATE --connection con1 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait'; SELECT * FROM t1 FOR UPDATE; COMMIT; --connection con2 --reap COMMIT; # A variant of the above scenario: # , # , , # , , # Expected: a deadlock, as INSERT INTENTION should not overtake locks on gap, to not slice them --connection con1 BEGIN; SELECT * FROM t1 WHERE id=1 FOR UPDATE; --connection con2 BEGIN; SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait'; --send SELECT * FROM t1 FOR SHARE --connection con1 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait'; CALL show_locks(); INSERT INTO t1 VALUES (0); CALL show_locks(); ROLLBACK; --connection con2 --error ER_LOCK_DEADLOCK --reap COMMIT; # More complicated scenario: # , # , , # , , # , , , # , , # Expected: a deadlock, as INSERT INTENTION should not overtake locks on gap, to not slice them --connection con1 BEGIN; SELECT * FROM t1 FOR SHARE; --connection con2 BEGIN; SELECT * FROM t1 WHERE id=1 FOR SHARE; --connection default CALL show_locks(); --connection con3 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con3_will_wait'; --send SELECT * FROM t1 FOR UPDATE --connection con1 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait'; CALL show_locks(); SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con1_will_wait'; --send INSERT INTO t1 VALUES (0) --connection con2 SET DEBUG_SYNC = 'now WAIT_FOR con1_will_wait'; CALL show_locks(); COMMIT; CALL show_locks(); --connection con1 --reap ROLLBACK; CALL show_locks(); --connection con3 --error ER_LOCK_DEADLOCK --reap # More complicated scenario. It's not ideal. Ideally, we'd like con1's X REC_NOT_GAP request to be # considered blocked by con3, and then to bypass it exceptionally because of con3's being blocked by con1. # What happens in reality is that con1's request will get blocked by con2, not con3, because we search for # a blocker in the queue starting from the granted locks. And then, when con2 releases locks, both con3 and con1 # are candidates to grant the lock, and con3 can't be granted the lock because it's blocked by con1's S lock, # and con1 can be granted the X REC_NOT_GAP lock, because CATS algorithm doesn't check for conflicts with waiting # locks during release induced granting. Thus the heuristic "to bypass waiters blocked by us" doesn't really take # part in this scenario. Still, it's good to verify that it works without a deadlock. # , # , , # , , # , , , # , , , # , # done. --connection con1 BEGIN; SELECT * FROM t1 FOR SHARE; --connection con2 BEGIN; SELECT * FROM t1 WHERE id=1 FOR SHARE; --connection default CALL show_locks(); --connection con3 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con3_will_wait'; --send SELECT * FROM t1 FOR UPDATE --connection con1 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait'; SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con1_will_wait'; --send SELECT * FROM t1 WHERE id=1 FOR UPDATE --connection con2 SET DEBUG_SYNC = 'now WAIT_FOR con1_will_wait'; CALL show_locks(); COMMIT; CALL show_locks(); --connection con1 --reap COMMIT; CALL show_locks(); --connection con3 --reap COMMIT; # A secenario, where con1 has to bypass two transactions, thus testing proper caching: # # # # --connection con1 BEGIN; SELECT * FROM t1 FOR SHARE; --connection con2 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait'; --send SELECT * FROM t1 FOR UPDATE --connection con3 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait'; SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con3_will_wait'; --send SELECT * FROM t1 FOR UPDATE --connection con1 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait'; CALL show_locks(); SELECT * FROM t1 WHERE id=1 FOR UPDATE; CALL show_locks(); COMMIT; --connection con2 --reap COMMIT; --connection con3 --reap COMMIT; --connection default --disconnect con1 --disconnect con2 --disconnect con3 DROP TABLE t1; --source ../include/cleanup_show_locks.inc --source include/wait_until_count_sessions.inc