drop table if exists t1, t2; create table t1 (a int) engine=innodb; create table t2 (a int) engine=myisam; reset master; begin; insert into t1 values(1); insert into t2 select * from t1; commit; show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 95 Query 1 # use `test`; BEGIN master-bin.000001 157 Query 1 # use `test`; insert into t1 values(1) master-bin.000001 238 Query 1 # use `test`; insert into t2 select * from t1 master-bin.000001 326 Query 1 # use `test`; COMMIT delete from t1; delete from t2; reset master; begin; insert into t1 values(2); insert into t2 select * from t1; rollback; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 95 Query 1 # use `test`; BEGIN master-bin.000001 157 Query 1 # use `test`; insert into t1 values(2) master-bin.000001 238 Query 1 # use `test`; insert into t2 select * from t1 master-bin.000001 326 Query 1 # use `test`; ROLLBACK delete from t1; delete from t2; reset master; begin; insert into t1 values(3); savepoint my_savepoint; insert into t1 values(4); insert into t2 select * from t1; rollback to savepoint my_savepoint; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back commit; show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 95 Query 1 # use `test`; BEGIN master-bin.000001 157 Query 1 # use `test`; insert into t1 values(3) master-bin.000001 238 Query 1 # use `test`; savepoint my_savepoint master-bin.000001 317 Query 1 # use `test`; insert into t1 values(4) master-bin.000001 398 Query 1 # use `test`; insert into t2 select * from t1 master-bin.000001 486 Query 1 # use `test`; rollback to savepoint my_savepoint master-bin.000001 577 Query 1 # use `test`; COMMIT delete from t1; delete from t2; reset master; begin; insert into t1 values(5); savepoint my_savepoint; insert into t1 values(6); insert into t2 select * from t1; rollback to savepoint my_savepoint; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back insert into t1 values(7); commit; select a from t1 order by a; a 5 7 show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 95 Query 1 # use `test`; BEGIN master-bin.000001 157 Query 1 # use `test`; insert into t1 values(5) master-bin.000001 238 Query 1 # use `test`; savepoint my_savepoint master-bin.000001 317 Query 1 # use `test`; insert into t1 values(6) master-bin.000001 398 Query 1 # use `test`; insert into t2 select * from t1 master-bin.000001 486 Query 1 # use `test`; rollback to savepoint my_savepoint master-bin.000001 577 Query 1 # use `test`; insert into t1 values(7) master-bin.000001 658 Query 1 # use `test`; COMMIT delete from t1; delete from t2; reset master; select get_lock("a",10); get_lock("a",10) 1 begin; insert into t1 values(8); insert into t2 select * from t1; select get_lock("a",10); get_lock("a",10) 1 show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 95 Query 1 # use `test`; BEGIN master-bin.000001 157 Query 1 # use `test`; insert into t1 values(8) master-bin.000001 238 Query 1 # use `test`; insert into t2 select * from t1 master-bin.000001 326 Query 1 # use `test`; ROLLBACK delete from t1; delete from t2; reset master; insert into t1 values(9); insert into t2 select * from t1; show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 95 Query 1 # use `test`; insert into t1 values(9) master-bin.000001 176 Query 1 # use `test`; insert into t2 select * from t1 delete from t1; delete from t2; reset master; insert into t1 values(10); begin; insert into t2 select * from t1; show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 95 Query 1 # use `test`; insert into t1 values(10) master-bin.000001 177 Query 1 # use `test`; insert into t2 select * from t1 insert into t1 values(11); commit; show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 95 Query 1 # use `test`; insert into t1 values(10) master-bin.000001 177 Query 1 # use `test`; insert into t2 select * from t1 master-bin.000001 265 Query 1 # use `test`; BEGIN master-bin.000001 327 Query 1 # use `test`; insert into t1 values(11) master-bin.000001 409 Query 1 # use `test`; COMMIT alter table t2 engine=INNODB; delete from t1; delete from t2; reset master; begin; insert into t1 values(12); insert into t2 select * from t1; commit; show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 95 Query 1 # use `test`; BEGIN master-bin.000001 157 Query 1 # use `test`; insert into t1 values(12) master-bin.000001 239 Query 1 # use `test`; insert into t2 select * from t1 master-bin.000001 327 Query 1 # use `test`; COMMIT delete from t1; delete from t2; reset master; begin; insert into t1 values(13); insert into t2 select * from t1; rollback; show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info delete from t1; delete from t2; reset master; begin; insert into t1 values(14); savepoint my_savepoint; insert into t1 values(15); insert into t2 select * from t1; rollback to savepoint my_savepoint; commit; show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 95 Query 1 # use `test`; BEGIN master-bin.000001 157 Query 1 # use `test`; insert into t1 values(14) master-bin.000001 239 Query 1 # use `test`; COMMIT delete from t1; delete from t2; reset master; begin; insert into t1 values(16); savepoint my_savepoint; insert into t1 values(17); insert into t2 select * from t1; rollback to savepoint my_savepoint; insert into t1 values(18); commit; select a from t1 order by a; a 16 18 show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 95 Query 1 # use `test`; BEGIN master-bin.000001 157 Query 1 # use `test`; insert into t1 values(16) master-bin.000001 239 Query 1 # use `test`; insert into t1 values(18) master-bin.000001 321 Query 1 # use `test`; COMMIT delete from t1; delete from t2; alter table t2 type=MyISAM; insert into t1 values (1); begin; select * from t1 for update; a 1 select (@before:=unix_timestamp())*0; (@before:=unix_timestamp())*0 0 begin; select * from t1 for update; insert into t2 values (20); ERROR HY000: Lock wait timeout exceeded; try restarting transaction select (@after:=unix_timestamp())*0; (@after:=unix_timestamp())*0 0 select (@after-@before) >= 2; (@after-@before) >= 2 1 drop table t1,t2;