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
|
ij> xa_datasource 'wombat';
ij> ---------------------------------------------
----- a single connection and 1 phase commit
-----
xa_connect ;
ij> xa_start xa_noflags 0;
ij> xa_getconnection;
ij(XA)> -- Global transactions can not have hold cursor over commit. And hence we need to make sure the holdability is false for all jdks
----- In jdk13 and lower, this Brokered Connection has its holdability false over commit so we are fine.
----- In jdk14 and higher, this Brokered Connection has its holdability true over commit. In order to set it to false, we have NoHoldForConnection
----- NoHoldForConnection uses setHoldability api on Connection to set the holdability to false. But this api exists only for jdk14 and higher
----- And that is why, in jkd13 master, we see an exception nosuchmethod
NoHoldForConnection;
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)> insert into foo values (0);
1 row inserted/updated/deleted
ij(XA)> select * from foo;
A
-----
0
ij(XA)> run resource '/org/apache/derbyTesting/functionTests/tests/store/global_xactTable.view';
ij(XA)> 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)> select * from global_xactTable where gxid is not null order by gxid;
GXID |STATUS |READ& |USERNAME |TYPE
-----
(0 |ACTIVE |false |APP |UserTransaction
ij(XA)> xa_end xa_success 0;
ij(XA)> xa_commit xa_1phase 0;
ij(XA)> xa_datasource 'wombat' shutdown;
ERROR 08006: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08006, SQLERRMC: Database 'wombat' shutdown.
ij(XA)> ---------------------------------------------
----- two interleaving connections and prepare/commit prepare/rollback
-----
xa_datasource 'wombat';
ij(XA)> xa_connect user 'sku' password 'testxa' ;
ij(XA)> xa_start xa_noflags 1;
ij(XA)> xa_getconnection;
ij(XA)> -- Global transactions can not have hold cursor over commit. And hence we need to make sure the holdability is false for all jdks
----- In jdk13 and lower, this Brokered Connection has its holdability false over commit so we are fine.
----- In jdk14 and higher, this Brokered Connection has its holdability true over commit. In order to set it to false, we have NoHoldForConnection
----- NoHoldForConnection uses setHoldability api on Connection to set the holdability to false. But this api exists only for jdk14 and higher
----- And that is why, in jkd13 master, we see an exception nosuchmethod
NoHoldForConnection;
ij(XA)> insert into APP.foo values (1);
1 row inserted/updated/deleted
ij(XA)> xa_end xa_suspend 1;
ij(XA)> xa_start xa_noflags 2;
ij(XA)> insert into APP.foo values (2);
1 row inserted/updated/deleted
ij(XA)> xa_end xa_suspend 2;
ij(XA)> xa_start xa_resume 1;
ij(XA)> insert into APP.foo values (3);
1 row inserted/updated/deleted
ij(XA)> xa_end xa_suspend 1;
ij(XA)> xa_start xa_resume 2;
ij(XA)> insert into APP.foo values (4);
1 row inserted/updated/deleted
ij(XA)> select * from APP.global_xactTable where gxid is not null order by gxid;
GXID |STATUS |READ& |USERNAME |TYPE
-----
(1 |ACTIVE |false |SKU |UserTransaction
(2 |ACTIVE |false |SKU |UserTransaction
ij(XA)> xa_end xa_success 2;
ij(XA)> xa_start xa_resume 1;
ij(XA)> insert into APP.foo values(5);
1 row inserted/updated/deleted
ij(XA)> xa_end xa_success 1;
ij(XA)> xa_prepare 1;
ij(XA)> xa_prepare 2;
ij(XA)> -- both transactions should be prepared
select * from APP.global_xactTable where gxid is not null order by gxid;
GXID |STATUS |READ& |USERNAME |TYPE
-----
(1 |PREPARED |false |SKU |UserTransaction
(2 |PREPARED |false |SKU |UserTransaction
ij(XA)> xa_recover xa_startrscan;
Recovered 2 in doubt transactions
Transaction 1 : {ClientXid: formatID(2), gtrid_length(6), bqual_length(6), data(776F6D62 6174776F 6D626174)}
Transaction 2 : {ClientXid: formatID(1), gtrid_length(6), bqual_length(6), data(776F6D62 6174776F 6D626174)}
ij(XA)> xa_recover xa_noflags;
Recovered 0 in doubt transactions
ij(XA)> xa_commit xa_2Phase 1;
ij(XA)> xa_rollback 2;
ij(XA)> -- check results
xa_start xa_noflags 3;
ij(XA)> select * from APP.global_xactTable where gxid is not null order by gxid;
GXID |STATUS |READ& |USERNAME |TYPE
-----
(3 |IDLE |NULL |SKU |UserTransaction
ij(XA)> select * from APP.foo;
A
-----
0
1
3
5
ij(XA)> xa_end xa_success 3;
ij(XA)> xa_prepare 3;
ij(XA)> -- should fail with XA_NOTA because we prepared a read only transaction
xa_commit xa_1Phase 3;
IJ ERROR: XAER_NOTA
ij(XA)> -- DERBY-246 xa_end after connection close should be ok.
----- Also to reuse xaconnection in the same global transaction
xa_getconnection;
ij(XA)> xa_start xa_noflags 4;
ij(XA)> create table APP.derby246 (i int);
0 rows inserted/updated/deleted
ij(XA)> insert into APP.derby246 values(1);
1 row inserted/updated/deleted
ij(XA)> disconnect;
ij> xa_getconnection;
ij(XA)> insert into APP.derby246 values(2);
1 row inserted/updated/deleted
ij(XA)> disconnect;
ij> xa_end xa_success 4;
ij> xa_prepare 4;
ij> xa_commit xa_2phase 4;
ij> -- now connect with a local connection to make sure locks are released
----- and our values are there. Should see two rows
connect 'wombat';
ij(CONNECTION1)> select * from APP.derby246;
I
-----
1
2
ij(CONNECTION1)> disconnect;
ij>
|
