| 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' create; |
| ij> -- xa_datasource 'wombat'; |
| --------------------------------------------- |
| -- a single connection, prepare transaction and crash. |
| --------------------------------------------- |
| 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 'global_xactTable.view'; |
| ij(XA)> -- |
| -- 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. |
| -- |
| 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)> run resource 'LockTableQuery.subsql'; |
| ij(XA)> -- |
| -- 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. |
| -- |
| create view lock_table as |
| select |
| cast(username as char(8)) as username, |
| cast(t.type as char(15)) as trantype, |
| cast(l.type as char(8)) as type, |
| cast(lockcount as char(3)) as cnt, |
| mode, |
| cast(tablename as char(12)) as tabname, |
| cast(lockname as char(10)) as lockname, |
| state, |
| status |
| from |
| syscs_diag.lock_table l right outer join syscs_diag.transaction_table t |
| on l.xid = t.xid where l.tableType <> 'S' and t.type='UserTransaction'; |
| 0 rows inserted/updated/deleted |
| ij(XA)> --on l.xid = t.xid where l.tableType <> 'S' or l.tableType is null |
| -- order by |
| -- tabname, type desc, mode, cnt, lockname |
| -- lock table with system catalog locks included. |
| create view full_lock_table as |
| select |
| cast(username as char(8)) as username, |
| cast(t.type as char(8)) as trantype, |
| cast(l.type as char(8)) as type, |
| cast(lockcount as char(3)) as cnt, |
| mode, |
| cast(tablename as char(12)) as tabname, |
| cast(lockname as char(10)) as lockname, |
| state, |
| status |
| from |
| syscs_diag.lock_table l right outer join syscs_diag.transaction_table t |
| on l.xid = t.xid where l.tableType <> 'S' ; |
| 0 rows inserted/updated/deleted |
| ij(XA)> -- lock table with no join. |
| -- DERBY-6183, removing CAST's to avoid background locks causing |
| -- data trunction warnings. |
| create view lock_table2 as |
| select |
| xid, |
| type, |
| lockcount as cnt, |
| mode, |
| tablename as tabname, |
| lockname, |
| state |
| from |
| syscs_diag.lock_table |
| where tableType <> 'S' ; |
| 0 rows inserted/updated/deleted |
| ij(XA)> -- transaction table with no join. |
| create view tran_table as |
| select |
| * |
| from |
| syscs_diag.transaction_table; |
| 0 rows inserted/updated/deleted |
| ij(XA)> xa_end xa_success 0; |
| ij(XA)> xa_commit xa_1phase 0; |
| ij(XA)> --------------------------------------------- |
| -- a single connection, prepare transaction and crash. |
| --------------------------------------------- |
| xa_start xa_noflags 1; |
| ij(XA)> insert into foo values (0); |
| 1 row inserted/updated/deleted |
| ij(XA)> select * from global_xactTable where gxid is not null order by gxid; |
| GXID|STATUS |READ&|USERNAME |TYPE |
| ------------------------------------------------------------- |
| (1 |ACTIVE |false|APP |UserTransaction |
| WARNING 01004: Data truncation |
| ij(XA)> select global_xid, username, type, status, cast(sql_text as varchar(512)) sql_text from tran_table where global_xid is not null order by global_xid, username, type, status, sql_text; |
| GLOBAL_XID |USERNAME |TYPE |STATUS |SQL_TEXT |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| (1,776f6d626174,776f6d626174) |APP |UserTransaction |ACTIVE |select global_xid, username, type, status, cast(sql_text as varchar(512)) sql_text from tran_table where global_xid is not null& |
| ij(XA)> xa_end xa_success 1; |
| ij(XA)> xa_prepare 1; |
| ij(XA)> xa_start xa_noflags 2; |
| ij(XA)> select * from global_xactTable where gxid is not null order by gxid; |
| GXID|STATUS |READ&|USERNAME |TYPE |
| ------------------------------------------------------------- |
| (1 |PREPARED|false|APP |UserTransaction |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| (2 |IDLE |NULL |APP |UserTransaction |
| ij(XA)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| APP |UserTransaction|TABLE |1 |IX |FOO |Tablelock |GRANT|PREPARED |
| APP |UserTransaction|ROW |1 |X |FOO |(1,8) |GRANT|PREPARED |
| ij(XA)> xa_end xa_success 2; |
| ij(XA)> xa_commit xa_1phase 2; |
| ij(XA)> xa_recover xa_startrscan; |
| [, Recovered 1 in-doubt transactions, , Transaction 1 : (1,776f6d626174,776f6d626174)] |
| ij(XA)> --- xa_datasource 'wombat' shutdown; |
| --------------------------------------------- |
| -- shutdown the database. |
| --------------------------------------------- |
| connect 'jdbc:derby:;shutdown=true'; |
| ERROR XJ015: Derby system shutdown. |
| ij(XA)> --------------------------------------------- |
| -- restart the system, should find the prepared transaction |
| --------------------------------------------- |
| xa_datasource 'wombat'; |
| ij(XA)> --------------------------------------------- |
| -- a single connection, verify the prepared xact has come back. |
| --------------------------------------------- |
| xa_connect ; |
| ij(XA)> xa_start xa_noflags 1; |
| IJ ERROR: XAER_DUPID |
| ij(XA)> xa_start xa_noflags 4; |
| ij(XA)> xa_getconnection; |
| ij(XA)> select * from global_xactTable where gxid is not null order by gxid; |
| GXID|STATUS |READ&|USERNAME |TYPE |
| ------------------------------------------------------------- |
| (1 |PREPARED|false|NULL |UserTransaction |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| (4 |IDLE |NULL |APP |UserTransaction |
| ij(XA)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| NULL |UserTransaction|TABLE |1 |IX |FOO |Tablelock |GRANT|PREPARED |
| NULL |UserTransaction|ROW |1 |X |FOO |(1,8) |GRANT|PREPARED |
| ij(XA)> xa_recover xa_startrscan; |
| [, Recovered 1 in-doubt transactions, , Transaction 1 : (1,776f6d626174,776f6d626174)] |
| ij(XA)> xa_end xa_success 4; |
| ij(XA)> xa_commit xa_1phase 4; |
| ij(XA)> --------------------------------------------- |
| -- a single connection, now make sure after the commit that it is gone. |
| --------------------------------------------- |
| xa_start xa_noflags 4; |
| ij(XA)> xa_commit xa_2phase 1; |
| ij(XA)> select * from global_xactTable where gxid is not null order by gxid; |
| GXID|STATUS |READ&|USERNAME |TYPE |
| ------------------------------------------------------------- |
| (4 |IDLE |NULL |APP |UserTransaction |
| WARNING 01004: Data truncation |
| ij(XA)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(XA)> xa_recover xa_startrscan; |
| [, Recovered 0 in-doubt transactions, ] |
| ij(XA)> xa_end xa_success 4; |
| ij(XA)> xa_commit xa_1phase 4; |
| ij(XA)> --------------------------------------------- |
| -- a single connection, verify the prepared xact has come back - should |
| -- get an error trying to start another with same global xact. |
| --------------------------------------------- |
| exit; |
