transactiontable.out

derby database source code.good for you.

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)> 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 new org.apache.derby.diag.LockTable() t
where tableType <> 'S'