-- source include/have_innodb.inc connect (con1,localhost,root,,); connect (con2,localhost,root,,); --disable_warnings drop table if exists t1,t2; --enable_warnings # # Testing of FOR UPDATE # connection con1; create table t1 (id integer, x integer) engine=INNODB; insert into t1 values(0, 0); set autocommit=0; SELECT * from t1 where id = 0 FOR UPDATE; connection con2; set autocommit=0; # The following query should hang because con1 is locking the page --send update t1 set x=2 where id = 0; --sleep 2 connection con1; update t1 set x=1 where id = 0; select * from t1; commit; connection con2; reap; commit; connection con1; select * from t1; commit; drop table t1; # # Testing of FOR UPDATE # connection con1; create table t1 (id integer, x integer) engine=INNODB; create table t2 (b integer, a integer) engine=INNODB; insert into t1 values(0, 0), (300, 300); insert into t2 values(0, 10), (1, 20), (2, 30); commit; set autocommit=0; select * from t2; update t2 set a=100 where b=(SELECT x from t1 where id = b FOR UPDATE); select * from t2; select * from t1; connection con2; set autocommit=0; # The following query should hang because con1 is locking the page --send update t1 set x=2 where id = 0; --sleep 2 connection con1; update t1 set x=1 where id = 0; select * from t1; commit; connection con2; reap; commit; connection con1; select * from t1; commit; drop table t1, t2; create table t1 (id integer, x integer) engine=INNODB; create table t2 (b integer, a integer) engine=INNODB; insert into t1 values(0, 0), (300, 300); insert into t2 values(0, 0), (1, 20), (2, 30); commit; connection con1; select a,b from t2 UNION SELECT id, x from t1 FOR UPDATE; select * from t2; select * from t1; connection con2; # The following query should hang because con1 is locking the page update t2 set a=2 where b = 0; select * from t2; --send update t1 set x=2 where id = 0; --sleep 2 connection con1; update t1 set x=1 where id = 0; select * from t1; commit; connection con2; reap; commit; connection con1; select * from t1; commit; drop table t1, t2; # End of 4.1 tests