--source include/have_innodb.inc --source include/master-slave.inc DROP TABLE IF EXISTS deadlock1; CREATE TABLE deadlock1 ( id tinyint unsigned NOT NULL ) ENGINE=InnoDB; DROP TABLE IF EXISTS deadlock2; CREATE TABLE deadlock2 ( id tinyint unsigned NOT NULL auto_increment, somekey tinyint unsigned NOT NULL DEFAULT 0, PRIMARY KEY (id), INDEX (somekey) ) ENGINE=InnoDB; DELIMITER //; CREATE TRIGGER aftupd_deadlock1 AFTER UPDATE ON deadlock1 FOR EACH ROW BEGIN DECLARE v_key, v_sleep tinyint unsigned; SELECT somekey INTO v_key FROM deadlock2 WHERE id = 1; SELECT sleep(3) INTO v_sleep; IF (v_key = 1) THEN UPDATE deadlock2 SET somekey = somekey + 1 WHERE id = 1; END IF; END; // DELIMITER ;// INSERT INTO deadlock1 VALUES (0); INSERT INTO deadlock2 VALUES (1, 1); UPDATE deadlock1 SET id = id + 1; #connect (addconroot, localhost, root,,); #connection addconroot; connection slave; START TRANSACTION; SELECT somekey FROM deadlock2 WHERE somekey = 1 LOCK IN SHARE MODE; INSERT INTO deadlock1 VALUES (0); INSERT INTO deadlock1 VALUES (0); INSERT INTO deadlock1 VALUES (0); SELECT sleep(3); SELECT * FROM deadlock2 WHERE id = 1 FOR UPDATE; COMMIT; sleep 3; --vertical_results show slave status;