stop slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; ######################################################################## # Environment ######################################################################## CREATE TABLE t (i INT, PRIMARY KEY(i), f CHAR(8)) engine = Innodb; CREATE TABLE n (d DATETIME, f CHAR(32)) engine = MyIsam; CREATE TRIGGER tr AFTER UPDATE ON t FOR EACH ROW BEGIN INSERT INTO n VALUES ( now(), concat( 'updated t: ', old.f, ' -> ', new.f ) ); END | INSERT INTO t VALUES (4,'black'), (2,'red'), (3,'yelow'), (1,'cyan'); ######################################################################## # Testing ER_LOCK_WAIT_TIMEOUT ######################################################################## SET AUTOCOMMIT = 1; BEGIN; UPDATE t SET f = 'yellow 2' WHERE i = 3; SET AUTOCOMMIT = 1; BEGIN; UPDATE t SET f = 'magenta 2' WHERE f = 'red'; ERROR HY000: Lock wait timeout exceeded; try restarting transaction INSERT INTO t VALUES (5 + (2 * 10),"brown"); INSERT INTO n VALUES (now(),"brown"); COMMIT; ROLLBACK; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'yellow 2' WHERE i = 3 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'magenta 2' WHERE f = 'red' master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (5 + (2 * 10),"brown") master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown") master-bin.000001 # Query # # ROLLBACK SET AUTOCOMMIT = 1; BEGIN; UPDATE t SET f = 'gray 2' WHERE i = 3; SET AUTOCOMMIT = 1; BEGIN; UPDATE t SET f = 'dark blue 2' WHERE f = 'red'; ERROR HY000: Lock wait timeout exceeded; try restarting transaction INSERT INTO t VALUES (6 + (2 * 10),"brown"); INSERT INTO n VALUES (now(),"brown"); COMMIT; COMMIT; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'gray 2' WHERE i = 3 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'dark blue 2' WHERE f = 'red' master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (6 + (2 * 10),"brown") master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown") master-bin.000001 # Xid # # COMMIT /* XID */ SET AUTOCOMMIT = 0; UPDATE t SET f = 'yellow 1' WHERE i = 3; SET AUTOCOMMIT = 0; UPDATE t SET f = 'magenta 1' WHERE f = 'red'; ERROR HY000: Lock wait timeout exceeded; try restarting transaction INSERT INTO t VALUES (5 + (1 * 10),"brown"); INSERT INTO n VALUES (now(),"brown"); COMMIT; ROLLBACK; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'yellow 1' WHERE i = 3 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'magenta 1' WHERE f = 'red' master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (5 + (1 * 10),"brown") master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown") master-bin.000001 # Query # # ROLLBACK SET AUTOCOMMIT = 0; UPDATE t SET f = 'gray 1' WHERE i = 3; SET AUTOCOMMIT = 0; UPDATE t SET f = 'dark blue 1' WHERE f = 'red'; ERROR HY000: Lock wait timeout exceeded; try restarting transaction INSERT INTO t VALUES (6 + (1 * 10),"brown"); INSERT INTO n VALUES (now(),"brown"); COMMIT; COMMIT; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'gray 1' WHERE i = 3 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'dark blue 1' WHERE f = 'red' master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (6 + (1 * 10),"brown") master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown") master-bin.000001 # Xid # # COMMIT /* XID */ ######################################################################## # Cleanup ######################################################################## DROP TRIGGER tr; DROP TABLE t; DROP TABLE n;