ij> -- testing Transaction table maximumdisplaywidth 9000; ij> connect 'wombat' as c1; ij(C1)> set isolation to rr; 0 rows inserted/updated/deleted ij(C1)> 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 new org.apache.derby.diag.TransactionTable() t where type != 'InternalTransaction'; 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 new org.apache.derby.diag.LockTable() t 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 new org.apache.derby.diag.LockTable() t ; 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 new org.apache.derby.diag.LockTable() t 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 new org.apache.derby.diag.LockTable() t; 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 new org.apache.derby.diag.LockTable() t 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)> -- make sure class alias works select * from xactTable order by username, sql_text, status, type; USERNAME |TYPE |STATUS |READONLY |SQL_TEXT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- APP |UserTransaction |ACTIVE |readonly |-- make sure class alias works 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 new org.apache.derby.diag.LockTable() t 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 new org.apache.derby.diag.LockTable() t 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 new org.apache.derby.diag.LockTable() t 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)> -- when this finished rolling back, this transaction should be IDLE select * from xactTable order by username, sql_text, status, type; USERNAME |TYPE |STATUS |READONLY |SQL_TEXT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- APP |UserTransaction |IDLE |readonly |-- when this finished rolling back, this transaction should be IDLE 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 new org.apache.derby.diag.LockTable() t 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 new org.apache.derby.diag.LockTable() t 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 new org.apache.derby.diag.LockTable() t 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 new org.apache.derby.diag.LockTable() l right outer join new org.apache.derby.diag.TransactionTable() 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)> disconnect; ij>