Getting Locking Information
Page 2 of 3

Displaying Current Locking Information

Locking information is provided by querying the LockTable and TransactionTable VTIs. Typically, you will derive the locking information you need by writing queries that join these two virtual tables on the XID column.

LockTable

Each row of the COM.cloudscape.vti.LockTable virtual table corresponds to a lock and its associated objects.

The columns of LockTable are:

Name

Data Type

Width

Nullable

Contents

XID

VARCHAR

10

no

The transaction ID

TYPE

VARCHAR

5

no

The type of lock:

  • ROW (row lock)
  • TABLE (table lock)

MODE

VARCHAR

4

no

Lock mode:

  • S (shared lock)
  • U (update lock)
  • X (exclusive lock)
  • IS (intent shared lock, N/A to Row lock)
  • IX (intent exclusive lock, N/A to Row lock)

TABLENAME

VARCHAR

30

no

Table name

LOCKNAME

VARCHAR

20

no

ID of locked object (for example, page and row number)

STATE

VARCHAR

5

no

GRANT or WAIT

TABLETYPE

VARCHAR

9

no

Type of table:

  • S (system table)
  • T (user table)

LOCKCOUNT

VARCHAR

5

no

An internal count

INDEXNAME

VARCHAR

30

yes

Index name, if applicable

LockTable is a built-in class alias, so you can invoke LockTable without using the class name. For example:

SELECT * FROM NEW LockTable() AS LT

NOTE: Update locks can only be acquired on update cursors (those declared with the FOR UPDATE clause).

TransactionTable

Each row of the COM.cloudscape.vti.TransactionTable VTI (aliased as TransactionTable) corresponds to a transaction and its associated objects.

The columns of TransactionTable are:

Name

Data Type

Width

Nullable

Contents

XID

VARCHAR

10

no

The transaction ID

GLOBAL_XID

VARCHAR

140

yes

The global transaction id of the transaction if it is a participant of a JTA-related global transaction.

USERNAME

VARCHAR

30

no

The user name (APP by default).

TYPE

VARCHAR

30

no

The transaction type. Types other than UserTransaction are typically short-lived and internal.

STATUS

VARCHAR

8

no

IDLE or ACTIVE

FIRST_INSTANT

VARCHAR

20

yes

The instant of the first log record (if this is an update transaction).

SQL_TEXT

LONGVARCHAR

1024

yes

The text of any currently-executing SQL-J statements. For all transactions not currently executing a statement, this column displays NULL.

Example Using the Lock Manager VTIs

The following example illustrates a use of the lock diagnostics VTIs. Statements that create locks on the HotelAvailability and FlightAvailability tables are run, and then a query of LockTable and TransactionTable shows the status of the locks.

SELECT Locks.xid, Locks.Type, TableName, Mode, Lockname, State
FROM NEW LockTable() AS LOCKS, NEW TransactionTable() AS Trans
WHERE Locks.xid = Trans.xid AND TABLENAME IN (VALUES 'HOTELAVAILABILITY', 'FLIGHTAVAILABILITY')

XID       |TYPE |TABLENAME                     |MODE|LOCKNAME            |STATE
-------------------------------------------------------------------------------
995       |ROW  |HOTELAVAILABILITY             |X   |(1,12)              |GRANT
1000      |ROW  |HOTELAVAILABILITY             |S   |(2,1)               |GRANT
995       |ROW  |HOTELAVAILABILITY             |X   |(1,13)              |GRANT
995       |ROW  |HOTELAVAILABILITY             |X   |(1,14)              |GRANT
995       |ROW  |HOTELAVAILABILITY             |X   |(1,15)              |GRANT
1000      |ROW  |FLIGHTAVAILABILITY            |X   |(1,19)              |GRANT
995       |TABLE|HOTELAVAILABILITY             |IX  |Tablelock           |GRANT
1000      |TABLE|HOTELAVAILABILITY             |IX  |Tablelock           |GRANT
1000      |ROW  |FLIGHTAVAILABILITY            |X   |(1,18)              |GRANT
1000      |ROW  |FLIGHTAVAILABILITY            |X   |(1,17)              |GRANT
1000      |ROW  |FLIGHTAVAILABILITY            |X   |(1,16)              |GRANT
995       |ROW  |HOTELAVAILABILITY             |X   |(1,6)               |GRANT
1000      |ROW  |HOTELAVAILABILITY             |X   |(1,6)               |WAIT 
995       |ROW  |HOTELAVAILABILITY             |X   |(1,7)               |GRANT
1000      |ROW  |FLIGHTAVAILABILITY            |X   |(1,11)              |GRANT
1000      |ROW  |FLIGHTAVAILABILITY            |X   |(1,10)              |GRANT
1000      |ROW  |FLIGHTAVAILABILITY            |X   |(1,9)               |GRANT
1000      |ROW  |FLIGHTAVAILABILITY            |X   |(1,8)               |GRANT
1000      |ROW  |FLIGHTAVAILABILITY            |X   |(1,15)              |GRANT
1000      |ROW  |FLIGHTAVAILABILITY            |X   |(1,14)              |GRANT
1000      |ROW  |FLIGHTAVAILABILITY            |X   |(1,13)              |GRANT
1000      |ROW  |FLIGHTAVAILABILITY            |X   |(1,12)              |GRANT
995       |ROW  |HOTELAVAILABILITY             |X   |(1,16)              |GRANT
995       |ROW  |HOTELAVAILABILITY             |X   |(1,17)              |GRANT
1000      |TABLE|FLIGHTAVAILABILITY            |IX  |Tablelock           |GRANT
995       |TABLE|FLIGHTAVAILABILITY            |IX  |Tablelock           |GRANT
995       |ROW  |HOTELAVAILABILITY             |X   |(1,18)              |GRANT
995       |ROW  |HOTELAVAILABILITY             |X   |(1,19)              |GRANT
995       |ROW  |FLIGHTAVAILABILITY            |S   |(2,1)               |GRANT
1000      |ROW  |FLIGHTAVAILABILITY            |X   |(1,7)               |GRANT
995       |ROW  |HOTELAVAILABILITY             |X   |(1,20)              |GRANT
1000      |ROW  |FLIGHTAVAILABILITY            |X   |(1,6)               |GRANT
995       |ROW  |FLIGHTAVAILABILITY            |X   |(1,6)               |WAIT 
995       |ROW  |HOTELAVAILABILITY             |X   |(1,8)               |GRANT
995       |ROW  |HOTELAVAILABILITY             |X   |(1,9)               |GRANT
995       |ROW  |HOTELAVAILABILITY             |X   |(1,10)              |GRANT
995       |ROW  |HOTELAVAILABILITY             |X   |(1,11)              |GRANT

Note in this example that transactions 995 and 1000 are competing for the same locks. For the row lock on (1,6) in HotelAvailability, Transaction 995 has the lock and transaction 1000 is waiting. For the rowlock on (1,6) in FlightAvailability, transaction 1000 has the lock and Transaction 995 is waiting. (These two transactions are deadlocked).

At this point, you may want to see the text of the SQL statements associated with each of these transactions. You could query the TransactionTable like this:

SELECT SQL_TEXT FROM NEW TransactionTable() AS Trans
WHERE XID = 995 OR XID = 1000

NOTE: See the sample program JBMSTours.AdminHelper for this example of forcing a deadlock and working with the lock diagnostics VTIs.