The ROWKEY command displays granted and blocked row-level locks.
To request row-level locks the Lock Manager uses the rowkey value, described in Locking and Row Partitions, which allows the Lock Manager to request row-level locks that are limited to the rows in a single partition:
- For row-partitioned tables, if the IPN portion of the rowkey being locked is a non-zero value, the lock is applied only to rows in the specified partition. These rows have rowhash values matching the rowhash portion of the rowkey being locked.
If the IPN portion of the rowkey being locked is zero, the lock is applied to rows in all partitions of the table that match the rowhash portion of the rowkey.
- For non-row-partitioned tables, the partition number portion of the rowkey is always zero, and any rows in the table with rowhash values matching the rowhash portion of the rowkey are locked.
Syntax
{ ROWKEY | ROWK } [
DBname.Tablename TypeAndIndex [,] Partition RowHash1 RowHash2 |
ALL
]
Syntax Elements
- DBname
- The name of a database.
- Tablename
- The name of a table in DBname.
- TypeAndIndex
- A subtable identifier.
- Partition
- The internal partition number for the rowkey lock. Both decimal and hexadecimal numbers are accepted. A decimal number must be followed by a period.
- RowHash1
- RowHash2
- The first and second portions of the row hash, representing the row for which lock information is to be displayed.
- ALL
- That all tables that have a rowkey or rowhash lock applied are considered.
Usage Notes
The following table shows the components of ROWKEY command output.
| Component... | Specifies... |
|---|---|
| Tran | currently running transactions with locks being applied. |
| Hash Locks | the total number of rowhash locks applied by the transaction. |
| Range Locks | the total number of range locks applied by the transaction. |
| RowKey Locks | the number of rowkey locks applied by the transaction. |
| Host | the logical host ID (origin of the transaction). |
| Session | the session number for the transaction. |
| Mode | the type of lock mode:
|
| User | the logon-ID for whom the lock is being requested. |
| Database | the name of the database with granted or blocked locks. |
| Table | the name of the table with granted or blocked locks. |
| PLLKind | the type of partition-level lock that is applied. This only appears for locks on row-partitioned tables. |
| Subtable ID | the identifier of the subtable to which the rowkey lock is applied |
| Partition1 and Partition2 | the internal partition numbers of the rowkeys that begin and end the range of locked rows. For partition-level locks displayed by the ROWKEY command, Partition1 and Partition2 always match. The internal partition number is part of the rowid of the physical row. It is displayed in hexadecimal as [[[hhhh.] hhhh.] [hhhh.hhhh]] or zero. |
| RowHash1 and RowHash2 | the first and second portions of the row hash, representing the row for which lock information is to be displayed. Both decimal and hexadecimal numbers are accepted. A decimal number must be followed by a period. |
In the following examples table RPT1 is a row-partitioned table, and table NOTRPT2 is a non-partitioned table.
Examples: ROWKEY command examples
This example shows the following granted lock requests:
- a proxy lock, used to serialize locking to prevent deadlocks
- a write lock on the row or rows having rowhash 31158,40503 in a row-partitioned table. Only the rows in partition number 3 are locked.
- a write lock on the row or rows having rowhash 31158,40503 in a non-partitioned table.
-> Please enter your selection from the list:
rowkey
- The following amps are available:
0 1 2 3
-> Which amp(s) do you want to request on (S=Sampling/A=all/C=cancel/Q=quit):
1
---------------- AMP 1 REPORTS 5 LOCK ENTRIES -------------
GRANTED LOCK REQUEST(S):
Tran: 30719 00015DCA
Hash Locks : 1
Range Locks : 6
RowKey Locks : 2
Host: 1025 Session: 0, 1003 Mode: Rd User: EXAMPLE
Database: EXAMPLE Table: RPT1
PLLKind: RowHash + All Partitions Subtable ID: 1024
PROXY LOCK
: Partition1 : 0 Row Hash1: 65535,65535
: Partition2 : 0 Row Hash2: 65535,65535
Host: 1025 Session: 0, 1003 Mode: WR User: EXAMPLE
Database: EXAMPLE Table: RPT1
PLLKind: RowKey Subtable ID: 1024
: Partition1 : 0003 Row Hash1: 31158,40503
: Partition2 : 0003 Row Hash2: 31158,40503
Host: 1025 Session: 0, 1003 Mode: WR User: EXAMPLE
Database: EXAMPLE Table: NOTRPT2
Row Hash Lock Subtable ID: 1024
Row Hash1: 31158,40503
BLOCKED LOCK REQUEST(S):
Tran: 30719 00015E1B
Host: 0 Session: 0, 0 Mode: WR User: ALL
Database: EXAMPLE Table: RPT1#
PLLKind: RowKey Subtable ID: 1024
: Partition1 : 0003 Row Hash1: 31158,40503
: Partition2 : 0003 Row Hash2: 31158,40503
Tran: 30719 00015E1C
Host: 0 Session: 0, 0 Mode: WR User: ALL
Database: EXAMPLE Table: NOTRPT2
Row Hash Lock Subtable ID: 1024
Row Hash1: 31158,40503#
-> Please enter your selection from the list:
rowkey example.rpt1 400, 3 31158. 40503.
- The following amps are available:
0 1 2 3
-> Which amp(s) do you want to request on (S=Sampling/A=all/C=cancel/Q=quit):
1
---------------- AMP 1 REPORTS 2 LOCK ENTRIES -------------
GRANTED LOCK REQUEST(S):
Tran: 30719 00015DCA
Hash Locks : 1
Range Locks : 6
RowKey Locks : 1
Host: 1025 Session: 0, 1003 Mode: WR User: EXAMPLE
Database: EXAMPLE Table: RPT1
PLLKind: RowKey Subtable ID: 1024
: Partition1 : 0003 Row Hash1: 31158,40503
: Partition2 : 0003 Row Hash2: 31158,40503
BLOCKED LOCK REQUEST(S):
Tran: 30719 00015E1B
Host: 0 Session: 0, 0 Mode: WR User: ALL
Database: EXAMPLE Table: RPT1#
PLLKind: RowKey Subtable ID: 1024
: Partition1 : 0003 Row Hash1: 31158,40503
: Partition2 : 0003 Row Hash2: 31158,40503
-> Please enter your selection from the list:
rowkey example.notrpt2 400, 0 31158. 40503.
- The following amps are available:
0 1 2 3
-> Which amp(s) do you want to request on (S=Sampling/A=all/C=cancel/Q=quit):
1
---------------- AMP 1 REPORTS 2 LOCK ENTRIES -------------
GRANTED LOCK REQUEST(S):
Tran: 30719 00015DCA
Hash Locks : 1
Range Locks : 6
Host: 1025 Session: 0, 1003 Mode: WR User: EXAMPLE
Database: EXAMPLE Table: NOTRPT2
Row Hash Lock Subtable ID: 1024
Row Hash1: 31158,40503
BLOCKED LOCK REQUEST(S):
Tran: 30719 00015E1C
Host: 0 Session: 0, 0 Mode: WR User: ALL
Database: EXAMPLE Table: NOTRPT2
Row Hash Lock Subtable ID: 1024
Row Hash1: 31158,40503#