transactiontable.out

derby database source code.good for you.

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>