17.10 - ROWKEY - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

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.
A table is composed logically of one or more subtables. TypeAndIndex specifies one of these subtables. For example:
  • 0 is the table header.
  • hex 400 (decimal 1024) is a primary subtable.
  • hex values 404, 408, and 40C (decimal values 1028, 1032, and 1036), and other +4 incremental values, are secondary index subtables.
  • hex values 800, C00, and 1000 (decimal values 2048, 3072, and 4096), and other multiples of hex 400 (decimal 1024) are fallback subtables.
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.
Partition is zero for non-row-partitioned tables.
RowHash1
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.
ALL
That all tables that have a rowkey or rowhash lock applied are considered.
ALL is the default if you do not specify an object name.

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:
  • Access
  • Read
  • Write
  • Exclusive
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#