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:

All users can access this diagnostic table, whether or not the database has authentication and SQL authorization enabled.

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.SPACE_TABLE diagnostic table function
SYSCS_DIAG.STATEMENT_DURATION diagnostic table function