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
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
|
#
# Test A Outline:
# ===============
#
# This test tests the scenario for MW-369 where a new child table
# row referring to parent table row is inserted concurrently from
# another node while the transaction which tries to delete a
# referred row from the parent table is committing.
#
# The p table will originally have rows (1, 0), (2, 0).
# The c table will be empty.
#
# A new row (1, 1) pointing to parent row (1, 0) is inserted from
# connection node_2, the transaction which tries to remove the
# parent row (1, 0) is run from connection node_1.
#
# Expected outcome:
# ================
#
# The transaction on node_1 will fail. The parent table will contain
# rows (1, 0), (2, 0) and the child table will contain row (1, 1).
#
--source include/galera_cluster.inc
--source include/have_innodb.inc
--source include/have_debug_sync.inc
--source suite/galera/include/galera_have_debug_sync.inc
CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER,
CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1)) ;
INSERT INTO p VALUES (1, 0);
INSERT INTO p VALUES (2, 0);
--let $mw_369_parent_query = DELETE FROM p WHERE f1 = 1
--let $mw_369_child_query = INSERT INTO c VALUES (1, 1)
#
# we must open connection node_1a here, MW-369.inc will use it later
#
--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
--source MW-369.inc
# Commit fails
--connection node_1
--error ER_LOCK_DEADLOCK
--reap
--connection node_2
SELECT * FROM p;
SELECT * FROM c;
DROP TABLE c;
DROP TABLE p;
#
# Test B Outline:
# ===============
#
# This test tests the scenario for MW-369 where a existing
# child table row is updated concurrently from another node
# with a transaction which updates the parent table.
#
# The p table will originally have rows (1, 0), (2, 0).
# The c table will originally have rows (1, 1, 0) which points
# to parent table row (1, 0).
#
# Expected outcome:
# ================
#
# Both updates should succeed since they are done to separate tables and
# rows. The parent table will contain rows (1, 1), (2, 0). The child
# table will contain row (1, 1, 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,
f2 INTEGER,
CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1)) ;
INSERT INTO p VALUES (1, 0);
INSERT INTO p VALUES (2, 0);
INSERT INTO c VALUES (1, 1, 0);
--let mw_369_parent_query = UPDATE p SET f2 = 1 WHERE f1 = 1
--let $mw_369_child_query = UPDATE c SET f2 = 1 WHERE f1 = 1
--source MW-369.inc
# Commit succeeds
--connection node_1
--reap
--connection node_2
SELECT * FROM p;
SELECT * FROM c;
DROP TABLE c;
DROP TABLE p;
#
# Test C Outline:
# ===============
#
# This test tests the scenario for MW-369 where a child table row is
# deleted concurrently from the other node while a transaction updates
# the parent table referred by the child table row.
#
# The p table will originally have rows (1, 0), (2, 0)
# The c table will originally have row (1, 1) which points to parent
# table row (1, 0).
#
# A row (1, 1) pointing to parent row (1, 0) is deleted from
# connection node_2, the transaction which tries to update the
# parent row (1, 0) is run from connection node_1.
#
# Expected Outcome:
# ================
# Both operations on node_1 and node_2 should succeed without conflicts.
# The parent table should contain values (1, 1), (2, 0) and the child
# table should be empty.
--connection node_1
CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER,
CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1)) ;
INSERT INTO p VALUES (1, 0);
INSERT INTO p VALUES (2, 0);
INSERT INTO c VALUES (1, 1);
--let $mw_369_parent_query = UPDATE p SET f2 = 1 WHERE f1 = 1
--let $mw_369_child_query = DELETE FROM c WHERE f1 = 1
--source MW-369.inc
# Commit succeeds
--connection node_1
--reap
--connection node_2
SELECT * FROM p;
SELECT * FROM c;
DROP TABLE c;
DROP TABLE p;
#
# Test D Outline:
# ===============
#
# This test is similar to test A, where parent row is deleted while a child row
# is inserted simultaneously on node 2. However, in this test case the FK
# constraint's target column is a unique key, and parent row is not delete,
# but this key value is changed so that insert on node 2 will cause FK
# violation
#
# The p table will originally have rows (1, 0)
# The c table will originally be empty
#
# in node_1, parent row is updated to value (1,1)
# A row (1, 0) pointing to the old version of parent row (1, 0) is inserted
# in connection node_2
#
# Expected Outcome:
# ================
# This is a true conflict and one transaciton must abort. In this case it is node_1
# transaction, which was scheduled later.
# Parent table should have row (1,0)
# child table should have row (1,0)
#
CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER UNIQUE KEY) ENGINE=INNODB;
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER,
CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f2)) ;
INSERT INTO p VALUES (1, 0);
--let $mw_369_parent_query = UPDATE p SET f2 = 1 WHERE f1 = 1
--let $mw_369_child_query = INSERT INTO c VALUES (1, 0);
--source MW-369.inc
# Commit fails
--connection node_1
--error ER_LOCK_DEADLOCK
--reap
--connection node_2
SELECT * FROM p;
SELECT * FROM c;
DROP TABLE c;
DROP TABLE p;
#
# Test E Outline:
# ===============
#
# This test is similar to test B, where parent row is deleted while a child row
# is updated simultaneously on node 2. However, in this test case the FK
# constraint has ON DELETE CASCADE option, and the delete on parent row will
# cascade a delete on child row as well. This will cause true conflict with
# connection node_2, which tries to update unrelated column on child table.
#
# The p table will originally have rows (1, 0), (2,0)
# The c table will originally have row (1,1,0)
#
# in node_1, parent row (1,0) is deleted and cascaded delete will happen on
# child table row (1,1,0).
# in connection node_2 child table row is update to value (1,1,1)
#
# Expected Outcome:
# ================
# This is a true conflict and one transaciton must abort. In this case it is node_1
# transaction, which was scheduled later.
# Parent table should have rows (1,0), (2,0)
# child table should have row (1,1,1)
#
CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER, f2 INTEGER,
CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1)
ON DELETE CASCADE) ;
INSERT INTO p VALUES (1, 0);
INSERT INTO p VALUES (2, 0);
INSERT INTO c VALUES (1, 1, 0);
--let $mw_369_parent_query = DELETE FROM p WHERE f1 = 1
--let $mw_369_child_query = UPDATE c SET f2 = 1 WHERE f1 = 1
--source MW-369.inc
# Commit fails
--connection node_1
--error ER_LOCK_DEADLOCK
--reap
--connection node_2
SELECT * FROM p;
SELECT * FROM c;
DROP TABLE c;
DROP TABLE p;
|
