17.10 - TRAN - 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 TRAN command displays currently running transactions with lock requests that have been granted or blocked.

Syntax

{ TRAN | TR } [ ProcId Uniq1 Uniq2 | ALL ]

Syntax Elements

ProcId
The virtual processor number of the parsing engine processor handling the transaction.
Since virtual processor numbers are designated as integer numbers, the corresponding value for this option normally is specified in decimal notation.
This value is the first component of a transaction ID.
Uniq1
A value that is normally specified as four hexadecimal digits.
This value is the second component of a transaction ID.
Uniq2
A value that is normally specified as four hexadecimal digits.
This value is the third component of a transaction ID.
ALL
That all blocked transactions and their corresponding blocker transactions will be considered.
ALL is the default if you do not specify ProcId, Uniq1, and Uniq2, which together constitute a transaction ID.

Usage Notes

A transaction is an internal database concept. A transaction can have more than one blocking transaction. For example, a transaction can have five lock requests, and five transactions can block those same lock requests. In other words, if you have five tables, then conceivably, five other transactions can have the locks on those same five tables.

Each logically grouped display represents one lock request. Only object names relevant to a given lock request are displayed.

For example, only the database name is displayed for a database lock, whereas both a database name and a table name are displayed for a table lock.

The following table shows the components of TRAN command output.

Component... Specifies...
Tran the currently running transactions with locks being applied.
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.
PROXY LOCK

PSEUDO LOCK

These are special locks used internally by the Lock Manager component of Vantage for the purpose of serializing all-AMP table-level or all-AMP partition range locks.

Example: Displaying lock requests on running transactions

In the following examples RPT1 and RPT2 are row-partitioned tables, and NOTRPT1 and NOTRPT2 are a non-partitioned tables.

-> Please enter your selection from the list:
 tran
- 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 10 LOCK ENTRIES -------------

                   GRANTED LOCK REQUEST(S): 

Tran: 30719 00015DCA
Hash Locks     :          1

Range Locks    :          6 

Host:  1025 Session:     0, 1003 Mode: Rd   User: EXAMPLE
Database: EXAMPLE  Table: RPT1 
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: RPT1 
PLLKind: IPN Partition Range        Subtable ID:  1024
                            : Partition1 :                0004 Row Hash1:     0,    0
                            : Partition2 :                0004 Row Hash2:     0,    0 

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: RPT2 
PLLKind: RowHash + IPN Part'n Range Subtable ID:  1024
                            : Partition1 :                0007 Row Hash1:     1,   17
                            : Partition2 :                0007 Row Hash2:     1,   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 

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#