Shredded_LockTbl: Blocking Transaction Lock Shredding Target Table - Teradata Database

Teradata Database Administration
Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
Product Category
Software

Shredded_LockTbl: Blocking Transaction Lock Shredding Target Table

The TargetDBName.Shredded_LockTbl stores shredded lock contention data for the blocking transaction and the first transaction that is blocked by it. The exact name of the target table is specified by the call to the stored procedure LockLogShredder.SP_CreateTable.

 

Shredded_LockTbl Field

Description

DatabaseName

The database containing the object locked by the blocker

TableName

The table locked by the blocker

 

RequestID_1

Byte 1 of the request ID of the blocker

RequestID_2

Byte 2 of the request ID of the blocker

unique_1

Part 1 of the transaction ID of the blocker

unique_2

Part 2 of the transaction ID of the blocker

PEVproc

PE vproc of the blocker

TransactionState

Transaction state of the blocker

Possible values include the following:

  • Inactive
  • Active
  • DontAbort
  • Aborting
  • PKAborted
    		•

    TransactionStartTime

    Transaction start time of the blocker

    StepLevel

    The dispatcher-recorded step level of the blocker

    StepNum_1

    Part 1 of the step number of the blocker

    StepNum_2

    Part 2 of the step number of the blocker

    StatementNo

    The statement number of the blocker

    SessionID_1

    Part 1 of the session ID of the blocker

    SessionID_2

    Part 2 of the session ID of the blocker

    SessionID_Combined

    Combined session ID of blocker, as recorded in SessionTbl

    UserName

    User name of the blocker

    AccountName

    Account name of the blocker

    ExpReqProc

    Originating vproc of the internal express request lock of the blocker

    seq_1

    Part 1 of the logon sequence number of a HUT lock blocker

    seq_2

    Part 2 of the logon sequence number of a HUT lock blocker

    LogicalHostId

    Logical host ID of the blocker

    ZoneId

    Secure zone ID of the blocker

    MessageClass

    Internal AMP message class ID of the blocker

    MessageKind

    Internal AMP message kind ID of the blocker

    MessageClassName

    Internal AMP message class name of the blocker

    Possible values include the following:

  • SYSMSGSTPCLASS: Messages containing Parser-generated steps sent to the AMPs from the Dispatcher
  • SYSMSGS2SCLASS: Messages sent between steps during row redistribution or count cascading
  • SYSMSGHUTCLASS: Messages sent between host utilities and AMPs
  • SYSMSGEXPCLASS: Messages sent as express requests between the Parser and AMPs
    		•

    MessageKindName

    Internal AMP message kind name of the blocker

    WorkLevel

    Operation work level number of the blocker

    OperationType

    Type of SQL operation being performed by the blocker

    JobType

    Job types

    Possible values include the following:

  • AmpStep
  • ExpressRequest
  • Utilities
  • Archive/Restore
  • Undetermined
    		•

    SQLType

    Type of SQL performed in the operation of the blocker

    LockTypeRequested

    Type of lock requested by the blocker

    Possible values include the following:

  • Access
  • Read
  • Write
  • Exclusive
  • IAccess (intentional access)
  • IRead (intentional read)
  • IWrite (intentional write)
  • IExclusive (intentional exclusive)
  • Read4X (read for exclusive)
  • IRead4X (intentional read for exclusive)
  • Read4W (read for write)
  • ReadHL (read host utility lock)
  • IReadHL (intentional read host utility lock)
    		•

    LockObjectRequested

    Type of object on which the blocker requested a lock

    Possible values include the following:

  • D (database)
  • T (table)
  • R (row hash)
  • RK (row hash in one partition)
  • RN (row key range)
  • RP (row hash in partition range)
  • TP (table partition range)
    		•

    LockKindRequested

    The kind of lock requested by the blocker

    Possible values include the following:

  • Rowhash range
  • Rowhash on all partitions
  • Rowkey
  • Rowkey range (locked for a range of rows)
  • Rowhash and partition range
  • Partition range with complete rowhash range
  • Rowkey range for internal use
    		•

    BlkedDatabaseName

    The database containing the object on which the blocked request is waiting for a lock

    BlkedTableName

    The table on which the blocked request is waiting for a lock

    BlkedRequestID_1

    Byte 1 of the ID of the blocked request

    BlkedRequestID_2

    Byte 2 of the ID of the blocked request

    Blkedunique_1

    Part 1 of the transaction ID of the blocked request

    Blkedunique_2

    Part 2 of the transaction ID of the blocked request

    BlkedPEVproc

    PE vproc of the blocked request

    BlkedTransactionState

    Transaction state of the blocked request

    Possible values include the following:

  • Inactive
  • Active
  • DontAbort
  • Aborting
  • PKAborted
    		•

    BlkedTransactionStartTime

    Start time of the blocked transaction

    BlkedStepNum_1

    Part 1 of the step number of the blocked request

    BlkedStepNum_2

    Part 2 of the step number of the blocked request

    BlockedSessionID_1

    Part 1 of the Session ID of the blocked request

    BlockedSessionID_2

    Part 2 of the session ID of the blocked request

    BlockedSessionID_Combined

    Combined session ID of the blocked request as recorded in SessionTbl

    BlockedUserName

    User name for the blocked request

    BlockedAccountName

    Account name for the blocked request

    BlockedExpReqProc

    Originating vproc of the internal express request lock of the blocked request

    Blockedseq_1

    Part 1 of the logon sequence number for the blocked request

    Blockedseq_2

    Part 2 of the logon sequence number for the blocked request

    BlockedLogicalHostId

    Logical host ID of the blocked request

    BlockedStartPartition

    Starting partition number for the partition-level lock of the blocked request

    BlockedStartRowhashInt

    Starting row hash number for the partition-level lock of the blocked request

    BlockedEndPartition

    Ending partition number for the partition-level lock of the blocked request

    BlockedEndRowHashInt

    Ending row hash number for the partition-level lock of the blocked request

    BlockedLockTypeRequested

    Type of lock requested by the blocked request

    Possible values include the following:

  • Access
  • Read
  • Write
  • Exclusive
  • IAccess (intentional access)
  • IRead (intentional read)
  • IWrite (intentional write)
  • IExclusive (intentional exclusive)
  • Read4X (read for exclusive)
  • IRead4X (intentional read for exclusive)
  • Read4W (read for write)
  • ReadHL (read host utility lock)
  • IReadHL (intentional read host utility lock)
    		•

    BlockedLockObjectRequested

    The type of object on which the blocked request needs a lock

    Possible values include the following:

  • D (database)
  • T (table)
  • R (row hash)
  • RK (row hash in one partition)
  • RN (row key range)
  • RP (row hash in partition range)
  • TP (table partition range)
    		•

    BlockedLockKindRequested

    The kind of lock requested by the blocked request

    Possible values include the following:

  • Rowhash range
  • Rowhash on all partitions
  • Rowkey
  • Rowkey range (locked for a range of rows)
  • Rowhash and partition range
  • Partition range with complete rowhash range
  • Rowkey range for internal use
    		•

    LockDelay

    Delay time of the blocked request in centiseconds

    LocalDeadLock

    True if a local deadlock is detected, otherwise false

    GlobalDeadLock

    True if a global deadlock is detected, otherwise false

    MultipleBlocker

    True if there are multiple blocked requests, otherwise false

    AmpVproc

    AMP number where the lock contention occurred

    QueryID

    Unique query ID for joining with related lock shredded tables or other DBQL tables

    CollectTimeStamp

    Timestamp of when the lock contention was recorded

    DelayStartTime

    Timestamp of when the lock delay began

    ErrorText

    Error text associated with the lock contention, if any

    AbortFlag

    True if the lock contention has resulted in an abort

    ErrorCode

    Error code associated with the lock contention, if any

    TransactionCount

    Number of additional blocked transactions

    Investigating Deadlocks: Querying Shredded_LockTbl

    In the following scenario, the user received an error message indicating an AMP deadlock, which happens if two sessions simultaneously try to lock the same table. The following example provides further information about the aborted request that caused the error message.

    SELECT
    QueryId,
    DatabaseName,
    TableName,
    UserName,
    AccountName,
    OperationType,
    JobType,
    LockTypeRequested,
    LockObjectRequested,
    ErrorText,
    AbortFlag,
    ErrorCode
    FROM TargetDBName.Shredded_LockTbl
    WHERE AbortFlag = 1 and ErrorCode = 2631;

    The following output provides details about the blocking transaction and the first transaction that is blocked by it.

    *** Query completed. 3 rows found. 8 columns returned. 
     *** Total elapsed time was 1 second.
     
     
         queryid  307184741735818131.
    DatabaseName USER1
       TableName t3
       UserName USER1
    AccountName ?
    OperationType Asynch Abort Release lock message
    JobType AmpStep
    LockTypeRequested Write
    LockObjectRequested R
       ErrorText Amp deadlocking
       AbortFlag TRUE
       ErrorCode        2631
         queryid  307184741735818130.
    DatabaseName USER1
       TableName t3
        UserName USER1
    AccountName ?
    OperationType Asynch Abort Release lock message
    JobType AmpStep
    LockTypeRequested Write
    LockObjectRequested R
       ErrorText Amp deadlocking
       AbortFlag TRUE
       ErrorCode        2631