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
|
--source include/have_innodb.inc
--source include/have_debug.inc
--source include/have_debug_sync.inc
--source include/galera_cluster.inc
#
# This test will reproduce regressions of bugs
# https://github.com/codership/mysql-wsrep/issues/335
# https://github.com/codership/mysql-wsrep/issues/336
#
# The test will issue duplicate inserts into a table, which has foreign key
# constraint for a parent table.
# The inserts happen in separate nodes, and the
# The test outline is as follows:
# 1. in node 1, an update is done for the FK parent row, in a transaction,
# which is left open. The purpose of this is just to establish a X lock on
# the parent row.
# 2. in node 1, an insert for the child table is sent. The insert will have to wait
# for the parent row X lock, because it needs S lock on the same row.
# However, we have set an DBUG sync point just before the insert will call for
# innodb suspend
# 3. in node 2, a similar insert is issued. This will replicate to node 1 and try to
# abort the conflicting update and blocked insert
# 4. the inserts dbug sync point is relased, and insert should continue and abort
# gracefully
# 5. update is continued to commit, and it should also observe the deadlock
#
# This test is run in 3 phases:
#
# 1. with plain SQL statement
# 2. as SQL prepared statements
# 3. as SQl stored procedures
#
# The test phase 3 is for reproducing the issue in bug #336 specifically
#
#
# create the test tables and foreign key constraint between them
#
CREATE TABLE user(id int primary key, j int) ENGINE=InnoDB;
CREATE TABLE user_session(id int primary key, fk1 int, fk2 int) ENGINE=InnoDB;
alter table user_session add foreign key (fk1) references user(id);
# populate a few initial rows
INSERT INTO user values (1,0), (2,0), (3,0), (4,0);
INSERT INTO user_session values (1,1,1);
#
# prepare test connections, for:
# update of parent row in node1
# insert of child row in node1
# insert of child row in node2
--connect node_1_u, 127.0.0.1, root, , test, $NODE_MYPORT_1
--connect node_1_i, 127.0.0.1, root, , test, $NODE_MYPORT_1
--connect node_2_i, 127.0.0.1, root, , test, $NODE_MYPORT_2
#
# test phase 1: plain SQL statements
#
--echo "Phase 1: plain SQL statements"
--connection node_1
let $counter=10;
let $sleep_period=1;
while($counter > 0)
{
--connection node_1_u
begin;
update user set j = j + 1 WHERE id > 0;
--connection node_1_i
set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins';
send insert into user_session(id,fk1,fk2) values (2, 2, 2);
--connection node_1
set debug_sync='now WAIT_FOR ins_waiting';
--connection node_2_i
insert into user_session(id,fk1,fk2) values (2, 2, 3);
--connection node_1
set debug_sync='now SIGNAL cont_ins';
--connection node_1_i
--error 0,ER_LOCK_DEADLOCK,ER_DUP_ENTRY
reap;
--connection node_1_u
--error 0,ER_LOCK_DEADLOCK
commit;
--connection node_1
--error 0,ER_LOCK_DEADLOCK
truncate user_session;
set debug_sync = reset;
dec $counter;
}
#
# test phase 2: prepared statements
#
--echo "Phase 2: prepared statements"
--connection node_1_u
prepare upd from 'update user set j = j + 1 WHERE id > 0';
--connection node_1_i
prepare ins1 from 'insert into user_session(id,fk1,fk2) values (2, 2, 2)';
--connection node_2_i
prepare ins2 from 'insert into user_session(id,fk1,fk2) values (2, 2, 3)';
--connection node_1
let $counter=10;
let $sleep_period=1;
while($counter > 0)
{
--connection node_1_u
begin;
execute upd;
#update user set j = j + 1 WHERE id > 0;
--connection node_1_i
set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins';
send execute ins1;
--connection node_1
set debug_sync='now WAIT_FOR ins_waiting';
--connection node_2_i
execute ins2;
--connection node_1
set debug_sync='now SIGNAL cont_ins';
--connection node_1_i
--error 0,ER_LOCK_DEADLOCK,ER_DUP_ENTRY
reap;
--connection node_1_u
--error 0,ER_LOCK_DEADLOCK
commit;
--connection node_1
--error 0,ER_LOCK_DEADLOCK
truncate user_session;
set debug_sync = reset;
dec $counter;
}
--connection node_1
drop table user_session,user;
|
