1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
|
set @old_innodb_lock_wait_timeout=@@global.innodb_lock_wait_timeout;
set global innodb_lock_wait_timeout=300;
set session innodb_lock_wait_timeout=300;
call mtr.add_suppression("Deadlock found when trying to get lock; try restarting transaction");
#
# Bug #22876 Four-way deadlock
#
DROP TABLE IF EXISTS t1;
connect con1,localhost,root,,;
connect con2,localhost,root,,;
connect con3,localhost,root,,;
connection con1;
set @@autocommit=0;
CREATE TABLE t1(s1 INT UNIQUE) ENGINE=innodb;
INSERT INTO t1 VALUES (1);
connection con2;
set @@autocommit=0;
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (1);
connection con3;
set @@autocommit=0;
DROP TABLE t1;
connection con1;
# Waiting for until transaction will be locked inside innodb subsystem
# Connection 1 is now holding the lock.
# Issuing insert from connection 1 while connection 2&3
# is waiting for the lock should give a deadlock error.
INSERT INTO t1 VALUES (2);
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
# Cleanup
connection con2;
commit;
set @@autocommit=1;
connection con1;
commit;
set @@autocommit=1;
connection con3;
set @@autocommit=1;
connection default;
disconnect con1;
disconnect con2;
disconnect con3;
#
# Test for bug #37346 "innodb does not detect deadlock between update
# and alter table".
#
drop table if exists t1;
create table t1 (c1 int primary key, c2 int, c3 int) engine=InnoDB;
insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0);
begin;
# Run statement which acquires X-lock on one of table's rows.
update t1 set c3=c3+1 where c2=3;
#
connect con37346,localhost,root,,test,,;
connection con37346;
# The below ALTER TABLE statement should wait till transaction
# in connection 'default' is complete and then succeed.
# It should not deadlock or fail with ER_LOCK_DEADLOCK error.
# Sending:
alter table t1 add column c4 int;;
#
connection default;
# Wait until the above ALTER TABLE gets blocked because this
# connection holds SW metadata lock on table to be altered.
# The below statement should succeed. It should not
# deadlock or end with ER_LOCK_DEADLOCK error.
update t1 set c3=c3+1 where c2=4;
# Unblock ALTER TABLE by committing transaction.
commit;
#
connection con37346;
# Reaping ALTER TABLE.
#
connection default;
disconnect con37346;
drop table t1;
#
# Bug#53798 OPTIMIZE TABLE breaks repeatable read
#
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT) engine=innodb;
INSERT INTO t1 VALUES (1), (2), (3);
connect con1, localhost, root;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT * FROM t1;
a
1
2
3
connection default;
# This should block
# Sending:
OPTIMIZE TABLE t1;
connection con1;
SELECT * FROM t1;
a
1
2
3
COMMIT;
connection default;
# Reaping OPTIMIZE TABLE t1
Table Op Msg_type Msg_text
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
test.t1 optimize status OK
disconnect con1;
DROP TABLE t1;
#
# Bug#49891 View DDL breaks REPEATABLE READ
#
DROP TABLE IF EXISTS t1, t2;
DROP VIEW IF EXISTS v2;
CREATE TABLE t1 ( f1 INTEGER ) ENGINE = innodb;
CREATE TABLE t2 ( f1 INTEGER );
CREATE VIEW v1 AS SELECT 1 FROM t1;
connect con2, localhost, root;
connect con3, localhost, root;
connection con3;
LOCK TABLE t1 WRITE;
connection default;
START TRANSACTION;
# Sending:
SELECT * FROM v1;
connection con2;
# Waiting for 'SELECT * FROM v1' to sync in.
# Sending:
ALTER VIEW v1 AS SELECT 2 FROM t2;
connection con3;
# Waiting for 'ALTER VIEW v1 AS SELECT 2 FROM t2' to sync in.
UNLOCK TABLES;
connection default;
# Reaping: SELECT * FROM v1
1
SELECT * FROM v1;
1
COMMIT;
connection con2;
# Reaping: ALTER VIEW v1 AS SELECT 2 FROM t2
connection default;
DROP TABLE t1, t2;
DROP VIEW v1;
disconnect con2;
disconnect con3;
#
# Bug#11815600 [ERROR] INNODB COULD NOT FIND INDEX PRIMARY
# KEY NO 0 FOR TABLE IN ERROR LOG
#
DROP TABLE IF EXISTS t1;
connect con1,localhost,root;
connection default;
CREATE TABLE t1 (id INT PRIMARY KEY, value INT) ENGINE = InnoDB;
INSERT INTO t1 VALUES (1, 12345);
START TRANSACTION;
SELECT * FROM t1;
id value
1 12345
connection con1;
SET lock_wait_timeout=1;
ALTER TABLE t1 ADD INDEX idx(value);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
ALTER TABLE t1 ADD INDEX idx(value);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
connection default;
SELECT * FROM t1;
id value
1 12345
COMMIT;
DROP TABLE t1;
disconnect con1;
set global innodb_lock_wait_timeout=@old_innodb_lock_wait_timeout;
|
