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> xa_datasource 'wombat'; ij> --------------------------------------------- -- a single connection and 1 phase commit --------------------------------------------- xa_connect ; ij> xa_start xa_noflags 0; ij> xa_getconnection; ij> drop table APP.foo; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'APP.FOO' because it does not exist. ij> create table APP.foo (a int); 0 rows inserted/updated/deleted ij> insert into APP.foo values (0); 1 row inserted/updated/deleted ij> select * from APP.foo; A ----------- 0 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, status; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- (0 |ACTIVE |false|APP |UserTransaction ij> xa_end xa_success 0; ij> xa_commit xa_1phase 0; ij> xa_datasource 'wombat' shutdown; ERROR 08006: Database 'wombat' shutdown. ij> --------------------------------------------- -- 5 interleaving xa connections --------------------------------------------- xa_datasource 'wombat'; ij> xa_connect user 'mamta' password 'mamta' ; ij> -- global connection 1 xa_start xa_noflags 1; ij> xa_getconnection; ij> insert into APP.foo values (1); 1 row inserted/updated/deleted ij> xa_end xa_suspend 1; ij> -- global connection 2 xa_start xa_noflags 2; ij> insert into APP.foo values (2); 1 row inserted/updated/deleted ij> xa_end xa_suspend 2; ij> -- global connection 3 xa_start xa_noflags 3; ij> insert into APP.foo values (3); 1 row inserted/updated/deleted ij> xa_end xa_suspend 3; ij> -- global connection 4 xa_start xa_noflags 4; ij> insert into APP.foo values (4); 1 row inserted/updated/deleted ij> xa_end xa_suspend 4; ij> -- global connection 5 xa_start xa_noflags 5; ij> insert into APP.foo values (5); 1 row inserted/updated/deleted ij> xa_end xa_suspend 5; ij> xa_start xa_resume 1; ij> insert into APP.foo values (11); 1 row inserted/updated/deleted ij> xa_end xa_suspend 1; ij> xa_start xa_resume 5; ij> insert into APP.foo values (55); 1 row inserted/updated/deleted ij> xa_end xa_suspend 5; ij> xa_start xa_resume 2; ij> insert into APP.foo values (22); 1 row inserted/updated/deleted ij> xa_end xa_suspend 2; ij> xa_start xa_resume 4; ij> insert into APP.foo values (44); 1 row inserted/updated/deleted ij> xa_end xa_suspend 4; ij> xa_start xa_resume 3; ij> insert into APP.foo values (33); 1 row inserted/updated/deleted ij> xa_end xa_suspend 3; ij> -- prepare all the global connections except the first one. This way, we will see all -- the global transactions prepared so far after the database shutdown and restart. xa_end xa_success 2; ij> xa_prepare 2; ij> xa_end xa_success 3; ij> xa_prepare 3; ij> xa_end xa_success 4; ij> xa_prepare 4; ij> xa_end xa_success 5; ij> xa_prepare 5; ij> -- local connection 1 connect 'wombat' as local1; ij(LOCAL1)> autocommit off; ij(LOCAL1)> -- at this point, should see 4 global connections in the prepared mode and one global -- connection in active mode and one local connection. select * from global_xactTable where gxid is not null order by gxid, username, status; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- (1 |ACTIVE |false|MAMTA |UserTransaction (2 |PREPARED|false|MAMTA |UserTransaction (3 |PREPARED|false|MAMTA |UserTransaction (4 |PREPARED|false|MAMTA |UserTransaction (5 |PREPARED|false|MAMTA |UserTransaction ij(LOCAL1)> select count(*) from syscs_diag.lock_table where mode = 'X' or mode = 'IX'; 1 ----------- 15 ij(LOCAL1)> xa_datasource 'wombat' shutdown; ERROR 08006: Database 'wombat' shutdown. ij(LOCAL1)> -- after shutdown and restart, should see only 4 prepared global connection from -- earlier boot of the database. The local connections made during that time and -- unprepared global connection will all rollback at the startup time and hence -- we won't see them xa_datasource 'wombat'; ij(LOCAL1)> xa_connect user 'mamta1' password 'mamta1'; ij(LOCAL1)> connect 'wombat' as local2; ij(LOCAL2)> autocommit off; ij(LOCAL2)> -- this will time out because there are locks on the table APP.foo from the global -- transactions select * from APP.foo; A ----------- ERROR 40XL1: A lock could not be obtained within the time requested ij(LOCAL2)> -- should see 4 global transactions and a local connection select * from global_xactTable where gxid is not null order by gxid, username, status; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- (2 |PREPARED|false|NULL |UserTransaction (3 |PREPARED|false|NULL |UserTransaction (4 |PREPARED|false|NULL |UserTransaction (5 |PREPARED|false|NULL |UserTransaction ij(LOCAL2)> -- rollback first global transactions 2 and 3 and commit the 3rd one. xa_rollback 2; ij(LOCAL2)> xa_rollback 3; ij(LOCAL2)> xa_commit xa_2Phase 4; ij(LOCAL2)> -- add couple more global transactions xa_start xa_noflags 6; ij(LOCAL2)> xa_getconnection; ij(XA)> insert into APP.foo values (6); 1 row inserted/updated/deleted ij(XA)> xa_end xa_suspend 6; ij(XA)> xa_start xa_noflags 7; ij(XA)> insert into APP.foo values (7); 1 row inserted/updated/deleted ij(XA)> xa_end xa_suspend 7; ij(XA)> xa_start xa_noflags 8; ij(XA)> insert into APP.foo values (8); 1 row inserted/updated/deleted ij(XA)> xa_end xa_suspend 8; ij(XA)> -- once a transaction is in prepare mode, can't resume it. Can only commit/rollback -- so the following will give an error xa_start xa_resume 5; IJ ERROR: XAER_NOTA ij(XA)> xa_start xa_resume 6; ij(XA)> insert into APP.foo values (66); 1 row inserted/updated/deleted ij(XA)> xa_end xa_suspend 6; ij(XA)> xa_start xa_resume 8; ij(XA)> insert into APP.foo values (88); 1 row inserted/updated/deleted ij(XA)> xa_end xa_suspend 8; ij(XA)> xa_start xa_resume 7; ij(XA)> insert into APP.foo values (77); 1 row inserted/updated/deleted ij(XA)> xa_end xa_suspend 7; ij(XA)> -- prepare the global transactions added after the database boot xa_end xa_success 6; ij(XA)> xa_prepare 6; ij(XA)> xa_end xa_success 7; ij(XA)> xa_prepare 7; ij(XA)> xa_end xa_success 8; ij(XA)> xa_prepare 8; ij(XA)> -- make a local connection and at this point, should see 4 global transactions -- and 2 local connections connect 'wombat' as local3; ij(LOCAL3)> autocommit off; ij(LOCAL3)> select * from global_xactTable where gxid is not null order by gxid, username, status; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- (5 |PREPARED|false|NULL |UserTransaction (6 |PREPARED|false|MAMTA1 |UserTransaction (7 |PREPARED|false|MAMTA1 |UserTransaction (8 |PREPARED|false|MAMTA1 |UserTransaction ij(LOCAL3)> xa_datasource 'wombat' shutdown; ERROR 08006: Database 'wombat' shutdown. ij(LOCAL3)> -- shutdown the datbase, restart and check the transactions in the transaction table. xa_datasource 'wombat'; ij(LOCAL3)> xa_connect user 'mamta2' password 'mamta2'; ij(LOCAL3)> connect 'wombat' as local4; ij(LOCAL4)> autocommit off; ij(LOCAL4)> -- this will time out as expected select * from APP.foo; A ----------- ERROR 40XL1: A lock could not be obtained within the time requested ij(LOCAL4)> -- will see 4 global transactions and 1 local transaction select * from global_xactTable where gxid is not null order by gxid, username, status; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- (5 |PREPARED|false|NULL |UserTransaction (6 |PREPARED|false|NULL |UserTransaction (7 |PREPARED|false|NULL |UserTransaction (8 |PREPARED|false|NULL |UserTransaction ij(LOCAL4)> xa_datasource 'wombat' shutdown; ERROR 08006: Database 'wombat' shutdown. ij(LOCAL4)> -- shutdown and restart and check the transaction table xa_datasource 'wombat'; ij(LOCAL4)> xa_connect user 'mamta3' password 'mamta3'; ij(LOCAL4)> connect 'wombat' as local5; ij(LOCAL5)> autocommit off; ij(LOCAL5)> insert into APP.foo values(90); 1 row inserted/updated/deleted ij(LOCAL5)> connect 'wombat' as local6; ij(LOCAL6)> autocommit off; ij(LOCAL6)> insert into APP.foo values(101); 1 row inserted/updated/deleted ij(LOCAL6)> -- 4 global transactions and 2 local transactions select * from global_xactTable where gxid is not null order by gxid, username, status; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- (5 |PREPARED|false|NULL |UserTransaction (6 |PREPARED|false|NULL |UserTransaction (7 |PREPARED|false|NULL |UserTransaction (8 |PREPARED|false|NULL |UserTransaction ij(LOCAL6)> -- rollback few global transactions and commit few others xa_rollback 5; ij(LOCAL6)> xa_rollback 6; ij(LOCAL6)> xa_commit xa_2Phase 7; ij(LOCAL6)> xa_rollback 8; ij(LOCAL6)> -- at this point, still time out because there are 2 local transactions -- holding locks on table APP.foo select * from APP.foo; A ----------- ERROR 40XL1: A lock could not be obtained within the time requested ij(LOCAL6)> select * from global_xactTable where gxid is not null order by gxid, username, status; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- ij(LOCAL6)> xa_datasource 'wombat' shutdown; ERROR 08006: Database 'wombat' shutdown. ij(LOCAL6)> -- shutdown and restart. There should be no global transactions at this point. xa_datasource 'wombat'; ij(LOCAL6)> xa_connect user 'mamta4' password 'mamta4'; ij(LOCAL6)> connect 'wombat' as local7; ij(LOCAL7)> autocommit off; ij(LOCAL7)> -- no more locks on table APP.foo and hence select won't time out. select * from APP.foo; A ----------- 0 4 44 7 77 ij(LOCAL7)> -- no more global transactions, just one local transaction select * from global_xactTable where gxid is not null order by gxid, username, status; GXID|STATUS |READ&|USERNAME |TYPE ------------------------------------------------------------- ij(LOCAL7)>