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)