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
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
|
#
# 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
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;
--echo #
--echo # Start of 10.4 tests
--echo #
#
# Test F Outline:
# ===============
#
# Test two concurrent INSERTs on the child table.
#
# The pf table will originally have row (1)
# The cf table will originally be empty
#
# A new row (10, 1) pointing to parent row (1) is inserted from
# connection node_2. A transaction which tries to INSERT another child
# row (20, 1), pointing to the same parent, is run from connection node_1.
#
# Expected Outcome:
# =================
# Both INSERTs should succeed since they don't modify the common parent
# key.
#
# At the end of the test:
# parent table should have row (1)
# child table should have rows (10, 1), (20, 1)
--connection node_1
CREATE TABLE pf (f1 INTEGER PRIMARY KEY) ENGINE=INNODB;
CREATE TABLE cf (
f1 INTEGER PRIMARY KEY,
p_id INTEGER,
CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES pf (f1)
);
INSERT INTO pf VALUES (1);
# This is run on node1:
--let $mw_369_parent_query = INSERT INTO cf (f1, p_id) VALUES (10, 1)
# This is run on node2:
--let $mw_369_child_query = INSERT INTO cf (f1, p_id) VALUES (20, 1)
--source MW-369.inc
--connection node_1
--reap
--connection node_2
SELECT * FROM pf;
SELECT * FROM cf;
DROP TABLE cf;
DROP TABLE pf;
#
# Test G Outline:
# ===============
#
# This test is similar to test B where a existing
# child table row is updated concurrently from another node
# with a transaction which updates the parent table, except
# that here the child table row is inserted, not updated.
#
# The pg table will originally have rows (1, 0), (2, 0).
# The cg table will originally be empty
#
# Expected outcome:
# ================
#
# Both UPDATE and INSERT should succeed since they are done to separate tables
# and UPDATE to parent row does not touch the foreign key referenced by the
# child row INSERT. The parent table shall contain rows (1, 1), (2, 0).
# The child table shall contain row (1, 1, 0) which points to parent table
# row (1, 0).
#
--connection node_1
CREATE TABLE pg (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
CREATE TABLE cg (f1 INTEGER PRIMARY KEY, p_id INTEGER,
f2 INTEGER,
CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES pg (f1)) ;
INSERT INTO pg VALUES (1, 0);
INSERT INTO pg VALUES (2, 0);
--let mw_369_parent_query = UPDATE pg SET f2 = 1 WHERE f1 = 1
--let $mw_369_child_query = INSERT INTO cg VALUES (1, 1, 0)
--source MW-369.inc
# Commit succeeds
--connection node_1
--reap
--connection node_2
SELECT * FROM pg;
SELECT * FROM cg;
DROP TABLE cg;
DROP TABLE pg;
|
