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. -- disconnect; ij> -- -- testing using an xaConnection alternately produce local and global -- transaction -- -- xa_datasource 'wombat' ; ij> xa_connect user 'sku' password 'testmorph'; ij> -- get a local connection thru the XAConnection xa_getconnection; ij> run resource '/org/apache/derbyTesting/functionTests/tests/store/global_xactTable.view'; ij> 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> select * from global_xactTable where gxid is not null order by gxid,username; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- ij> drop table foo; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'FOO' because it does not exist. ij> create table foo (a int); 0 rows inserted/updated/deleted ij> commit; ij> autocommit off; ij> insert into foo values (1); 1 row inserted/updated/deleted ij> select * from global_xactTable where gxid is not null order by gxid,username; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- ij> commit; ij> autocommit on; ij> insert into foo values (2); 1 row inserted/updated/deleted ij> select * from global_xactTable where gxid is not null order by gxid,username; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- ij> -- morph the connection to a global transaction xa_start xa_noflags 1; ij> select * from global_xactTable where gxid is not null order by gxid,username; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- (1 |IDLE |NULL |SKU |UserTransaction ij> insert into foo values (3); 1 row inserted/updated/deleted ij> -- disallowed commit; ERROR XJ057: Cannot commit a global transaction using the Connection, commit processing must go thru XAResource interface. ij> -- disallowed rollback; ERROR XJ058: Cannot rollback a global transaction using the Connection, commit processing must go thru XAResource interface. ij> -- disallowed autocommit on; ERROR XJ056: Cannot set AUTOCOMMIT ON when in an XA connection. ij> -- OK autocommit off; ij> select * from foo; A ----------- 1 2 3 ij> xa_end xa_success 1; ij> xa_prepare 1; ij> -- dup id xa_start xa_noflags 1; IJ ERROR: XAER_DUPID ij> xa_start xa_noflags 2; ij> -- still should disallow autommit; autocommit on; ERROR XJ056: Cannot set AUTOCOMMIT ON when in an XA connection. ij> -- still should disallow commit and rollback commit; ERROR XJ057: Cannot commit a global transaction using the Connection, commit processing must go thru XAResource interface. ij> rollback; ERROR XJ058: Cannot rollback a global transaction using the Connection, commit processing must go thru XAResource interface. ij> select * from global_xactTable where gxid is not null order by gxid,username; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- (1 |PREPARED|false|SKU |UserTransaction (2 |IDLE |NULL |SKU |UserTransaction ij> xa_end xa_suspend 2; ij> -- get local connection again xa_getconnection; ij> insert into foo values (5); 1 row inserted/updated/deleted ij> -- autocommit should be on by default; commit; ij> autocommit off; ij> insert into foo values (6); 1 row inserted/updated/deleted ij> -- commit and rollback is allowed on local connection rollback; ij> insert into foo values (6); 1 row inserted/updated/deleted ij> commit; ij> select * from global_xactTable where gxid is not null order by gxid,username; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- (1 |PREPARED|false|SKU |UserTransaction (2 |IDLE |NULL |SKU |UserTransaction ij> -- I am still able to commit other global transactions while I am attached to a -- local transaction. xa_commit xa_2phase 1; ij> xa_end xa_success 2; ij> xa_rollback 2; ij> -- still connected locally select * from global_xactTable where gxid is not null order by gxid,username; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- ij> disconnect; ij> xa_getconnection; ij> select * from global_xactTable where gxid is not null order by gxid,username; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- ij> select * from foo; A ----------- 1 2 3 5 6 ij> autocommit off; ij> delete from foo; 5 rows inserted/updated/deleted ij> -- yanking a local connection away should rollback the changes -- this really depends on if the connection pool manage is doing the job, roll -- it back by hand here since we don't have a way to call local pooled -- connection close method. rollback; ij> -- yank it xa_getconnection; ij> -- getting a new connection handle will revert it to the default autocommit on -- commit should fail commit; ij> autocommit off; ij> select * from global_xactTable where gxid is not null order by gxid,username; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- ij> select * from foo; A ----------- 1 2 3 5 6 ij> -- cannot morph it if the local transaction is not idle xa_start xa_noflags 3; IJ ERROR: XAER_OUTSIDE ij> commit; ij> -- now morph it to a global transaction xa_start xa_noflags 3; ij> -- now I shouldn't be able to yank it xa_getconnection; ERROR XJ059: Cannot close a connection while a global transaction is still active. ij> -- the following does not use the view, or the method alias, so that -- the act of executing this vti does not change the state of the transaction. -- Using the view would sometimes changes the results of the query depending -- on the order of the rows in the vti. select cast(global_xid as char(2)) as gxid, status, username, type from syscs_diag.transaction_table order by gxid, status, username, type; GXID|STATUS |USERNAME |TYPE ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (3 |IDLE |SKU |UserTransaction ij> select * from foo; A ----------- 1 2 3 5 6 ij> delete from foo; 5 rows inserted/updated/deleted ij> xa_end xa_fail 3; IJ ERROR: XA_RBROLLBACK ij> xa_rollback 3; ij> -- local connection again xa_getconnection; ij> select * from global_xactTable where gxid is not null order by gxid,username; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- ij> select * from foo; A ----------- 1 2 3 5 6 ij>