# # wl#9383 INNODB: ADD AN OPTION TO TURN OFF/ON DEADLOCK CHECKER # --source include/have_debug.inc --source include/have_debug_sync.inc --source include/count_sessions.inc SET GLOBAL innodb_deadlock_detect=OFF; SET GLOBAL innodb_lock_wait_timeout=2; connect (con1,localhost,root,,); connection default; CREATE TABLE t1( id INT, PRIMARY KEY(id) ) ENGINE=InnoDB; INSERT INTO t1 VALUES(1), (2), (3); BEGIN; SELECT * FROM t1 WHERE id = 1 FOR UPDATE; connection con1; BEGIN; SELECT * FROM t1 WHERE id = 2 FOR UPDATE; --send SELECT * FROM t1 WHERE id = 1 FOR UPDATE; connection default; --send SELECT * FROM t1 WHERE id = 2 FOR UPDATE; connection con1; --error ER_LOCK_WAIT_TIMEOUT --reap; ROLLBACK; connection default; --reap; ROLLBACK; DROP TABLE t1; disconnect con1; --source include/wait_until_count_sessions.inc SET GLOBAL innodb_lock_wait_timeout=default; SET GLOBAL innodb_deadlock_detect=default; --echo # --echo #Bug #23036096 INNODB LAST DETECTED DEADLOCK NOT SHOWING FULL QUERY --echo # #This test will check whether the detected deadlock shows full query or not --source include/count_sessions.inc SET GLOBAL innodb_deadlock_detect=ON; SET GLOBAL innodb_lock_wait_timeout=10; connect(con1, localhost, root,,); connect(con2, localhost, root,,); CREATE TABLE bug( id INT PRIMARY KEY, ColumnAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA INT, ColumnBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB INT, ColumnCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC INT, ColumnDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD INT, ColumnEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE INT, ColumnFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF INT, ColumnGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG INT, ColumnHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH INT, ColumnIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII INT, ColumnJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ INT, ColumnKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKK INT, ColumnMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM INT, ColumnNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN INT, ColumnOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO INT, ColumnPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP INT, ColumnQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ INT, ColumnRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRR INT, ColumnSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS INT, ColumnTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT INT, ColumnUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU INT) engine = innodb; INSERT INTO bug(id) VALUES(0), (1); --connection con1 BEGIN; SELECT * FROM bug WHERE id = 0 FOR UPDATE; --connection con2 BEGIN; SELECT * FROM bug WHERE id = 1 FOR UPDATE; --connection con1 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con1_will_wait'; --send SELECT * FROM bug WHERE id = 1 AND ColumnAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA = 0 AND ColumnBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB = 0 AND ColumnCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC = 0 AND ColumnDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD = 0 AND ColumnEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE = 0 AND ColumnFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF = 0 AND ColumnGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG = 0 AND ColumnHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH = 0 AND ColumnIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII = 0 AND ColumnJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ = 0 AND ColumnKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKK = 0 AND ColumnMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM = 0 AND ColumnNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN = 0 AND ColumnOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO = 0 AND ColumnPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP = 0 AND ColumnQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ = 0 AND ColumnRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRR = 0 AND ColumnSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS = 0 AND ColumnTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT = 0 AND ColumnUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU = 0 FOR UPDATE; --connection con2 SET DEBUG_SYNC = 'now WAIT_FOR con1_will_wait'; --error ER_LOCK_DEADLOCK SELECT * FROM bug WHERE id = 0 AND ColumnAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA = 0 AND ColumnBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB = 0 AND ColumnCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC = 0 AND ColumnDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD = 0 AND ColumnEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE = 0 AND ColumnFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF = 0 AND ColumnGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG = 0 AND ColumnHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH = 0 AND ColumnIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII = 0 AND ColumnJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ = 0 AND ColumnKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKK = 0 AND ColumnMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM = 0 AND ColumnNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN = 0 AND ColumnOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO = 0 AND ColumnPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP = 0 AND ColumnQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ = 0 AND ColumnRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRR = 0 AND ColumnSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS = 0 AND ColumnTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT = 0 AND ColumnUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU = 0 FOR UPDATE; --connection con1 --reap ROLLBACK; --source suite/innodb/include/show_last_deadlock.inc --connection default DROP TABLE bug; disconnect con1; disconnect con2; --source include/wait_until_count_sessions.inc SET GLOBAL innodb_lock_wait_timeout = default; SET GLOBAL innodb_deadlock_detect = default;