--source include/master-slave.inc --source include/have_log_bin.inc --source include/have_innodb.inc # # Prepare data # connection master; --disable_warnings DROP TABLE IF EXISTS t1; --enable_warnings CREATE TABLE t1 (a int primary key, b int) engine=innodb; INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); COMMIT; sync_slave_with_master; # Make sure slave get the data connection slave; SHOW CREATE TABLE t1; SELECT * FROM t1; # # Deadlock in master, the victim is the one with less undo log # and locks. # connection master; BEGIN; DELETE FROM t1 WHERE a = 1; connection master1; BEGIN; SELECT * FROM t1 WHERE a = 2 FOR UPDATE; INSERT INTO t1 VALUES (6,6); send SELECT * FROM t1 WHERE a = 1 FOR UPDATE; connection master; let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.innodb_trx WHERE trx_operation_state = 'starting index read' AND trx_state = "LOCK WAIT"; --source include/wait_condition.inc --error ER_LOCK_DEADLOCK DELETE FROM t1 WHERE a = 2; connection master1; reap; COMMIT; # # Deadlock in master and slave. The master's transaction is transfered # by binlog and executed by SLAVE SQL thread. The victim always be the # user thread. # connection slave; BEGIN; SELECT * FROM t1 WHERE a = 1 FOR UPDATE; INSERT INTO t1 VALUES (7,7); INSERT INTO t1 VALUES (8,8); INSERT INTO t1 VALUES (9,9); connection master; BEGIN; DELETE FROM t1 WHERE a = 2; DELETE FROM t1 WHERE a = 1; COMMIT; connection slave; let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.innodb_trx WHERE trx_operation_state = 'starting index read' AND trx_state = "LOCK WAIT"; --source include/wait_condition.inc #--error ER_LOCK_DEADLOCK SELECT * FROM t1 WHERE a = 2 FOR UPDATE; show slave status; connection master; DROP TABLE t1; show slave status; --source include/rpl_end.inc