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