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
|
connection node_2;
connection node_1;
connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1;
connection node_1;
CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER) ENGINE=INNODB;
INSERT INTO p VALUES (1, 0);
INSERT INTO p VALUES (2, 0);
INSERT INTO c VALUES (1, 1);
INSERT INTO c VALUES (2, 2);
connection node_1;
SET AUTOCOMMIT=ON;
START TRANSACTION;
UPDATE p SET f1 = f1 + 100;
connection node_1a;
SET SESSION wsrep_sync_wait = 0;
SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
connection node_2;
ALTER TABLE c ADD FOREIGN KEY (p_id) REFERENCES p(f1);
connection node_1a;
SET SESSION wsrep_on = 0;
SET SESSION wsrep_on = 1;
SET GLOBAL wsrep_provider_options = 'dbug=';
SET GLOBAL wsrep_provider_options = 'dbug=d,local_monitor_master_enter_sync';
connection node_1;
COMMIT;
connection node_1a;
SET SESSION wsrep_on = 0;
SET SESSION wsrep_on = 1;
SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
SET GLOBAL wsrep_provider_options = 'signal=local_monitor_master_enter_sync';
SET GLOBAL wsrep_provider_options = 'dbug=';
connection node_1;
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
connection node_2;
SELECT * FROM p;
f1 f2
1 0
2 0
SELECT * FROM c;
f1 p_id
1 1
2 2
DROP TABLE c;
DROP TABLE p;
connection node_1;
CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id1 INTEGER, p_id2 INTEGER) ENGINE=INNODB;
INSERT INTO p1 VALUES (1, 0), (2, 0);
INSERT INTO p2 VALUES (1, 0), (2, 0);
INSERT INTO c VALUES (1, 1, 1);
INSERT INTO c VALUES (2, 2, 2);
connection node_1;
SET AUTOCOMMIT=ON;
START TRANSACTION;
UPDATE p1 SET f1 = f1 + 100;
connection node_1a;
SET SESSION wsrep_sync_wait = 0;
SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
connection node_2;
ALTER TABLE c ADD FOREIGN KEY (p_id1) REFERENCES p1(f1), ADD FOREIGN KEY (p_id2) REFERENCES p2(f1);
connection node_1a;
SET SESSION wsrep_on = 0;
SET SESSION wsrep_on = 1;
SET GLOBAL wsrep_provider_options = 'dbug=';
SET GLOBAL wsrep_provider_options = 'dbug=d,local_monitor_master_enter_sync';
connection node_1;
COMMIT;
connection node_1a;
SET SESSION wsrep_on = 0;
SET SESSION wsrep_on = 1;
SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
SET GLOBAL wsrep_provider_options = 'signal=local_monitor_master_enter_sync';
SET GLOBAL wsrep_provider_options = 'dbug=';
connection node_1;
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
connection node_2;
SELECT * FROM p1;
f1 f2
1 0
2 0
SELECT * FROM p2;
f1 f2
1 0
2 0
SELECT * FROM c;
f1 p_id1 p_id2
1 1 1
2 2 2
DROP TABLE c;
DROP TABLE p1;
DROP TABLE p2;
connection node_1;
CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id1 INTEGER, p_id2 INTEGER) ENGINE=INNODB;
INSERT INTO p1 VALUES (1, 0), (2, 0);
INSERT INTO p2 VALUES (1, 0), (2, 0);
INSERT INTO c VALUES (1, 1, 1);
INSERT INTO c VALUES (2, 2, 2);
connection node_1;
SET AUTOCOMMIT=ON;
START TRANSACTION;
UPDATE p2 SET f1 = f1 + 100;
connection node_1a;
SET SESSION wsrep_sync_wait = 0;
SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
connection node_2;
ALTER TABLE c ADD FOREIGN KEY (p_id1) REFERENCES p1(f1), ADD FOREIGN KEY (p_id2) REFERENCES p2(f1);
connection node_1a;
SET SESSION wsrep_on = 0;
SET SESSION wsrep_on = 1;
SET GLOBAL wsrep_provider_options = 'dbug=';
SET GLOBAL wsrep_provider_options = 'dbug=d,local_monitor_master_enter_sync';
connection node_1;
COMMIT;
connection node_1a;
SET SESSION wsrep_on = 0;
SET SESSION wsrep_on = 1;
SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
SET GLOBAL wsrep_provider_options = 'signal=local_monitor_master_enter_sync';
SET GLOBAL wsrep_provider_options = 'dbug=';
connection node_1;
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
connection node_2;
SELECT * FROM p1;
f1 f2
1 0
2 0
SELECT * FROM p2;
f1 f2
1 0
2 0
SELECT * FROM c;
f1 p_id1 p_id2
1 1 1
2 2 2
DROP TABLE c;
DROP TABLE p1;
DROP TABLE p2;
|
