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. -- xa_datasource 'wombat'; ij> --------------------------------------------- ----- a single connection and 1 phase commit ----- xa_connect ; ij> xa_start xa_noflags 0; ij> xa_getconnection; 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)> 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)> -- this prepare won't work since transaction 1 has been suspended - XA_PROTO xa_prepare 1; IJ ERROR: XAER_PROTO 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)> -- this assumes a resume 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)> -- NOTE: The following call to "xa_recover xa_startrscan" is apt to ----- return the result set rows in reverse order when changes to ----- the Derby engine affect the number of transactions that it takes ----- to create a database. The transactions are stored in a hash table ----- based on a global and local id, and when the number of transactions ----- changes, the (internal) local id can change, which may lead to a ----- change in the result set order. This order is determined by the ----- JVM's hashing algorithm. Examples of changes to the engine that ----- can affect this include ones that cause more commits or that ----- change the amount of data being stored, such as changes to the ----- metadata statements (which is what prompted this explanation in ----- the first place). Ultimately, the problem is that there is no ----- way to order the return values from "xa_recover" since it is an ----- ij internal statement, not SQL... 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 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)> disconnect; ij> --------------------------------------------- ----- 3 interleaving xa connections and a local connection ----- xa_start xa_noflags 4; ij> xa_end xa_suspend 4; ij> xa_start xa_noflags 5; ij> xa_end xa_suspend 5; ij> xa_start xa_noflags 6; ij> xa_end xa_suspend 6; ij> connect 'wombat' as local; ij(LOCAL)> select * from foo; A ----- 0 1 3 ij(LOCAL)> xa_start xa_resume 4; ij(LOCAL)> xa_getconnection; ij(XA)> insert into APP.foo values (4); 1 row inserted/updated/deleted ij(XA)> disconnect; ij> set connection local; ij(LOCAL)> insert into foo values (77); 1 row inserted/updated/deleted ij(LOCAL)> xa_end xa_suspend 4; ij(LOCAL)> xa_end xa_success 4; ij(LOCAL)> -- this getconnection should get a local connection ----- this has problems -----xa_getconnection; -----insert into APP.foo values (88); -----commit; -----disconnect; xa_start xa_resume 5; ij(LOCAL)> xa_getconnection; ij(XA)> insert into APP.foo values (5); 1 row inserted/updated/deleted ij(XA)> xa_end xa_success 5; ij(XA)> xa_start xa_resume 6; ij(XA)> insert into APP.foo values (6); 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 ----- (4 |ACTIVE |false |SKU |UserTransaction (5 |ACTIVE |false |SKU |UserTransaction (6 |ACTIVE |false |SKU |UserTransaction ij(XA)> xa_commit xa_1Phase 4; ij(XA)> insert into APP.foo values (6); 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 ----- (5 |ACTIVE |false |SKU |UserTransaction (6 |ACTIVE |false |SKU |UserTransaction ij(XA)> xa_end xa_fail 6; IJ ERROR: XA_RBROLLBACK ij(XA)> xa_rollback 6; ij(XA)> xa_start xa_join 5; ij(XA)> select * from APP.global_xactTable where gxid is not null order by gxid; GXID |STATUS |READ& |USERNAME |TYPE ----- (5 |ACTIVE |false |SKU |UserTransaction ij(XA)> select * from APP.foo; A ----- 0 1 3 4 77 5 ij(XA)> drop table APP.foo; 0 rows inserted/updated/deleted ij(XA)> xa_end xa_success 5; ij(XA)> xa_prepare 5; ij(XA)> xa_commit xa_2Phase 5; ij(XA)> --------------------------------------------- ----- Test procedure with server-side JDBC ----- ----- local transaction create table t1(i int not null primary key, b char(15)); 0 rows inserted/updated/deleted ij(XA)> insert into t1 values (1,'one'), (2, 'two'), (3,'three'); 3 rows inserted/updated/deleted ij(XA)> create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'; 0 rows inserted/updated/deleted ij(XA)> call DRS(1); I |B ----- 1 |one ij(XA)> drop table t1; 0 rows inserted/updated/deleted ij(XA)> drop procedure DRS; 0 rows inserted/updated/deleted ij(XA)> commit; ij(XA)> --- global transaction xa_start xa_noflags 6; ij(XA)> create table t1(i int not null primary key, b char(15)); 0 rows inserted/updated/deleted ij(XA)> insert into t1 values (1,'one'), (2, 'two'), (3,'three'); 3 rows inserted/updated/deleted ij(XA)> create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'; 0 rows inserted/updated/deleted ij(XA)> call DRS(1); I |B ----- 1 |one ij(XA)> drop procedure DRS; 0 rows inserted/updated/deleted ij(XA)> drop table t1; 0 rows inserted/updated/deleted ij(XA)> drop view APP.global_xacttable; 0 rows inserted/updated/deleted ij(XA)> drop schema sku restrict; 0 rows inserted/updated/deleted ij(XA)> xa_end xa_success 6; ij(XA)> xa_commit xa_1Phase 6; ij(XA)>