1# Establish connection con1 (user=root) 2# Establish connection con2 (user=root) 3drop table if exists t1,t2; 4# Switch to connection con1 5create table t1 (id integer, x integer) engine = InnoDB; 6insert into t1 values(0, 0); 7set autocommit=0; 8SELECT * from t1 where id = 0 FOR UPDATE; 9id x 100 0 11# Switch to connection con2 12set autocommit=0; 13update t1 set x=2 where id = 0; 14# Switch to connection con1 15update t1 set x=1 where id = 0; 16select * from t1; 17id x 180 1 19commit; 20# Switch to connection con2 21commit; 22# Switch to connection con1 23select * from t1; 24id x 250 2 26commit; 27drop table t1; 28# Switch to connection con1 29create table t1 (id integer, x integer) engine = InnoDB; 30create table t2 (b integer, a integer) engine = InnoDB; 31insert into t1 values(0, 0), (300, 300); 32insert into t2 values(0, 10), (1, 20), (2, 30); 33commit; 34set autocommit=0; 35select * from t2; 36b a 370 10 381 20 392 30 40update t2 set a=100 where b=(SELECT x from t1 where id = b FOR UPDATE); 41select * from t2; 42b a 430 100 441 20 452 30 46select * from t1; 47id x 480 0 49300 300 50# Switch to connection con2 51set autocommit=0; 52update t1 set x=2 where id = 0; 53# Switch to connection con1 54update t1 set x=1 where id = 0; 55select * from t1; 56id x 570 1 58300 300 59commit; 60# Switch to connection con2 61commit; 62# Switch to connection con1 63select * from t1; 64id x 650 2 66300 300 67commit; 68drop table t1, t2; 69create table t1 (id integer, x integer) engine = InnoDB; 70create table t2 (b integer, a integer) engine = InnoDB; 71insert into t1 values(0, 0), (300, 300); 72insert into t2 values(0, 0), (1, 20), (2, 30); 73commit; 74# Switch to connection con1 75select a,b from t2 UNION SELECT id, x from t1 FOR UPDATE; 76a b 770 0 7820 1 7930 2 80300 300 81select * from t2; 82b a 830 0 841 20 852 30 86select * from t1; 87id x 880 0 89300 300 90# Switch to connection con2 91update t2 set a=2 where b = 0; 92select * from t2; 93b a 940 2 951 20 962 30 97update t1 set x=2 where id = 0; 98# Switch to connection con1 99update t1 set x=1 where id = 0; 100select * from t1; 101id x 1020 1 103300 300 104commit; 105# Switch to connection con2 106commit; 107# Switch to connection con1 108select * from t1; 109id x 1100 2 111300 300 112commit; 113# Switch to connection default + disconnect con1 and con2 114drop table t1, t2; 115End of 4.1 tests 116set default_storage_engine=innodb; 117drop table if exists a; 118drop table if exists A; 119create table A (c int); 120insert into A (c) values (0); 121create table a as select * from A; 122drop table A; 123drop table if exists a; 124set default_storage_engine=default; 125End of 5.0 tests. 126