transactiontable.out

derby database source code.good for you.

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