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
178
179
180
181
182
|
--source include/have_innodb.inc
--source include/have_log_bin.inc
--let $rpl_topology=1->2,1->3
--source include/rpl_init.inc
--connect (server_2b,127.0.0.1,root,,,$SERVER_MYPORT_2)
--connection server_2
SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads;
SET @old_parallel_mode= @@GLOBAL.slave_parallel_mode;
SET @old_timeout= @@GLOBAL.lock_wait_timeout;
SET @old_innodb_timeout= @@GLOBAL.innodb_lock_wait_timeout;
--source include/stop_slave.inc
SET GLOBAL slave_parallel_threads=5;
set global slave_parallel_mode= aggressive;
# High timeout so we get replication sync error and test failure if the
# conflict handling is insufficient and lock wait timeout occurs.
SET GLOBAL lock_wait_timeout= 86400;
SET GLOBAL innodb_lock_wait_timeout= 86400;
SET STATEMENT sql_log_bin=0 FOR ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
--source include/start_slave.inc
--connection server_1
CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0), (6, 0), (7, 0), (8, 0);
--save_master_pos
--connection server_2
--sync_with_master
--source include/stop_slave.inc
# Test the following scenario:
#
# Transactions T1, T2 in domain 1, U1, U2 in domain 2.
# Wait cycle T1->U2->U1->T2->T1 as follows:
# T1 row lock wait on U2
# U2 wait_for_prior_commit on U1
# U1 row lock wait on T2
# T2 wait_for_prior_commit on T1
#
# Test that the wait cycle is broken correctly with deadlock kill.
--connection server_2b
# Temporarily block T1 and U1.
BEGIN;
SELECT * FROM t1 WHERE a=1 FOR UPDATE;
SELECT * FROM t1 WHERE a=5 FOR UPDATE;
--connection server_1
SET SESSION gtid_domain_id= 1;
# T1 in domain 1
BEGIN;
UPDATE t1 SET b=1 WHERE a=1;
UPDATE t1 SET b=1 WHERE a=7;
COMMIT;
# T2 in domain 1
UPDATE t1 SET b=2 WHERE a=3;
SET SESSION gtid_domain_id=2;
# U1 in domain 2
BEGIN;
UPDATE t1 SET b=3 WHERE a=5;
UPDATE t1 SET b=3 WHERE a=3;
COMMIT;
# U2 in domain 2
UPDATE t1 SET b=4 WHERE a=7;
SET SESSION gtid_domain_id= 0;
--source include/save_master_gtid.inc
--connection server_2
--source include/start_slave.inc
# Wait until T2, U2 are holding the row locks.
--let $wait_condition= SELECT COUNT(*)=2 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE state LIKE '%Waiting for prior transaction to commit%'
--source include/wait_condition.inc
# Then let T1, U1 continue to conflict on the row locks, and check that
# replication correctly handles the conflict.
--connection server_2b
ROLLBACK;
--connection server_2
--source include/sync_with_master_gtid.inc
# Allow either domain to "win" on the conflicting updates.
SELECT a, (
(a=1 AND b=1) OR
(a=3 AND (b=2 OR b=3)) OR
(a=5 AND b=3) OR
(a=7 AND (b=1 OR b=4)) OR
((a MOD 2)=0 AND b=0)) AS `ok`
FROM t1
ORDER BY a;
# Now try the same thing with multi-source replication.
# Make server_3 a second master
--connection server_3
--source include/sync_with_master_gtid.inc
--source include/stop_slave.inc
--connection server_2
--source include/stop_slave.inc
--replace_result $SERVER_MYPORT_3 MYPORT_3
eval CHANGE MASTER 'm2' to master_port=$SERVER_MYPORT_3 , master_host='127.0.0.1', master_user='root', master_use_gtid=slave_pos, master_ssl_verify_server_cert=0;
--connection server_1
SET SESSION gtid_domain_id= 1;
# T1 in domain 1
BEGIN;
UPDATE t1 SET b=11 WHERE a=1;
UPDATE t1 SET b=11 WHERE a=7;
COMMIT;
# T2 in domain 1
UPDATE t1 SET b=12 WHERE a=3;
SET SESSION gtid_domain_id= 1;
--connection server_3
SET SESSION gtid_domain_id=3;
# U1 in domain 3
BEGIN;
UPDATE t1 SET b=13 WHERE a=5;
UPDATE t1 SET b=13 WHERE a=3;
COMMIT;
# U2 in domain 3
UPDATE t1 SET b=14 WHERE a=7;
--source include/save_master_gtid.inc
--connection server_2b
# Temporarily block T1 and U1.
BEGIN;
SELECT * FROM t1 WHERE a=1 FOR UPDATE;
SELECT * FROM t1 WHERE a=5 FOR UPDATE;
START ALL SLAVES;
# Wait until T2, U2 are holding the row locks.
--let $wait_condition= SELECT COUNT(*)=2 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE state LIKE '%Waiting for prior transaction to commit%'
--source include/wait_condition.inc
--connection server_2b
ROLLBACK;
--connection server_1
--source include/save_master_gtid.inc
--connection server_2
--source include/sync_with_master_gtid.inc
--connection server_3
--source include/save_master_gtid.inc
--connection server_2
--source include/sync_with_master_gtid.inc
SELECT a, (
(a=1 AND b=11) OR
(a=3 AND (b=12 OR b=13)) OR
(a=5 AND b=13) OR
(a=7 AND (b=11 OR b=14)) OR
((a MOD 2)=0 AND b=0)) AS `ok`
FROM t1
ORDER BY a;
SET default_master_connection = 'm2';
--source include/stop_slave.inc
RESET SLAVE 'm2' ALL;
SET default_master_connection = '';
--connection server_3
--source include/start_slave.inc
# Cleanup
--disconnect server_2b
--connection server_1
DROP TABLE t1;
--connection server_2
--source include/stop_slave.inc
SET GLOBAL slave_parallel_threads=@old_parallel_threads;
set global slave_parallel_mode= @old_parallel_mode;
SET GLOBAL lock_wait_timeout= @old_timeout;
SET GLOBAL innodb_lock_wait_timeout= @old_innodb_timeout;
--source include/start_slave.inc
--source include/rpl_end.inc
|
