1# include/deadlock.inc 2# 3# The variable 4# $engine_type -- storage engine to be tested 5# has to be set before sourcing this script. 6# 7# Last update: 8# 2006-07-26 ML refactoring + print when connection is switched 9# old name was t/innodb-deadlock.test 10# main code went into include/deadlock.inc 11# 12 13connect (con1,localhost,root,,); 14connect (con2,localhost,root,,); 15 16--disable_warnings 17drop table if exists t1,t2; 18--enable_warnings 19 20# 21# Testing of FOR UPDATE 22# 23 24connection con1; 25eval create table t1 (id integer, x integer) engine = $engine_type; 26insert into t1 values(0, 0); 27set autocommit=0; 28SELECT * from t1 where id = 0 FOR UPDATE; 29 30connection con2; 31set autocommit=0; 32 33# The following query should hang because con1 is locking the record 34--send 35update t1 set x=2 where id = 0; 36--sleep 2 37 38connection con1; 39update t1 set x=1 where id = 0; 40select * from t1; 41commit; 42 43connection con2; 44reap; 45commit; 46 47connection con1; 48select * from t1; 49commit; 50 51drop table t1; 52# 53# Testing of FOR UPDATE 54# 55 56connection con1; 57eval create table t1 (id integer, x integer) engine = $engine_type; 58eval create table t2 (b integer, a integer) engine = $engine_type; 59insert into t1 values(0, 0), (300, 300); 60insert into t2 values(0, 10), (1, 20), (2, 30); 61commit; 62set autocommit=0; 63select * from t2; 64update t2 set a=100 where b=(SELECT x from t1 where id = b FOR UPDATE); 65select * from t2; 66select * from t1; 67 68connection con2; 69set autocommit=0; 70 71# The following query should hang because con1 is locking the record 72--send 73update t1 set x=2 where id = 0; 74--sleep 2 75 76connection con1; 77update t1 set x=1 where id = 0; 78select * from t1; 79commit; 80 81connection con2; 82reap; 83commit; 84 85connection con1; 86select * from t1; 87commit; 88 89drop table t1, t2; 90eval create table t1 (id integer, x integer) engine = $engine_type; 91eval create table t2 (b integer, a integer) engine = $engine_type; 92insert into t1 values(0, 0), (300, 300); 93insert into t2 values(0, 0), (1, 20), (2, 30); 94commit; 95 96connection con1; 97select a,b from t2 UNION (SELECT id, x from t1 FOR UPDATE); 98select * from t2; 99select * from t1; 100 101connection con2; 102 103# The following query should hang because con1 is locking the record 104update t2 set a=2 where b = 0; 105select * from t2; 106--send 107update t1 set x=2 where id = 0; 108--sleep 2 109 110connection con1; 111update t1 set x=1 where id = 0; 112select * from t1; 113commit; 114 115connection con2; 116reap; 117commit; 118 119connection con1; 120select * from t1; 121commit; 122 123# Cleanup 124connection default; 125disconnect con1; 126disconnect con2; 127drop table t1, t2; 128 129--echo End of 4.1 tests 130 131# 132# Bug#25164 create table `a` as select * from `A` hangs 133# 134 135set storage_engine=innodb; 136 137--disable_warnings 138drop table if exists a; 139drop table if exists A; 140--enable_warnings 141 142create table A (c int); 143insert into A (c) values (0); 144--error 0,ER_LOCK_DEADLOCK,ER_TABLE_EXISTS_ERROR 145create table a as select * from A; 146drop table A; 147 148--disable_warnings 149drop table if exists a; 150--enable_warnings 151 152set storage_engine=default; 153 154--echo End of 5.0 tests. 155