SYSCS_DIAG.LOCK_TABLE diagnostic table

The SYSCS_DIAG.LOCK_TABLE diagnostic table shows all of the locks that are currently held in the Derby database.

You can reference the SYSCS_DIAG.LOCK_TABLE diagnostic table directly in a statement. For example:
SELECT * FROM SYSCS_DIAG.LOCK_TABLE

All users can access this diagnostic table, whether or not the database has authentication and SQL authorization enabled. See "Configuring user authentication" and "Configuring user authorization" in the Derby Security Guide for more information.

When the SYSCS_DIAG.LOCK_TABLE diagnostic table is referenced in a statement, a snapshot of the lock table is taken. A snapshot is used so that referencing the diagnostic table does not alter the normal timing and flow of the application. It is possible that some locks will be in a transition state when the snapshot is taken.

The table has the columns shown in the following table.

Table 1. Columns in the SYSCS_DIAG.LOCK_TABLE table
Column Name Type Length Nullable Contents
XID VARCHAR 15 false The transaction ID, which can be joined with the XID of the transaction table. See SYSCS_DIAG.TRANSACTION_TABLE diagnostic table.
TYPE VARCHAR 5 true The type of lock, which can be either 'ROW', 'TABLE', or 'LATCH'.
MODE VARCHAR 4 false The mode of the lock. For a lock of type 'TABLE', the valid values are:

'S' for shared lock
'U' for update lock
'X' for exclusive lock
'IS' for intent shared lock
'IX' for intent exclusive lock

For a lock of type 'ROW', the valid values are:

'S' for shared lock
'U' for update lock
'X' for exclusive lock

For a lock of type 'LATCH', the only valid value is:

'X' for exclusive lock

TABLENAME VARCHAR 128 false The name of the base table that the lock is for.
LOCKNAME VARCHAR 20 false The name of the lock.
STATE VARCHAR 5 true The state of the lock, which is either 'GRANT' or 'WAIT'.
TABLETYPE VARCHAR 9 false The type of the table. Valid values are:

'T' for user table
'S' for system table

LOCKCOUNT VARCHAR 5 false The internal lock count.
INDEXNAME VARCHAR 128 true Value is normally null. If it is non-null, a lock is held on the index.
Related reference
SYSCS_DIAG.CONTAINED_ROLES diagnostic table function
SYSCS_DIAG.ERROR_LOG_READER diagnostic table function
SYSCS_DIAG.ERROR_MESSAGES diagnostic table
SYSCS_DIAG.SPACE_TABLE diagnostic table function
SYSCS_DIAG.STATEMENT_CACHE diagnostic table
SYSCS_DIAG.STATEMENT_DURATION diagnostic table function
SYSCS_DIAG.TRANSACTION_TABLE diagnostic table