| 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. |
| -- |
| -- testing Transaction table |
| maximumdisplaywidth 9000; |
| ij> connect 'wombat' as c1; |
| ij(C1)> set isolation to rr; |
| 0 rows inserted/updated/deleted |
| ij(C1)> -- Only look at user transactions. Depending on timing of background |
| -- threads for post commit and checkpoint there may be system and |
| -- and internal transactions that vary from machine to machine. |
| create view xactTable as |
| select username, type, status, |
| case when first_instant is NULL then 'readonly' else 'not readonly' end as readOnly, cast(sql_text as varchar(512)) sql_text |
| from syscs_diag.transaction_table |
| where type = 'UserTransaction'; |
| 0 rows inserted/updated/deleted |
| ij(C1)> commit; |
| ij(C1)> select * from xactTable order by username, sql_text, status, type; |
| USERNAME |TYPE |STATUS |READONLY |SQL_TEXT |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type |
| APP |UserTransaction |IDLE |readonly |NULL |
| ij(C1)> create table foo (a int); |
| 0 rows inserted/updated/deleted |
| ij(C1)> create index fooi on foo (a); |
| 0 rows inserted/updated/deleted |
| ij(C1)> select * from xactTable order by username, sql_text, status, type; |
| USERNAME |TYPE |STATUS |READONLY |SQL_TEXT |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type |
| APP |UserTransaction |IDLE |readonly |NULL |
| ij(C1)> autocommit off; |
| ij(C1)> select * from foo; |
| A |
| ----------- |
| ij(C1)> select * from xactTable order by username, sql_text, status, type; |
| USERNAME |TYPE |STATUS |READONLY |SQL_TEXT |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| APP |UserTransaction |ACTIVE |readonly |select * from xactTable order by username, sql_text, status, type |
| APP |UserTransaction |IDLE |readonly |NULL |
| ij(C1)> select type, lockcount as cnt, mode, tablename, lockname, state |
| from syscs_diag.lock_table |
| where tableType <> 'S' |
| order by lockname, mode, cnt, state; |
| TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TABLE|1 |S |FOO |Tablelock |GRANT |
| ij(C1)> commit; |
| ij(C1)> select * from xactTable order by username, sql_text, status, type; |
| USERNAME |TYPE |STATUS |READONLY |SQL_TEXT |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type |
| APP |UserTransaction |IDLE |readonly |NULL |
| ij(C1)> select type, lockcount as cnt, mode, tablename, lockname, state |
| from syscs_diag.lock_table ; |
| TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ij(C1)> insert into foo values (1), (3), (5), (7), (9); |
| 5 rows inserted/updated/deleted |
| ij(C1)> select * from xactTable order by username, sql_text, status, type; |
| USERNAME |TYPE |STATUS |READONLY |SQL_TEXT |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| APP |UserTransaction |ACTIVE |not readonly|select * from xactTable order by username, sql_text, status, type |
| APP |UserTransaction |IDLE |readonly |NULL |
| ij(C1)> select type, lockcount as cnt, mode, tablename, lockname, state |
| from syscs_diag.lock_table |
| where tableType <> 'S' |
| order by lockname, mode, cnt, state; |
| TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ROW |1 |X |FOO |(1,10) |GRANT |
| ROW |1 |X |FOO |(1,11) |GRANT |
| ROW |1 |X |FOO |(1,7) |GRANT |
| ROW |1 |X |FOO |(1,8) |GRANT |
| ROW |1 |X |FOO |(1,9) |GRANT |
| TABLE|2 |IX |FOO |Tablelock |GRANT |
| ij(C1)> commit; |
| ij(C1)> select * from xactTable order by username, sql_text, status, type; |
| USERNAME |TYPE |STATUS |READONLY |SQL_TEXT |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type |
| APP |UserTransaction |IDLE |readonly |NULL |
| ij(C1)> select type, lockcount as cnt, mode, tablename, lockname, state |
| from syscs_diag.lock_table; |
| TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ij(C1)> insert into foo values (6), (10); |
| 2 rows inserted/updated/deleted |
| ij(C1)> -- make another connection |
| connect 'wombat' as c2; |
| ij(C2)> set isolation to rr; |
| 0 rows inserted/updated/deleted |
| ij(C2)> autocommit off; |
| ij(C2)> select * from xactTable order by username, sql_text, status, type; |
| USERNAME |TYPE |STATUS |READONLY |SQL_TEXT |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type |
| APP |UserTransaction |ACTIVE |not readonly|NULL |
| APP |UserTransaction |IDLE |readonly |NULL |
| ij(C2)> select type, lockcount as cnt, mode, tablename, lockname, state |
| from syscs_diag.lock_table |
| where tableType <> 'S' |
| order by lockname, mode, cnt, state; |
| TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ROW |1 |X |FOO |(1,12) |GRANT |
| ROW |1 |X |FOO |(1,13) |GRANT |
| TABLE|2 |IX |FOO |Tablelock |GRANT |
| ij(C2)> select * from xactTable order by username, sql_text, status, type; |
| USERNAME |TYPE |STATUS |READONLY |SQL_TEXT |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| APP |UserTransaction |ACTIVE |readonly |select * from xactTable order by username, sql_text, status, type |
| APP |UserTransaction |ACTIVE |not readonly|NULL |
| APP |UserTransaction |IDLE |readonly |NULL |
| ij(C2)> select type, lockcount as cnt, mode, tablename, lockname, state |
| from syscs_diag.lock_table |
| where tableType <> 'S' |
| order by lockname, mode, cnt, state; |
| TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ROW |1 |X |FOO |(1,12) |GRANT |
| ROW |1 |X |FOO |(1,13) |GRANT |
| TABLE|2 |IX |FOO |Tablelock |GRANT |
| ij(C2)> autocommit off; |
| ij(C2)> select * from foo where a < 2; |
| A |
| ----------- |
| 1 |
| ij(C2)> select * from xactTable order by username, sql_text, status, type; |
| USERNAME |TYPE |STATUS |READONLY |SQL_TEXT |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| APP |UserTransaction |ACTIVE |readonly |select * from xactTable order by username, sql_text, status, type |
| APP |UserTransaction |ACTIVE |not readonly|NULL |
| APP |UserTransaction |IDLE |readonly |NULL |
| ij(C2)> select type, lockcount as cnt, mode, tablename, lockname, state |
| from syscs_diag.lock_table |
| where tableType <> 'S' |
| order by lockname, mode, cnt, state; |
| TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ROW |1 |X |FOO |(1,12) |GRANT |
| ROW |1 |X |FOO |(1,13) |GRANT |
| ROW |1 |S |FOO |(1,3) |GRANT |
| ROW |1 |S |FOO |(1,7) |GRANT |
| TABLE|1 |IS |FOO |Tablelock |GRANT |
| TABLE|2 |IX |FOO |Tablelock |GRANT |
| ij(C2)> insert into foo values (2), (4); |
| 2 rows inserted/updated/deleted |
| ij(C2)> select * from xactTable order by username, sql_text, status, type; |
| USERNAME |TYPE |STATUS |READONLY |SQL_TEXT |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| APP |UserTransaction |ACTIVE |not readonly|select * from xactTable order by username, sql_text, status, type |
| APP |UserTransaction |ACTIVE |not readonly|NULL |
| APP |UserTransaction |IDLE |readonly |NULL |
| ij(C2)> select type, lockcount as cnt, mode, tablename, lockname, state |
| from syscs_diag.lock_table |
| where tableType <> 'S' |
| order by lockname, mode, cnt, state; |
| TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ROW |1 |X |FOO |(1,12) |GRANT |
| ROW |1 |X |FOO |(1,13) |GRANT |
| ROW |1 |X |FOO |(1,14) |GRANT |
| ROW |1 |X |FOO |(1,15) |GRANT |
| ROW |1 |S |FOO |(1,3) |GRANT |
| ROW |1 |S |FOO |(1,7) |GRANT |
| TABLE|1 |IS |FOO |Tablelock |GRANT |
| TABLE|2 |IX |FOO |Tablelock |GRANT |
| TABLE|2 |IX |FOO |Tablelock |GRANT |
| ij(C2)> -- this should block and result in a timeout |
| select * from foo; |
| ERROR 40XL1: A lock could not be obtained within the time requested |
| ij(C2)> select * from xactTable order by username, sql_text, status, type; |
| USERNAME |TYPE |STATUS |READONLY |SQL_TEXT |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type |
| APP |UserTransaction |ACTIVE |not readonly|NULL |
| APP |UserTransaction |IDLE |readonly |NULL |
| ij(C2)> -- when last statement finished rolling back, this transaction should be IDLE; |
| select type, lockcount as cnt, mode, tablename, lockname, state |
| from syscs_diag.lock_table |
| where tableType <> 'S' |
| order by lockname, mode, cnt, state; |
| TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ROW |1 |X |FOO |(1,12) |GRANT |
| ROW |1 |X |FOO |(1,13) |GRANT |
| TABLE|2 |IX |FOO |Tablelock |GRANT |
| ij(C2)> -- this should also block |
| drop table foo; |
| ERROR 40XL1: A lock could not be obtained within the time requested |
| ij(C2)> select * from xactTable order by username, sql_text, status, type; |
| USERNAME |TYPE |STATUS |READONLY |SQL_TEXT |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type |
| APP |UserTransaction |ACTIVE |not readonly|NULL |
| APP |UserTransaction |IDLE |readonly |NULL |
| ij(C2)> select type, lockcount as cnt, mode, tablename, lockname, state |
| from syscs_diag.lock_table |
| where tableType <> 'S' |
| order by lockname, mode, cnt, state; |
| TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ROW |1 |X |FOO |(1,12) |GRANT |
| ROW |1 |X |FOO |(1,13) |GRANT |
| TABLE|2 |IX |FOO |Tablelock |GRANT |
| ij(C2)> commit; |
| ij(C2)> disconnect; |
| ij> set connection c1; |
| ij(C1)> select * from xactTable order by username, sql_text, status, type; |
| USERNAME |TYPE |STATUS |READONLY |SQL_TEXT |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| APP |UserTransaction |ACTIVE |not readonly|select * from xactTable order by username, sql_text, status, type |
| APP |UserTransaction |IDLE |readonly |NULL |
| ij(C1)> select type, lockcount as cnt, mode, tablename, lockname, state |
| from syscs_diag.lock_table |
| where tableType <> 'S' |
| order by lockname, mode, cnt, state; |
| TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ROW |1 |X |FOO |(1,12) |GRANT |
| ROW |1 |X |FOO |(1,13) |GRANT |
| TABLE|2 |IX |FOO |Tablelock |GRANT |
| ij(C1)> drop table foo; |
| 0 rows inserted/updated/deleted |
| ij(C1)> commit; |
| ij(C1)> select * from xactTable order by username, sql_text, status, type; |
| USERNAME |TYPE |STATUS |READONLY |SQL_TEXT |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type |
| APP |UserTransaction |IDLE |readonly |NULL |
| ij(C1)> select l.type, lockcount as cnt, mode, tablename, lockname, state |
| 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' |
| order by lockname, mode, cnt, state; |
| TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ij(C1)> commit; |
| ij(C1)> -- ensure the system vti can not be modified. |
| drop table syscs_diag.transaction_table; |
| ERROR X0Y56: 'DROP TABLE' is not allowed on the System table '"SYSCS_DIAG"."TRANSACTION_TABLE"'. |
| ij(C1)> alter table syscs_diag.transaction_table add column x int; |
| ERROR 42X62: 'ALTER TABLE' is not allowed in the 'SYSCS_DIAG' schema. |
| ij(C1)> update syscs_diag.transaction_table set xid = NULL; |
| ERROR 42Y25: 'SYSCS_DIAG.TRANSACTION_TABLE' is a system table. Users are not allowed to modify the contents of this table. |
| ij(C1)> delete from syscs_diag.transaction_table where 1 = 1; |
| ERROR 42Y25: 'SYSCS_DIAG.TRANSACTION_TABLE' is a system table. Users are not allowed to modify the contents of this table. |
| ij(C1)> insert into syscs_diag.transaction_table(xid) values('bad'); |
| ERROR 42Y25: 'SYSCS_DIAG.TRANSACTION_TABLE' is a system table. Users are not allowed to modify the contents of this table. |
| ij(C1)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SYSCS_DIAG', 'TRANSACTION_TABLE', 1); |
| ERROR 38000: The exception 'java.sql.SQLException: 'ALTER TABLE' is not allowed in the 'SYSCS_DIAG' schema.' was thrown while evaluating an expression. |
| ERROR 42X62: 'ALTER TABLE' is not allowed in the 'SYSCS_DIAG' schema. |
| ij(C1)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SYSCS_DIAG', 'TRANSACTION_TABLE', 1, 1, 1); |
| 0 rows inserted/updated/deleted |
| ij(C1)> -- ensure the old syntax still works until it is deprecated |
| select xid from new org.apache.derby.diag.TransactionTable() AS t where 1 = 0; |
| XID |
| --------------- |
| ij(C1)> update new org.apache.derby.diag.TransactionTable() set xid = NULL; |
| ERROR 42X08: The constructor for class 'org.apache.derby.diag.TransactionTable' cannot be used as an external virtual table because the class does not implement 'java.sql.PreparedStatement'. |
| ij(C1)> delete from new org.apache.derby.diag.TransactionTable() where 1 = 0; |
| ERROR 42X08: The constructor for class 'org.apache.derby.diag.TransactionTable' cannot be used as an external virtual table because the class does not implement 'java.sql.PreparedStatement'. |
| ij(C1)> disconnect; |
| ij> |