How to find a particular table in Teradata is locked using SQL query?

Database
Enthusiast

How to find a particular table in Teradata is locked using SQL query?

Hi All,

Query is running very long due to some locks and normally we used to check using PMON and find the session which is blocking the current query.

what are the different way by which we can check whether particular table in Teradata is locked ?

Thnaks
sakthi

9 REPLIES
Fan

Re: How to find a particular table in Teradata is locked using SQL query?

Run command CNSTOOL on the db server to showlocks both data base level and table level.
Enthusiast

Re: How to find a particular table in Teradata is locked using SQL query?

I dont think that you can find such an information with a SQL.Would like to request forum members to share such SQL ,if there is any .Apart from using SHOWLOCK which is a Teradata utility for showing Host Utility (HUT) locks, there are addition utilities such as
Lock Display for real-time database locks and Locking Logger utility which captures locking information in a database table.
Refer to Teradata utilities volume 2 G-S pdf for more info about these utilities and their pros/cons.
To use above utilities you should be familiar with the Teradata Database console running the Database Window (DBW) and your client (host) system.

Additionally Teradata Manager Alerts can be triggered,emailing you any present locking contention on the system.
Hope it helps.
BPP
Enthusiast

Re: How to find a particular table in Teradata is locked using SQL query?

Locking Logger is a good tool to use. it captures all the blocks and locks.
Enthusiast

Re: How to find a particular table in Teradata is locked using SQL query?

I am having a situation where in multiple loads are trying to update an Audit table and resulting in deadlock scenario. 

Some facts regarding the loading:

1. The load's can't be scheduled to diff time as the data feeds from the source can be pushed in the landing zone any time .  And as on when the feeds arrive, processing gets kicked off.

2. Loading is being done thru Informatica. In the map, BTEQ script is called which performs the loading and at the end it writes to Audit Table.

3. Creating separate Audit table is not feasible as per business norms.

What i think:

In the BTEQ script, can we put a sql which checks for the current lock in the Audit Table. If there is a lock, we can fork a Sleep process for say 60-120 secs and then perform the loading. So for achieving this, i need to know the exact DBC table/view through which I can query and come to know the result of current lock scenario of the Audit table. 

Can you please help me in this regard.

Regards,

Sudipta.

Enthusiast

Re: How to find a particular table in Teradata is locked using SQL query?

Hi,

Whether the MLOAD HUT locks can be viewed using SHOWLOCKS utility?

Please advise.

Junior Contributor

Re: How to find a particular table in Teradata is locked using SQL query?

@Sudipta:

There's no table with lock information, but you might use the UDF interface to PMon data in the syslib database:

MonitorSession plus IdentifyTable/User/Session

@trustngs:

HUT locks are backup locks, afaik only a CHECKTABLE AT LEVEL PENDINGOPS can show MLoad locks.

Dieter

Enthusiast

Re: How to find a particular table in Teradata is locked using SQL query?

Thanks Dieter. Got clarified now.

Enthusiast

Re: How to find a particular table in Teradata is locked using SQL query?

Thanks Dieter. Will keep this is mind and approach further.

dhv
Teradata Employee

Re: How to find a particular table in Teradata is locked using SQL query?

As many people have often requested instructions upon how to figure out which tables were blocked, at which time, by whom, this is a simple tutorial using the 20-year old tool LOCK LOGGER. I did not have time to explain everything, but if you need more detailed info, please contact me.

1) Turn on the LOCKING LOGGER Flag via dbscontrol:

DBS Control Record - General Fields:

     1. Version                        = 10

     2. SysInit                        = TRUE        (2013-11-04 10:55)

     3. DeadLockTimeout                = 240         (Seconds)

     4. (Reserved for future use)

     5. HashFuncDBC                    = 6           (Universal)

     6. (Reserved for future use)

     7. (Reserved for future use)

     8. SessionMode                    = 0           (Teradata mode)

     9. LockLogger                     = FALSE       (Disabled) <---- THIS MUST BE TRUE

    10. RollbackPriority               = FALSE       (Disabled)

   ...

    26. ExternalAuthentication         = 0           (On)

    27. IdCol Batch Size               = 100000      (Default value)

    28. LockLogger Delay Filter        = FALSE

    29. LockLogger Delay Filter Time   = 0           (Seconds; Default value)

    30. ObjectUseCountCollectRate      = 10          (Minutes; Recommended)

    31. LockLogSegmentSize             = 64          (KB; Minimum value)

    32. CostProfileId                  = 0

There are 3 flags of interest (9), (28), and (29). Flag (31) is optional.

A/ Flag 9 allows LOCKING LOGGER to record contention into memory and save into table

B/ Flag 28 allows the filtering of contention time, so that less worthy data can be skipped

C/ Flag 29 works in conjunction with flag 28. It is the minimum contention time to be recorded.

A tpareset is needed one time to enable these flags to take effects.

Enter a command, HELP, or QUIT:

> modify gen 9 = true

modify gen 9 = true

The LockLogger field has been modified from FALSE to TRUE.

NOTE: This change will become effective after the next DBS restart.

Enter a command, HELP, or QUIT:

> write

write

Locking the DBS Control GDO...

Updating the DBS Control GDO...

Enter a command, HELP, or QUIT:

> quit

quit

Exiting DBSControl...

2) Now, restart the DBS and verify the flag being ON.

unzen:~ # tpareset "RESTART DBS TO ENABLE LOCK LOGGER"

        You are about to restart the database

                 on the system

                    'unzen'

        Do you wish to continue (default: n) [y,n] Y

unzen:~ #

DBS Control Record - General Fields:

     1. Version                        = 10

     2. SysInit                        = TRUE        (2013-11-04 10:55)

     3. DeadLockTimeout                = 240         (Seconds)

     4. (Reserved for future use)

     5. HashFuncDBC                    = 6           (Universal)

     6. (Reserved for future use)

     7. (Reserved for future use)

     8. SessionMode                    = 0           (Teradata mode)

     9. LockLogger                     = TRUE        (Enabled) <--------- ENABLED

    10. RollbackPriority               = FALSE       (Disabled)

    11. MaxLoadTasks                   = 5

    12. RollForwardLock                = FALSE       (Disabled)

3) The LOCK LOGGER is a historical recording tool, so it will track data as long as the buffer (64K, flag 31) can hold. The entries will be wrapped around this buffer when it is full. So, there could be as much data as a week worth, or a minute worth depending how filled up the buffer is. Another thing to realize is that this information is PER-AMP. So, there will be multiple repeating entries of the same lock contentions across many amps, say, in the case of a ALL-AMP lock. Or there might just be one if it is a hashed lock on just one amp.

Now, once the timing of the contention is known, afterwards, the tool DUMPLOCKLOG can be used to extract the lock contentions which have been recorded since the last TPARESET (These are stored in memory, so a reset will blow all away). DUMPLOCKLOG will transfer these data from memory into a user-table for querying, selecting, etc..

Input Supervisor Command:

> start dumplocklog

start dumplocklog

Started 'dumplocklog' in window 1

 at Tue Dec 17 17:11:31 2013

Input Supervisor Command:

Input Supervisor Command:

unzen:~ # cnsterm 1

Attempting to connect to CNS...Completed.

Hello

 _______

    |                                |              |

    |    ___     __     ____         |    ____    __|__    ____

    |   /      |/  \    ____|    ____|    ____|     |      ____|

    |   ---    |       /    |   /    |   /    |     |     /    |

    |   \___   |       \____|   \____|   \____|     |__   \____|

    Release 15g.00.00.155 Version 15g.00.00.155dr142532_23

    DUMP LOCK LOG Utility (Dec 98)

This utility writes lock log entries in memory to an user specified table.

Enter your logon string: <username,password> (Q/q to quit)

> systemfe,service

systemfe,service

Do you want this utility to run continuously? (Y/N)

> n

n

Enter number of sessions (? For Help, Q/q to Quit):

> *

*

8 sessions will be logged on.

Enter the Character Set (? For Help, Q/q to Quit):

> *

*

Enter the table name where the lock log entries will

be written (? For Help, Q/q to Quit):

> LOCKLOGGER_TEST

LOCKLOGGER_TEST

Do you want this utility to create table "SYSTEMFE"."LOCKLOGGER_TEST"? (Y/N)

> y

y

Table "SYSTEMFE"."LOCKLOGGER_TEST" has been created.

Enter the time constraint to control selection of lock log entries

that were generated AT or LATER than this time YYYYMMDD HHMMSS

(? For Help, Q/q to Quit):

> *

*

Enter the time constraint to control selection of lock log entries

that were generated AT or PRIOR to this time YYYYMMDD HHMMSS

(? For Help, Q/q to Quit):

> *

*

Enter the object constraint for selection of

lock log entries (? For Help, Q/q to Quit):

> *

*

Writing lock log entries to table "SYSTEMFE"."LOCKLOGGER_TEST".

Press <F2> any time to stop the utility.

>

Writing lock log entries to table "SYSTEMFE"."LOCKLOGGER_TEST".

Press <F2> any time to stop the utility.

> 5 rows have been inserted to table "SYSTEMFE"."LOCKLOGGER_TEST".

*** DumpLockLog is terminated ***

4) The same execution of DUMPLOCKLOG above could be done in batch. There is a BATCH version called "dumplocklogb" which could take in the input above in batch mode. I will go into this later.

5) Once the execution is done, the table will be populated and we can query on it:

  1  BTEQ 12.00.00.02 Tue Dec 17 18:27:12 2013

  2

  3  +---------+---------+---------+---------+---------+---------+---------+----

  4  .logon unzen/dbc,

  5

  6   *** Logon successfully completed.

  7   *** Teradata Database Release is 15g.00.00.155

  8   *** Teradata Database Version is 15g.00.00.155dr142532_23

  9   *** Transaction Semantics are BTET.

 10   *** Character Set Name is 'ASCII'.

 11

 12   *** Total elapsed time was 1 second.

 13

 14  +---------+---------+---------+---------+---------+---------+---------+----

 15  database systemfe;

 16

 17   *** New default database accepted.

 18   *** Total elapsed time was 1 second.

 19

 20

 21  +---------+---------+---------+---------+---------+---------+---------+----

 22  show table LOCKLOGGER_TEST;

 23

 24   *** Text of DDL statement returned.

 25   *** Total elapsed time was 1 second.

 26

 27  ---------------------------------------------------------------------------

 28  CREATE SET TABLE SYSTEMFE.LOCKLOGGER_TEST ,FALLBACK ,

 29       NO BEFORE JOURNAL,

 30       NO AFTER JOURNAL,

 31       CHECKSUM = DEFAULT,

 32       DEFAULT MERGEBLOCKRATIO

 33       (

 34        BEGDATE DATE FORMAT 'YY/MM/DD' NOT NULL,

 35        BEGTIME FLOAT FORMAT '99:99:99.999' NOT NULL,

 36        DELAY FLOAT FORMAT '99:99:99.999' NOT NULL,

 37        DBID BYTE(4) NOT NULL,

 38        TID BYTE(6) NOT NULL,

 39        BLKDLOGHOST SMALLINT NOT NULL,

 40        BLKDSESSNO INTEGER NOT NULL,

 41        BLKDLEVEL CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

 42        BLKDMODE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

 43        BLKINGLOGHOST SMALLINT NOT NULL,

 44        BLKINGSESSNO INTEGER NOT NULL,

 45        BLKINGLEVEL CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

 46        BLKINGMODE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

 47        PROCESSOR SMALLINT FORMAT 'ZZZZ9' NOT NULL,

 48        DEADLOCK CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

 49        MULTIPLEBLOCKER CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NUL

 50  L,

 51        STMTTYPE VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)

 52  PRIMARY INDEX ( BEGDATE ,BEGTIME );

 53

 54

 55  +---------+---------+---------+---------+---------+---------+---------+----

 56  /*

 57  sel count(*) from LOCKLOGGER_TEST;

 58

 59  sel delay, count(*)

 60  from LOCKLOGGER_TEST

 61  group by 1

 62  order by  1;

 63  */

 64

 65  SEL DatabaseNameI (format 'x(30)'),

 66      TVMNameI (format 'x(30)'),

 67       delay (FORMAT '-----------9'), count(*)

 68  from LOCKLOGGER_TEST L,

 69       dbc.DBase D, dbc.TVM T

 70  group by 1,2,3

 71  order by  1 ,2,3

 72  where delay > 0 and

 73     L.dbid = D.DatabaseId AND

 74     T.TVMId = l.tid;

 75

 76   *** Query completed. 3 rows found. 4 columns returned.

 77   *** Total elapsed time was 1 second.

 78

 79  DatabaseNameI                  TVMNameI                              DELAY

 80  ------------------------------ ------------------------------ ------------

 81  DBC                            SW_EVENT_LOG                              0

 82  SYSTEMFE                       T1                                        6

 83  SYSTEMFE                       T1                                        6

 84

 85  +---------+---------+---------+---------+---------+---------+---------+----

 86

 87  .os rm unzen_LOCKLOGGER_TEST.txt

 88  unzen_LOCKLOGGER_TEST.txt: No such file or directory

 89   *** Warning: System call failed.

 90  +---------+---------+---------+---------+---------+---------+---------+----

 91  .set width 200

 92  +---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--

 93  .export report file=unzen_LOCKLOGGER_TEST.txt

 94   *** To reset export, type .EXPORT RESET

 95  +---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--

 96

 97  sel

 98     BEGDATE,

 99     BEGTIME,

100     DatabaseNameI (format 'x(10)'),

101     TVMNameI (format 'x(20)'),

102     delay (FORMAT '------9'),

103     BLKDSESSNO,

104     BLKDLEVEL,

105     BLKDMODE,

106     BLKINGSESSNO,

107     BLKINGLEVEL,

108     BLKINGMODE,

109     PROCESSOR,

110     DEADLOCK,

111     MULTIPLEBLOCKER,

112     STMTTYPE

113  from LOCKLOGGER_TEST L,

114       dbc.DBase D, dbc.TVM T

115  where delay > 0 and

116     L.dbid = D.DatabaseId AND

117     T.TVMId = l.tid

118  order by begdate, begtime;

119

120   *** Query completed. 5 rows found. 15 columns returned.

121   *** Total elapsed time was 1 second.

122

123

124  +---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--

125

126  .export reset

127   *** Output returned to console.

128  +---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--

129

130  .logoff

131   *** You are now logged off from the DBC.

132  +---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--

133  .quit

134   *** Exiting BTEQ...

135   *** RC (return code) = 0

6) Here's the sample data of contention  recorded:

 1   BEGDATE      BEGTIME DatabaseNameI TVMNameI               DELAY  BLKDSESSNO BLKDLEVEL BLKDMODE BLKINGSESSNO BLKINGLEVEL BLKINGMODE PROCESSOR DEADLOCK MULTIPLEBLOCKER STMTTYPE

 2  -------- ------------ ------------- -------------------- ------- ----------- --------- -------- ------------ ----------- ---------- --------- -------- --------------- --------------------

 3  13/12/18 02:20:52.850 DBC           SW_EVENT_LOG               0           0 RowHash   Wr                  1 RowHash     Wr                 5 N        N               UNKNOWN

 4  13/12/18 02:20:55.600 DBC           SW_EVENT_LOG               0           0 RowHash   Wr                  1 RowHash     Wr                 2 N        N               UNKNOWN

 5  13/12/18 02:20:59.320 DBC           SW_EVENT_LOG               0           0 RowHash   Wr                  1 RowHash     Wr                 1 N        N               UNKNOWN

 6  13/12/18 02:21:59.690 SYSTEMFE      T1                         6        1001 RowHash   Ac               1002 Database    Ex                 7 N        N               Update

 7  13/12/18 02:22:10.110 SYSTEMFE      T1                         6        1001 RowHash   Ac               1003 Database    Ex                 7 N        N               Update