IDKCS007930
Unable to logon database. Any logon attempts to database are getting hung.​ WrittenSep 11, 2017

 
Attachments0
  

Product Categories
Teradata Database

 Problem
Any logon attempts to database are getting hung.
 
Sessions which are already logged on database can run SQL.
System is not busy. (sar shows high CPU idle, awtmon shows low AWT usage)
 
 
lokdisp shows a session is holding table write lock on DBC.SessionTbl and blocking other sessions for long time.
 
 
Following is sample output from lokdisp blocker command
 
 
---------------- AMP 2 REPORTS 2 LOCK ENTRIES -------------
Number of Blocked Trans displayed :      2
=========================================
Blocked Trans : 30719 002D6A22
   Number of blockers displays :       1
   Number of blockers exists   :       1
   Blocker Trans : 30719 002D6A12
           lock mode       : Write
           lock status     : Granted
           lock objectType : Table
           lock objectID   : DBID      : 00000001
                           : DBNAME    : DBC
                           : TableID   : 0000004C,0000
                           : TableName : SESSIONTBL
                           : RowHash1  : 00000000
                           : RowHash2  : 00000000
 
 
 
 
 
 
 
 

Environment/Conditions/Configuration
TDBMS 15.10 and later

Cause
DROP ROLE and DROP TABLE were submitted at a time.
 
DROP ROLE and DROP TABLE can cause deadlock on DBC.AccessRights.
And DROP ROLE places table write lock on DBC.SessionTbl before locking DBC.AccessRights.
 
Therefore DROP ROLE keeps on holding write lock on DBC.SessionTbl and blocking logon of other sessions when it gets deadlock on DBC.AccessRights.
 
Teradata database automatically detects and aborts deadlock.
But in this case, customer ran DROP ROLE and DROP TABLE in parallel many times, which caused the deadlock scenario below many 
times and customer was unable to logon system for long time.
 
 
 
Deadlock scenario between DROP ROLE and DROP TABLE on DBC.AccessRights is as follows:
 
 
1) DROP ROLE places a Rowhash+All Partitions lock for write.
    (DBC.AccessRights is fallback table and Rowhash lock is placed in order of primary and fallback AMP)
    DROP ROLE placed write lock on primary AMP
 
2) DROP TABLE places a single partition lock for write on all AMPs in parallel(lock can be placed on fallback AMP prior to primary).
    DROP TABLE placed write lock on fallback AMP
 
3) DROP ROLE tried to place write lock on fallback AMP but blocked by 2)
 
4) DROP TABLE tried to place write lock on primary AMP but blocked by 1)
 
5) DROP ROLE and DROP TABLE were waiting for each other on DBC.AccessRights >>> deadlock
    (And DROP ROLE holding table write lock on DBC.SessionTbl)
 
 
 

Solution
Please avoid running DROP ROLE and DROP TABLE at a time.
 
 
 
 

Special Considerations

Additional Information
- ​PI column for DBC.AccessRights = UserId and DatabaseId(pre-TD15.0),  UserId(TD15.0 and later) .
- Partition Level Lock(PLL) is implemented on TD15.10 and later.
Thus locking range/procedure depends on TDBMS version.
 
 
Locking range/procedure can be viewed in explain:
 
 
--- from explain of DROP ROLE on TD15.10 -----
  4) We lock DBC.ConnectRulesTbl for write, we lock DBC.RoleGrants for
     write, we lock DBC.SessionTbl for write, we lock DBC.Roles for
     write on a RowHash, and we lock DBC.AccessRights for write on a
     RowHash in all partitions.
 
 
--- from explain of DROP TABLE on TD15.10 -----
  4) We lock DBC.AccessRights for write on a reserved RowHash in a
     single partition to prevent global deadlock.
  5) We lock DBC.Indexes for write on a RowHash, we lock
     DBC.DBCAssociation for write on a RowHash, we lock DBC.TVFields
     for write on a RowHash, we lock DBC.TVM for write on a RowHash, we
     lock DBC.DBase for read on a RowHash, we lock DBC.RCEvent for
     write on a RowHash, we lock DBC.Dependency for write on a RowHash,
     we lock DBC.ObjectUsage for write on a RowHash, and we lock
     DBC.AccessRights for write on a single partition.
 
 
 
 
 
PI column for DBC.AccessRights is UserId(RoleId).
So DROP ROLE places lock on 1 AMP(and its fallback) only to delete accessrights for the role.
On the other hand, multiple users may have accessrights on a table, which means accessright records on a table may be distributed across multiple AMPs
So DROP TABLE places lock on all AMPs to delete accessrights for the table.