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
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
|
ij> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
--
-- some negative test for error checking
--
xa_datasource 'wombat';
ij> xa_connect user 'negativeTest' password 'xxx';
ij> -- start new transaction
xa_start xa_noflags 0;
ij> -- ERROR: cannot start without end
xa_start xa_noflags 1;
IJ ERROR: XAER_PROTO
ij> xa_getconnection;
ij(XA)> -- ERROR: cannot get connection again
xa_getconnection;
ERROR XJ059: Cannot close a connection while a global transaction is still active.
ij(XA)> -- ERROR: connot commit/rollback an xa connection
commit;
ERROR XJ057: Cannot commit a global transaction using the Connection, commit processing must go thru XAResource interface.
ij(XA)> -- ERROR: connot commit/rollback an xa connection
rollback;
ERROR XJ058: Cannot rollback a global transaction using the Connection, commit processing must go thru XAResource interface.
ij(XA)> drop table APP.negative;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'APP.NEGATIVE' because it does not exist.
ij(XA)> create table APP.negative (a char(10), b int);
0 rows inserted/updated/deleted
ij(XA)> create unique index negativei on APP.negative(b);
0 rows inserted/updated/deleted
ij(XA)> run resource '/org/apache/derbyTesting/functionTests/tests/store/global_xactTable.view';
ij(XA)> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
create view global_xactTable as
select
cast(global_xid as char(2)) as gxid,
status,
case when first_instant is NULL then 'NULL' else 'false' end as readOnly,
cast (username as char(10)) as username,
type
from syscs_diag.transaction_table;
0 rows inserted/updated/deleted
ij(XA)> insert into APP.negative values ('xyz', 1);
1 row inserted/updated/deleted
ij(XA)> select * from APP.negative;
A |B
----------------------
xyz |1
ij(XA)> -- ERROR: cannot commit/prepare/rollback without end
xa_commit xa_1phase 0;
IJ ERROR: XAER_PROTO
ij(XA)> -- ERROR: cannot commit/prepare/rollback without end
xa_rollback 0;
IJ ERROR: XAER_PROTO
ij(XA)> -- ERROR: cannot commit/prepare/rollback without end
xa_prepare 0;
IJ ERROR: XAER_PROTO
ij(XA)> -- OK suspend it
xa_end xa_suspend 0;
ij(XA)> -- ERROR: duplicate xid
xa_start xa_noflags 0;
IJ ERROR: XAER_DUPID
ij(XA)> -- ERROR: cannot commit/prepare/rollback with suspended
xa_commit xa_1phase 0;
IJ ERROR: XAER_PROTO
ij(XA)> -- ERROR: cannot commit/prepare/rollback with suspended
xa_rollback 0;
IJ ERROR: XAER_PROTO
ij(XA)> -- ERROR: cannot commit/prepare/rollback with suspended
xa_prepare 0;
IJ ERROR: XAER_PROTO
ij(XA)> -- ERROR: cannot commit/prepare/rollback with suspended
xa_commit xa_2phase 0;
IJ ERROR: XAER_PROTO
ij(XA)> xa_end xa_success 0;
ij(XA)> xa_prepare 0;
ij(XA)> xa_commit xa_2phase 0;
ij(XA)> -- should be able to use this xid again
xa_start xa_noflags 0;
ij(XA)> -- ERROR: cannot start without end
xa_start xa_noflags 0;
IJ ERROR: XAER_PROTO
ij(XA)> -- ERROR: duplicate key exception, statement level rollback
insert into APP.negative values ('rollback', 1);
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'NEGATIVEI' defined on 'NEGATIVE'.
ij(XA)> select * from APP.negative;
A |B
----------------------
xyz |1
ij(XA)> insert into APP.negative values ('ok', 2);
1 row inserted/updated/deleted
ij(XA)> select * from global_xactTable order by gxid, status, username, type;
GXID|STATUS |READ&|USERNAME |TYPE
-------------------------------------------------------------
(0 |ACTIVE |false|NEGATIVETE|UserTransaction
WARNING 01004: Data truncation
WARNING 01004: Data truncation
NULL|IDLE |NULL |APP |UserTransaction
ij(XA)> disconnect;
ij> xa_end xa_fail 0;
IJ ERROR: XA_RBROLLBACK
ij> xa_start xa_noflags 2;
ij> xa_getconnection;
ij(XA)> insert into APP.negative values ('ok', 3);
1 row inserted/updated/deleted
ij(XA)> -- ERROR: cannot suspend some other xid
xa_end xa_suspend 3;
IJ ERROR: XAER_PROTO
ij(XA)> -- ERROR: cannot end some other xid while I am still attached
xa_end xa_success 0;
IJ ERROR: XAER_PROTO
ij(XA)> xa_end xa_suspend 2;
ij(XA)> -- ERROR: cannot join an xid I just suspended have to resume
xa_start xa_join 2;
IJ ERROR: XAER_PROTO
ij(XA)> xa_start xa_resume 2;
ij(XA)> xa_end xa_suspend 2;
ij(XA)> xa_rollback 0;
ij(XA)> -- ERROR: should not find this xid any more
xa_prepare 0;
IJ ERROR: XAER_NOTA
ij(XA)> select * from global_xactTable order by gxid, status, username, type;
GXID|STATUS |READ&|USERNAME |TYPE
-------------------------------------------------------------
(2 |ACTIVE |false|NEGATIVETE|UserTransaction
WARNING 01004: Data truncation
WARNING 01004: Data truncation
WARNING 01004: Data truncation
NULL|IDLE |NULL |APP |UserTransaction
NULL|IDLE |NULL |NEGATIVETE|UserTransaction
ij(XA)> xa_end xa_success 2;
ij(XA)> disconnect;
ij> -- ERROR: can only join a successful branch, not resume
xa_start xa_resume 2;
IJ ERROR: XAER_PROTO
ij> -- this is OK
xa_start xa_join 2;
ij> xa_getconnection;
ij(XA)> -- ERROR: another dup
insert into APP.negative values ('rollback', 3);
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'NEGATIVEI' defined on 'NEGATIVE'.
ij(XA)> xa_end xa_suspend 2;
ij(XA)> xa_end xa_success 2;
ij(XA)> -- ERROR: cannot call fail now
xa_end xa_fail 2;
IJ ERROR: XAER_PROTO
ij(XA)> -- rollback is OK
xa_rollback 2;
ij(XA)> -- ERROR: cannot join something that is not there
xa_start xa_join 2;
IJ ERROR: XAER_NOTA
ij(XA)> -- ERROR: cannot join something that is not there
xa_start xa_resume 2;
IJ ERROR: XAER_NOTA
ij(XA)> -- start one
xa_start xa_noflags 1;
ij(XA)> -- ERROR: can only forget heuristically completed transaction
xa_forget 1;
IJ ERROR: XAER_PROTO
ij(XA)> delete from APP.negative;
1 row inserted/updated/deleted
ij(XA)> xa_end xa_success 1;
ij(XA)> -- ERROR: now try some bad flag
xa_start xa_suspend 1;
IJ ERROR: XAER_INVAL
ij(XA)> -- ERROR: now try some bad flag
xa_start xa_fail 1;
IJ ERROR: XAER_INVAL
ij(XA)> xa_prepare 1;
ij(XA)> -- can only forget heuristically completed transaction
xa_forget 1;
IJ ERROR: XAER_PROTO
ij(XA)> xa_start xa_noflags 2;
ij(XA)> -- ERROR: deadlock, transaction trashed
select * from APP.negative;
A |B
----------------------
ERROR 40XL1: A lock could not be obtained within the time requested
ij(XA)> -- ERROR: should have no connection underneath
select * from APP.negative;
A |B
----------------------
ERROR 40XL1: A lock could not be obtained within the time requested
ij(XA)> -- ERROR: should have no connection underneath and xid 2 is gone
xa_end xa_suspend 2;
IJ ERROR: XA_RBTIMEOUT
ij(XA)> -- ERROR: should have no connection underneath and xid 2 is gone
xa_end xa_fail 2;
IJ ERROR: XA_RBTIMEOUT
ij(XA)> xa_rollback 2;
ij(XA)> disconnect;
ij> xa_start xa_noflags 3;
ij> xa_getconnection;
ij(XA)> select * from global_xactTable order by gxid, status, username, type;
GXID|STATUS |READ&|USERNAME |TYPE
-------------------------------------------------------------
(1 |PREPARED|false|NEGATIVETE|UserTransaction
WARNING 01004: Data truncation
WARNING 01004: Data truncation
WARNING 01004: Data truncation
WARNING 01004: Data truncation
(3 |IDLE |NULL |NEGATIVETE|UserTransaction
NULL|IDLE |NULL |APP |UserTransaction
ij(XA)> drop table foo;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'FOO' because it does not exist.
ij(XA)> create table foo (a int);
0 rows inserted/updated/deleted
ij(XA)> xa_end xa_suspend 3;
ij(XA)> -- ERROR: cannot join a prepared transaction
xa_start xa_join 1;
IJ ERROR: XAER_PROTO
ij(XA)> -- ERROR: cannot resume a prepared transaction
xa_start xa_resume 1;
IJ ERROR: XAER_PROTO
ij(XA)> -- ERROR: bad flag
xa_start xa_fail 1;
IJ ERROR: XAER_INVAL
ij(XA)> -- ERROR: bad flag
xa_start xa_noflags 1;
IJ ERROR: XAER_DUPID
ij(XA)> -- rollback prepared transaction is OK
xa_rollback 1;
ij(XA)> -- ERROR: dup id
xa_start xa_noflags 3;
IJ ERROR: XAER_DUPID
ij(XA)> xa_start xa_resume 3;
ij(XA)> -- now that 1 is rolled back, this should succeed
select * from APP.negative;
A |B
----------------------
xyz |1
ij(XA)> select * from global_xactTable order by gxid, status, username, type;
GXID|STATUS |READ&|USERNAME |TYPE
-------------------------------------------------------------
(3 |ACTIVE |false|NEGATIVETE|UserTransaction
WARNING 01004: Data truncation
WARNING 01004: Data truncation
NULL|IDLE |NULL |APP |UserTransaction
ij(XA)> -- ERROR: bad flag
xa_end xa_noflags 3;
IJ ERROR: XAER_INVAL
ij(XA)> xa_end xa_fail 3;
IJ ERROR: XA_RBROLLBACK
ij(XA)> xa_rollback 3;
ij(XA)> -- ensure switching back and forward does not commit
-- the xact due to the commit in setAutoCommit();
AUTOCOMMIT ON;
ij(XA)> create table t44g(a int);
0 rows inserted/updated/deleted
ij(XA)> insert into t44g values 1,2;
2 rows inserted/updated/deleted
ij(XA)> select * from t44g where a > 4000;
A
-----------
ij(XA)> create table t44(i int);
0 rows inserted/updated/deleted
ij(XA)> xa_start xa_noflags 44;
ij(XA)> insert into t44g values(4400);
1 row inserted/updated/deleted
ij(XA)> insert into t44g values(4401);
1 row inserted/updated/deleted
ij(XA)> xa_end xa_suspend 44;
ij(XA)> values (1,2,3);
1 |2 |3
-----------------------------------
1 |2 |3
ij(XA)> commit;
ij(XA)> AUTOCOMMIT OFF;
ij(XA)> insert into t44 values(1);
1 row inserted/updated/deleted
ij(XA)> insert into t44 values(2);
1 row inserted/updated/deleted
ij(XA)> commit;
ij(XA)> insert into t44 values(3);
1 row inserted/updated/deleted
ij(XA)> insert into t44 values(4);
1 row inserted/updated/deleted
ij(XA)> rollback;
ij(XA)> AUTOCOMMIT ON;
ij(XA)> -- fail with lock issues
select * from t44g;
A
-----------
ERROR 40XL1: A lock could not be obtained within the time requested
ij(XA)> xa_start xa_resume 44;
ij(XA)> insert into t44g values(4500);
1 row inserted/updated/deleted
ij(XA)> insert into t44g values(4501);
1 row inserted/updated/deleted
ij(XA)> xa_end xa_success 44;
ij(XA)> insert into t44 values(5);
1 row inserted/updated/deleted
ij(XA)> insert into t44 values(6);
1 row inserted/updated/deleted
ij(XA)> commit;
ij(XA)> AUTOCOMMIT OFF;
ij(XA)> insert into t44 values(7);
1 row inserted/updated/deleted
ij(XA)> insert into t44 values(8);
1 row inserted/updated/deleted
ij(XA)> commit;
ij(XA)> AUTOCOMMIT ON;
ij(XA)> xa_start xa_join 44;
ij(XA)> select * from t44g where a > 4000;
A
-----------
4400
4401
4500
4501
ij(XA)> xa_end xa_success 44;
ij(XA)> -- fail with lock issues
select * from t44g;
A
-----------
ERROR 40XL1: A lock could not be obtained within the time requested
ij(XA)> xa_rollback 44;
ij(XA)> -- should be empty if no commit occurred in the middle;
select * from t44g where a > 4000;
A
-----------
ij(XA)> select * from t44;
I
-----------
1
2
5
6
7
8
ij(XA)>
|
