15.00 - TRAN - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
15.00
Content Type
Configuration
Publication ID
B035-1102-015K
Language
English (United States)
Last Update
2018-09-25

TRAN

Purpose  

The TRAN command identifies currently running transactions with locks being applied.

Syntax  

 

Syntax element...

Specifies...

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 number 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 that will be considered.

ALL is the default if you do not specify an ProcId.

Note: Together, ProcId, Uniq1, and Uniq2 identify 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 being locked.

    Table

    the name of the locked table.

    DUMMY LOCK

    the specific row has lock applied on a distinct pseudo dictionary table when applicable to facilitate concurrency control. The pseudo dictionary table does not exist. The table ID is used as the value of the row hash.

    Example  

    The following example shows the transactions on all AMPs, with AMP 0 having four lock entries, and AMP1 having three lock entries.

    -> Please enter your selection from the list: tr
    - 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): a
    -> Please enter your selection from the list:
    tr all

    - The following amps are available:

        0     1 

    -> Which amp(s) do you want to request on (S=Sampling/A=all/C=cancel/Q=quit):
    a


    ---------------- AMP 0 REPORTS 4 LOCK ENTRIES -------------


                       GRANTED LOCK REQUEST(S):

    Tran: 16383 00001EE0
    Host:  1025 Session:     0, 1001 Mode: Ac   User: DBC
    Database: DBC                         Table: DBASE

    Host:  1025 Session:     0, 1001 Mode: Ac   User: DBC
    Database: DBC                         Table: TVM

    Host:  1025 Session:     0, 1001 Mode: WR   User: DBC
    Database: RKPMED                      Table: STORES
    DUMMY LOCK

    Host:  1025 Session:     0, 1001 Mode: WR   User: DBC
    Database: RKPMED                      Table: STORES


    ---------------- AMP 1 REPORTS 3 LOCK ENTRIES -------------


                       GRANTED LOCK REQUEST(S):

    Tran: 16383 00001EE0
    Host:  1025 Session:     0, 1001 Mode: Ac   User: DBC
    Database: DBC                          Table: DBASE

    Host:  1025 Session:     0, 1001 Mode: Ac   User: DBC
    Database: DBC                          Table: TVM

    Host:  1025 Session:     0, 1001 Mode: WR   User: DBC
    Database: RKPMED                       Table: STORES


    -------------< END OF OUTPUT >-------------

    Example  

    This example shows a blocked row hash lock request. Note the hash mark (#) that appears next to the blocked row hash, indicating that the lock was requested, but has not yet been granted.

    LOCK DISPLAY UTILITY Command String Syntax:

        Help or ?
        TRan     [ProcId Uniq1 Uniq2] | [ALL]
        Db       [DBname] | [ALL]
        TAble    [DBname.Tablename] | [ALL]
        ROWRange [DBname.Tablename TypeAndIndex] | [ALL]
        ROWHash  [DBname.Tablename TypeAndIndex, RowHash1 RowHash2] | [ALL]
        Blockers [TRAN [ProcId Uniq1 Uniq2] | [ALL]] | [LIMIT [Number] | [NONE]]
        Quit

    -> Please enter your selection from the list: tr
    - 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): a


    ---------------- AMP 2 REPORTS 2 LOCK ENTRIES -------------


                     GRANTED LOCK REQUEST(S):

    Tran:  16383 006AACE
    Host:  1076 Session:    0,2013 Mode: EX   User: USER1
    Database: DBASE1                  Table: TBL_1A
    Row Hash1: 31158,40503


                     BLOCKED LOCK REQUEST(S):

    Tran: 16383 0006AAD2
    Host:  1076 Session:    0,2012 Mode: EX   User: USER2
    Database: DBASE1                  Table: TBL_1A
    Row Hash1: 31158,40503#