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
171
172
173
174
175
176
177
|
#
# Locking related tests which use DEBUG_SYNC facility.
#
--source include/have_debug_sync.inc
# We need InnoDB to be able use TL_WRITE_ALLOW_WRITE type of locks in our tests.
--source include/have_innodb.inc
# The test for Bug#50821 requires binary logging turned on.
# With binary logging on, sub-queries in DML statements acquire
# TL_READ_NO_INSERT which was needed to reproduce this deadlock bug.
--source include/have_log_bin.inc
# Until bug#41971 'Thread state on embedded server is always "Writing to net"'
# is fixed this test can't be run on embedded version of server.
--source include/not_embedded.inc
# Save the initial number of concurrent sessions.
--source include/count_sessions.inc
--echo #
--echo # Test for bug #45143 "All connections hang on concurrent ALTER TABLE".
--echo #
--echo # Concurrent execution of statements which required weak write lock
--echo # (TL_WRITE_ALLOW_WRITE) on several instances of the same table and
--echo # statements which tried to acquire stronger write lock (TL_WRITE,
--echo # TL_WRITE_ALLOW_READ) on this table might have led to deadlock.
#
# Suppress warnings for INSERTs that use get_lock().
#
disable_query_log;
call mtr.add_suppression("Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT");
enable_query_log;
--disable_warnings
drop table if exists t1;
drop view if exists v1;
--enable_warnings
--echo # Create auxiliary connections used through the test.
connect (con_bug45143_1,localhost,root,,test,,);
connect (con_bug45143_3,localhost,root,,test,,);
connect (con_bug45143_2,localhost,root,,test,,);
connection default;
--echo # Reset DEBUG_SYNC facility before using it.
set debug_sync= 'RESET';
--echo # Turn off logging so calls to locking subsystem performed
--echo # for general_log table won't interfere with our test.
set @old_general_log = @@global.general_log;
set @@global.general_log= OFF;
create table t1 (i int) engine=InnoDB;
--echo # We have to use view in order to make LOCK TABLES avoid
--echo # acquiring SNRW metadata lock on table.
create view v1 as select * from t1;
insert into t1 values (1);
--echo # Prepare user lock which will be used for resuming execution of
--echo # the first statement after it acquires TL_WRITE_ALLOW_WRITE lock.
select get_lock("lock_bug45143_wait", 0);
--echo # Switch to connection 'con_bug45143_1'.
connection con_bug45143_1;
--echo # Sending:
--send insert into t1 values (get_lock("lock_bug45143_wait", 100));
--echo # Switch to connection 'con_bug45143_2'.
connection con_bug45143_2;
--echo # Wait until the above INSERT takes TL_WRITE_ALLOW_WRITE lock on 't1'
--echo # and then gets blocked on user lock 'lock_bug45143_wait'.
let $wait_condition= select count(*)= 1 from information_schema.processlist
where state= 'User lock' and
info='insert into t1 values (get_lock("lock_bug45143_wait", 100))';
--source include/wait_condition.inc
--echo # Ensure that upcoming SELECT waits after acquiring TL_WRITE_ALLOW_WRITE
--echo # lock for the first instance of 't1'.
set debug_sync='thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go';
--echo # Sending:
--send select count(*) > 0 from t1 as a, t1 as b for update;
--echo # Switch to connection 'con_bug45143_3'.
connection con_bug45143_3;
--echo # Wait until the above SELECT ... FOR UPDATE is blocked after
--echo # acquiring lock for the the first instance of 't1'.
set debug_sync= 'now WAIT_FOR parked';
--echo # Send LOCK TABLE statement which will try to get TL_WRITE lock on 't1':
--send lock table v1 write;
--echo # Switch to connection 'default'.
connection default;
--echo # Wait until this LOCK TABLES statement starts waiting for table lock.
let $wait_condition= select count(*)= 1 from information_schema.processlist
where state= 'Table lock' and
info='lock table v1 write';
--source include/wait_condition.inc
--echo # Allow SELECT ... FOR UPDATE to resume.
--echo # Since it already has TL_WRITE_ALLOW_WRITE lock on the first instance
--echo # of 't1' it should be able to get lock on the second instance without
--echo # waiting, even although there is another thread which has such lock
--echo # on this table and also there is a thread waiting for a TL_WRITE on it.
set debug_sync= 'now SIGNAL go';
--echo # Switch to connection 'con_bug45143_2'.
connection con_bug45143_2;
--echo # Reap SELECT ... FOR UPDATE
--reap
--echo # Switch to connection 'default'.
connection default;
--echo # Resume execution of the INSERT statement.
select release_lock("lock_bug45143_wait");
--echo # Switch to connection 'con_bug45143_1'.
connection con_bug45143_1;
--echo # Reap INSERT statement.
--reap
--echo # Switch to connection 'con_bug45143_3'.
connection con_bug45143_3;
--echo # Reap LOCK TABLES statement.
--reap
unlock tables;
--echo # Switch to connection 'default'.
connection default;
--echo # Do clean-up.
disconnect con_bug45143_1;
disconnect con_bug45143_2;
disconnect con_bug45143_3;
set debug_sync= 'RESET';
set @@global.general_log= @old_general_log;
drop view v1;
drop table t1;
--echo #
--echo # Bug#50821 Deadlock between LOCK TABLES and ALTER TABLE
--echo #
--disable_warnings
DROP TABLE IF EXISTS t1, t2;
--enable_warnings
CREATE TABLE t1(id INT);
CREATE TABLE t2(id INT);
--echo # Connection con2
connect (con2, localhost, root);
START TRANSACTION;
SELECT * FROM t1;
--echo # Connection default
connection default;
--echo # Sending:
--send ALTER TABLE t1 ADD COLUMN j INT
--echo # Connection con2
connection con2;
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table"
AND info = "ALTER TABLE t1 ADD COLUMN j INT";
--source include/wait_condition.inc
--echo # This used to cause a deadlock.
INSERT INTO t2 SELECT * FROM t1;
COMMIT;
--echo # Connection default
connection default;
--echo # Reaping ALTER TABLE t1 ADD COLUMN j INT
--reap
DROP TABLE t1, t2;
disconnect con2;
# Check that all connections opened by test cases in this file are really
# gone so execution of other tests won't be affected by their presence.
--source include/wait_until_count_sessions.inc
|
