| 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> |
