BLOCKERS - Advanced SQL Engine - Teradata Database

Database Utilities
Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
xha1591998860283.ditamap
dita:ditavalPath
xha1591998860283.ditaval
dita:id
B035-1102
lifecycle
previous
Product Category
Teradata Vantage™

The BLOCKERS command displays currently blocked transactions and the corresponding blocking transactions with granted lock requests.

Syntax

{ BLOCKERS | B } TRAN
  [ ProcId Uniq1 Uniq2 | ALL ]
  [ LIMIT [ NUMBER | NONE ] ]

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 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 will be considered.
ALL is the default if you do not specify a transaction ID.
LIMIT
The number of blocker transactions to consider for a blocked transaction.
NUMBER
The desired limiting value.
NONE
That all blocker transactions for a blocked transaction are considered.
Specifying NONE corresponds to specifying zero for Number.

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.

The following table shows the components of BLOCKERS command output.

Component.... Includes the...
Number of Blocked Trans displayed total number of both blocked and blocker transactions.
Blocked Trans number of the blocked transaction and the following information:
  • Number of blockers displays

    Specifies blocker entry count.

  • Number of blockers exists

    Specifies actual blocker count.
Blocker Trans number of blocking transactions and the following information:
  • Lock mode specifies a type of lock mode:
    • Access
    • Read
    • Write
    • Exclusive
  • Lock status specifies a type of status of the lock request:
    • Granted
    • Waiting
  • Lock objectType specifies a type of object that is locked:
    • Database
    • Table
    • Rowrange
    • Row
  • Lock PLLkind specifies the type of partition-level lock applied to row-partitioned tables:
    • RowHash + All Partitions
    • RowKey
    • IPN Partition Range (IPN is internal partition number)
    • RowKey Range
    • RowHash + IPN Part’n Range
  • Lock objectID specifies an ID of the locked object:
    • Database ID
    • Database Name
    • Table ID
    • Table Name
    • Partition1
    • RowHash1
    • Partition2
    • RowHash2

In the following examples RPT1 is a row-partitioned table, and NOTRPT2 is a non-partitioned table.

Examples: BLOCKERS command

-> Please enter your selection from the list:
 blockers
- 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 -------------
Number of Blocked Trans displayed :      2
=========================================
Blocked Trans : 30719 00015E1C
   Number of blockers displays :       1
   Number of blockers exists   :       1
   Blocker Trans : 30719 00015DCA
           lock mode       : Write
           lock status     : Granted
           lock objectType : Row
           lock objectID   : DBID      : 00000402
                           : DBNAME    : EXAMPLE
                           : TableID   : 00000AA0,0400
                           : TableName : NOTRPT2
                           : RowHash1  : 79B69E37
                           : RowHash2  : 00000000
Blocked Trans : 30719 00015E1B
   Number of blockers displays :       1
   Number of blockers exists   :       1
   Blocker Trans : 30719 00015DCA
           lock mode       : Write
           lock status     : Granted
           lock objectType : RowRange
           lock PLLKind    : RowKey
           lock objectID   : DBID      : 00000402
                           : DBNAME    : EXAMPLE
                           : TableID   : 00000A9D,0400
                           : TableName : RPT1
                           : Partition1:                0003
                           : RowHash1  :            79B69E37
                           : Partition2:                0003
                           : RowHash2  :            79B69E37
-> Please enter your selection from the list:
 blockers tran 30719. 0001 5e1c
- 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 -------------
Number of Blocked Trans displayed :      1
=========================================
Blocked Trans : 30719 00015E1C
   Number of blockers displays :       1
   Number of blockers exists   :       1
   Blocker Trans : 30719 00015DCA
           lock mode       : Write
           lock status     : Granted
           lock objectType : Row
           lock objectID   : DBID      : 00000402
                           : DBNAME    : EXAMPLE
                           : TableID   : 00000AA0,0400
                           : TableName : NOTRPT2
                           : RowHash1  : 79B69E37
                           : RowHash2  : 00000000
-> Please enter your selection from the list:
 blockers tran 30719. 0001 5e1b
- 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 -------------
Number of Blocked Trans displayed :      1
=========================================
Blocked Trans : 30719 00015E1B
   Number of blockers displays :       1
   Number of blockers exists   :       1
   Blocker Trans : 30719 00015DCA
           lock mode       : Write
           lock status     : Granted
           lock objectType : RowRange
           lock PLLKind    : RowKey
           lock objectID   : DBID      : 00000402
                           : DBNAME    : EXAMPLE
                           : TableID   : 00000A9D,0400
                           : TableName : RPT1
                           : Partition1:                0003
                           : RowHash1  :            79B69E37
                           : Partition2:                0003
                           : RowHash2  :            79B69E37