1drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; 2create table t1 (id int not null, f_id int not null, f int not null, 3primary key(f_id, id)) engine = InnoDB; 4create table t2 (id int not null,s_id int not null,s varchar(200), 5primary key(id)) engine = InnoDB; 6INSERT INTO t1 VALUES (8, 1, 3); 7INSERT INTO t1 VALUES (1, 2, 1); 8INSERT INTO t2 VALUES (1, 0, ''); 9INSERT INTO t2 VALUES (8, 1, ''); 10commit; 11DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id) 12WHERE mm.id IS NULL; 13select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id) 14where mm.id is null lock in share mode; 15id f_id f 16drop table t1,t2; 17create table t1(a int not null, b int, primary key(a)) engine = InnoDB; 18insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3); 19commit; 20set autocommit = 0; 21select * from t1 lock in share mode; 22a b 231 1 242 2 253 1 264 2 275 1 286 2 297 3 30update t1 set b = 5 where b = 1; 31set autocommit = 0; 32select * from t1 where a = 2 and b = 2 for update; 33ERROR HY000: Lock wait timeout exceeded; try restarting transaction 34commit; 35commit; 36drop table t1; 37create table t1(a int not null, b int, primary key(a)) engine = InnoDB; 38insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3); 39commit; 40set autocommit = 0; 41update t1 set b = 5 where b = 1; 42set autocommit = 0; 43select * from t1 where a = 7 and b = 3 for update; 44a b 457 3 46commit; 47commit; 48drop table t1; 49create table t1(a int not null, b int, primary key(a)) engine = InnoDB; 50insert into t1 values (1,2),(5,3),(4,2); 51create table t2(d int not null, e int, primary key(d)) engine = InnoDB; 52insert into t2 values (8,6),(12,1),(3,1); 53commit; 54set autocommit = 0; 55select * from t2 for update; 56d e 573 1 588 6 5912 1 60set autocommit = 0; 61insert into t1 select * from t2; 62update t1 set b = (select e from t2 where a = d); 63create table t3(d int not null, e int, primary key(d)) engine = InnoDB 64select * from t2; 65commit; 66commit; 67drop table t1, t2, t3; 68SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB; 69SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB; 70SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB; 71create table t1(a int not null, b int, primary key(a)) engine = InnoDB; 72insert into t1 values (1,2),(5,3),(4,2); 73create table t2(a int not null, b int, primary key(a)) engine = InnoDB; 74insert into t2 values (8,6),(12,1),(3,1); 75create table t3(d int not null, b int, primary key(d)) engine = InnoDB; 76insert into t3 values (8,6),(12,1),(3,1); 77create table t5(a int not null, b int, primary key(a)) engine = InnoDB; 78insert into t5 values (1,2),(5,3),(4,2); 79create table t6(d int not null, e int, primary key(d)) engine = InnoDB; 80insert into t6 values (8,6),(12,1),(3,1); 81create table t8(a int not null, b int, primary key(a)) engine = InnoDB; 82insert into t8 values (1,2),(5,3),(4,2); 83create table t9(d int not null, e int, primary key(d)) engine = InnoDB; 84insert into t9 values (8,6),(12,1),(3,1); 85commit; 86set autocommit = 0; 87select * from t2 for update; 88a b 893 1 908 6 9112 1 92set autocommit = 0; 93SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 94insert into t1 select * from t2; 95set autocommit = 0; 96SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 97update t3 set b = (select b from t2 where a = d); 98set autocommit = 0; 99SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 100create table t4(a int not null, b int, primary key(a)) select * from t2; 101set autocommit = 0; 102insert into t5 (select * from t2 lock in share mode); 103set autocommit = 0; 104update t6 set e = (select b from t2 where a = d lock in share mode); 105set autocommit = 0; 106create table t7(a int not null, b int, primary key(a)) select * from t2 lock in share mode; 107set autocommit = 0; 108insert into t8 (select * from t2 for update); 109set autocommit = 0; 110update t9 set e = (select b from t2 where a = d for update); 111set autocommit = 0; 112create table t10(a int not null, b int, primary key(a)) select * from t2 for update; 113ERROR HY000: Lock wait timeout exceeded; try restarting transaction 114ERROR HY000: Lock wait timeout exceeded; try restarting transaction 115ERROR HY000: Lock wait timeout exceeded; try restarting transaction 116ERROR HY000: Lock wait timeout exceeded; try restarting transaction 117ERROR HY000: Lock wait timeout exceeded; try restarting transaction 118ERROR HY000: Lock wait timeout exceeded; try restarting transaction 119ERROR HY000: Lock wait timeout exceeded; try restarting transaction 120ERROR HY000: Lock wait timeout exceeded; try restarting transaction 121ERROR HY000: Can't update table 't2' while 't10' is being created. 122commit; 123drop table t1, t2, t3, t5, t6, t8, t9; 124