Debian Codegraph @

mysql-test/suite/galera/t/galera_sequences_transaction.test

raw
1 --source include/galera_cluster.inc
2 --source include/have_sequence.inc
3
4 --disable_ps2_protocol
5 #
6 # Case 1: Separate transactions from few connections
7 #
8 --connection node_1
9 CREATE SEQUENCE s INCREMENT=0 CACHE=5 ENGINE=InnoDB;
10 CREATE TABLE t1 (f1 INT PRIMARY KEY DEFAULT NEXTVAL(s), f2 INT) ENGINE=InnoDB;
11
12 --connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
13 --connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2
14
15 --connection node_1
16 BEGIN;
17 INSERT INTO t1(f2) values (1);
18 INSERT INTO t1(f2) values (1);
19 INSERT INTO t1(f2) values (1);
20 INSERT INTO t1(f2) values (1);
21 INSERT INTO t1(f2) values (1);
22 INSERT INTO t1(f2) values (1);
23 INSERT INTO t1(f2) values (1);
24 INSERT INTO t1(f2) values (1);
25 INSERT INTO t1(f2) values (1);
26 INSERT INTO t1(f2) values (1);
27 COMMIT;
28
29 --connection node_2
30 BEGIN;
31 INSERT INTO t1(f2) values (1);
32 INSERT INTO t1(f2) values (1);
33 INSERT INTO t1(f2) values (1);
34 INSERT INTO t1(f2) values (1);
35 INSERT INTO t1(f2) values (1);
36 INSERT INTO t1(f2) values (1);
37 INSERT INTO t1(f2) values (1);
38 INSERT INTO t1(f2) values (1);
39 INSERT INTO t1(f2) values (1);
40 INSERT INTO t1(f2) values (1);
41 COMMIT;
42
43 --connection node_2a
44 BEGIN;
45 INSERT INTO t1(f2) values (1);
46 INSERT INTO t1(f2) values (1);
47 INSERT INTO t1(f2) values (1);
48 INSERT INTO t1(f2) values (1);
49 INSERT INTO t1(f2) values (1);
50 INSERT INTO t1(f2) values (1);
51 INSERT INTO t1(f2) values (1);
52 INSERT INTO t1(f2) values (1);
53 INSERT INTO t1(f2) values (1);
54 INSERT INTO t1(f2) values (1);
55 COMMIT;
56
57 --connection node_1a
58 BEGIN;
59 INSERT INTO t1(f2) values (1);
60 INSERT INTO t1(f2) values (1);
61 INSERT INTO t1(f2) values (1);
62 INSERT INTO t1(f2) values (1);
63 INSERT INTO t1(f2) values (1);
64 INSERT INTO t1(f2) values (1);
65 INSERT INTO t1(f2) values (1);
66 INSERT INTO t1(f2) values (1);
67 INSERT INTO t1(f2) values (1);
68 INSERT INTO t1(f2) values (1);
69 COMMIT;
70
71 --connection node_2
72 SELECT LASTVAL(s);
73 --connection node_1
74 SELECT LASTVAL(s);
75 --connection node_2a
76 SELECT LASTVAL(s);
77 --connection node_1a
78 SELECT LASTVAL(s);
79
80 --connection node_1
81 SELECT * FROM t1;
82 --connection node_2
83 SELECT * FROM t1;
84
85 --connection node_1
86 DROP TABLE t1;
87 DROP SEQUENCE s;
88
89 #
90 # Case 2: All rollback
91 #
92 --connection node_1
93 CREATE SEQUENCE s INCREMENT=0 CACHE=5 ENGINE=InnoDB;
94 CREATE TABLE t1 (f1 INT PRIMARY KEY DEFAULT NEXTVAL(s), f2 INT) ENGINE=InnoDB;
95
96 --connection node_1
97 BEGIN;
98 INSERT INTO t1(f2) values (1);
99 INSERT INTO t1(f2) values (1);
100 INSERT INTO t1(f2) values (1);
101 INSERT INTO t1(f2) values (1);
102 INSERT INTO t1(f2) values (1);
103 INSERT INTO t1(f2) values (1);
104 INSERT INTO t1(f2) values (1);
105 INSERT INTO t1(f2) values (1);
106 INSERT INTO t1(f2) values (1);
107 INSERT INTO t1(f2) values (1);
108 ROLLBACK;
109
110 --connection node_2
111 BEGIN;
112 INSERT INTO t1(f2) values (1);
113 INSERT INTO t1(f2) values (1);
114 INSERT INTO t1(f2) values (1);
115 INSERT INTO t1(f2) values (1);
116 INSERT INTO t1(f2) values (1);
117 INSERT INTO t1(f2) values (1);
118 INSERT INTO t1(f2) values (1);
119 INSERT INTO t1(f2) values (1);
120 INSERT INTO t1(f2) values (1);
121 INSERT INTO t1(f2) values (1);
122 ROLLBACK;
123
124 --connection node_2a
125 BEGIN;
126 INSERT INTO t1(f2) values (1);
127 INSERT INTO t1(f2) values (1);
128 INSERT INTO t1(f2) values (1);
129 INSERT INTO t1(f2) values (1);
130 INSERT INTO t1(f2) values (1);
131 INSERT INTO t1(f2) values (1);
132 INSERT INTO t1(f2) values (1);
133 INSERT INTO t1(f2) values (1);
134 INSERT INTO t1(f2) values (1);
135 INSERT INTO t1(f2) values (1);
136 ROLLBACK;
137
138 --connection node_1a
139 BEGIN;
140 INSERT INTO t1(f2) values (1);
141 INSERT INTO t1(f2) values (1);
142 INSERT INTO t1(f2) values (1);
143 INSERT INTO t1(f2) values (1);
144 INSERT INTO t1(f2) values (1);
145 INSERT INTO t1(f2) values (1);
146 INSERT INTO t1(f2) values (1);
147 INSERT INTO t1(f2) values (1);
148 INSERT INTO t1(f2) values (1);
149 INSERT INTO t1(f2) values (1);
150 ROLLBACK;
151
152 --connection node_2
153 SELECT LASTVAL(s);
154 --connection node_1
155 SELECT LASTVAL(s);
156 --connection node_2a
157 SELECT LASTVAL(s);
158 --connection node_1a
159 SELECT LASTVAL(s);
160
161 --connection node_1
162 SELECT * FROM t1;
163 --connection node_2
164 SELECT * FROM t1;
165
166 --connection node_1
167 DROP TABLE t1;
168 DROP SEQUENCE s;
169 #
170 # Case 3: Mixed transactions
171 #
172 --connection node_1
173 CREATE SEQUENCE s INCREMENT=0 CACHE=5 ENGINE=InnoDB;
174 CREATE TABLE t1 (f1 INT PRIMARY KEY DEFAULT NEXTVAL(s), f2 INT) ENGINE=InnoDB;
175
176 --connection node_1
177 BEGIN;
178 INSERT INTO t1(f2) values (1);
179 INSERT INTO t1(f2) values (1);
180 INSERT INTO t1(f2) values (1);
181 INSERT INTO t1(f2) values (1);
182 INSERT INTO t1(f2) values (1);
183 INSERT INTO t1(f2) values (1);
184 INSERT INTO t1(f2) values (1);
185 INSERT INTO t1(f2) values (1);
186 INSERT INTO t1(f2) values (1);
187 INSERT INTO t1(f2) values (1);
188
189 --connection node_1a
190 BEGIN;
191 INSERT INTO t1(f2) values (1);
192 INSERT INTO t1(f2) values (1);
193 INSERT INTO t1(f2) values (1);
194 INSERT INTO t1(f2) values (1);
195 INSERT INTO t1(f2) values (1);
196 INSERT INTO t1(f2) values (1);
197 INSERT INTO t1(f2) values (1);
198 INSERT INTO t1(f2) values (1);
199 INSERT INTO t1(f2) values (1);
200 INSERT INTO t1(f2) values (1);
201
202 --connection node_2a
203 BEGIN;
204 INSERT INTO t1(f2) values (1);
205 INSERT INTO t1(f2) values (1);
206 INSERT INTO t1(f2) values (1);
207 INSERT INTO t1(f2) values (1);
208 INSERT INTO t1(f2) values (1);
209 INSERT INTO t1(f2) values (1);
210 INSERT INTO t1(f2) values (1);
211 INSERT INTO t1(f2) values (1);
212 INSERT INTO t1(f2) values (1);
213 INSERT INTO t1(f2) values (1);
214
215 --connection node_2
216 BEGIN;
217 INSERT INTO t1(f2) values (1);
218 INSERT INTO t1(f2) values (1);
219 INSERT INTO t1(f2) values (1);
220 INSERT INTO t1(f2) values (1);
221 INSERT INTO t1(f2) values (1);
222 INSERT INTO t1(f2) values (1);
223 INSERT INTO t1(f2) values (1);
224 INSERT INTO t1(f2) values (1);
225 INSERT INTO t1(f2) values (1);
226 INSERT INTO t1(f2) values (1);
227
228 --connection node_1
229 COMMIT;
230 --connection node_1a
231 ROLLBACK;
232 --connection node_2
233 COMMIT;
234 --connection node_2a
235 ROLLBACK;
236
237 --connection node_2
238 SELECT LASTVAL(s);
239 --connection node_1
240 SELECT LASTVAL(s);
241 --connection node_2a
242 SELECT LASTVAL(s);
243 --connection node_1a
244 SELECT LASTVAL(s);
245
246 --connection node_1
247 SELECT * FROM t1;
248 --connection node_2
249 SELECT * FROM t1;
250
251 --connection node_1
252 DROP TABLE t1;
253 DROP SEQUENCE s;