17.10 - ROWRANGE - 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 ROWRANGE command displays granted and blocked row range locks. A row range lock locks a range of rows (the rows identified by a range of contiguous rowhash values in non-row-partitioned tables or a range of contiguous rowkey values in row-partitioned tables). Rowkey values are described in Locking and Row Partitions.

Syntax

{ ROWRANGE | ROWR } [
  DBname.Tablename TypeAndIndex |
  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.
ALL
That all tables that have a rowrange-level lock request are considered.
ALL is the default if you do not specify the command parameters.

Usage Notes

The following table shows the components of ROWRANGE command output.

Component... Specifies...
Tran the 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.
Row Range Locks the number of row range 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 row range lock is applied
Partition1 and Partition2 the internal partition numbers of the rowkeys that begin and end the range of locked rows.

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 RPT1 is a row-partitioned table, and NOTRPT1 is a non-partitioned table.

Examples: ROWRANGE command examples

This example shows:
  • a write lock on the range of rows beginning at rowhash 1,17 and ending at rowhash 2,17 in a row-partitioned table. Only the rows in partition number 000A are locked.
  • a write lock on the range of rows beginning at rowhash 31158,40496 and ending at rowhash 31158,40502 in a non-partitioned table. 
-> Please enter your selection from the list:
 rowrange
- 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

Row Range Locks:          2

Host:  1025 Session:     0, 1003 Mode: WR   User: EXAMPLE
Database: EXAMPLE  Table: RPT1
PLLKind:  RowKey Range                Subtable ID:  1024

                            : Partition1 :                000A Row Hash1:     1,   17
                            : Partition2 :                000A Row Hash2:     2,   17

Host:  1025 Session:     0, 1003 Mode: WR   User: EXAMPLE
Database: EXAMPLE  Table: NOTRPT1 
 Row Range Lock                       Subtable ID:  1024
 Row Hash1: 31158,40496 Row Hash2: 31158,40502 



-> Please enter your selection from the list:
 rowrange example.rpt1 400
- 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 1 LOCK ENTRIES -------------

                   GRANTED LOCK REQUEST(S):

Tran: 30719 00015DCA
Hash Locks     :          1

Range Locks    :          6

Row Range Locks:          1

Host:  1025 Session:     0, 1003 Mode: WR   User: EXAMPLE
Database: EXAMPLE  Table: RPT1
PLLKind:  RowKey Range                Subtable ID:  1024

                            : Partition1 :                000A Row Hash1:     1,   17
                            : Partition2 :                000A Row Hash2:     2,   17 



-> Please enter your selection from the list:
 rowrange example.notrpt1 400
- 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 1 LOCK ENTRIES -------------

                   GRANTED LOCK REQUEST(S):

Tran: 30719 00015DCA
Hash Locks     :          1

Range Locks    :          6

Row Range Locks:          1

Host:  1025 Session:     0, 1003 Mode: WR   User: EXAMPLE
Database: EXAMPLE  Table: NOTRPT1 
 Row Range Lock                       Subtable ID:  1024
 Row Hash1: 31158,40496 Row Hash2: 31158,40502