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.

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.