16.10 - Pseudo Table Locks - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

You can think of a pseudo table as an alias for the physical table it represents. (Note that the word pseudo modifies the table, and not the lock.) Pseudo tables provide a mechanism for queueing data dictionary rowhash locks to avoid the global deadlocks that can otherwise occur when Teradata places locks on data dictionary rows (see Deadlock).

When DDL needs to lock a rowhash for both the primary and fallback of a dictionary table, the system automatically places a rowhash-level pseudo table lock. Think of a rowhash-level pseudo table lock as an intention lock, that is, it indicates that the request intends to place a rowhash lock on both the primary and fallback of a dictionary table.

Pseudo table locks enable the sequential locking of rowhashes on both primary and fallback rows that are on different AMPs in a parallel database architecture. Without pseudo table locking, if multiple users simultaneously submit DDL requests that need to place a rowhash lock on both the primary and the fallback of a data dictionary table, a deadlock can occur. If multiple requests are sent in parallel and need to lock the same rowhash of a dictionary table, they are likely to arrive at the primary and fallback AMPs holding the rowhash in different sequential orders. One request may obtain the rowhash lock on the primary AMP first and the other on a fallback AMP first, thus creating a deadlock.

Pseudo Table Locking Properties

Pseudo table locking has the following properties:

  • Each dictionary table has a system-assigned table ID hash code alias. This hash code constitutes a pseudo table.
  • The system places pseudo table locks on this table ID hash code for a lock to be placed at the rowhash-level. Rowhash pseudo table locks are placed only on data dictionary tables with fallback.
  • The table ID hash codes are evenly distributed across the AMPs.
  • Each AMP is a gatekeeper for those tables to which it has been assigned table ID hashcodes.
  • The locks are always dispatched to the relevant gatekeeper AMP for pseudo table lock processing.
  • Pseudo table locks are placed on various data dictionary tables at the rowhash-level for the following DDL statements:
    • CREATE HASH INDEX
    • CREATE JOIN INDEX
    • CREATE TABLE
    • DROP HASH INDEX
    • DROP JOIN INDEX
    • DROP MACRO
    • DROP PROCEDURE
    • DROP TABLE
    • DROP VIEW

      It would be more accurate to refer to these locks as pseudo rowhash locks rather than rowhash-level pseudo table locks, but the EXPLAIN phrase text does not follow this convention, and this description follows the terminology used by the EXPLAIN phrase text (see EXPLAIN Request Modifier Phrase Terminology).

Rowhash-Level Pseudo Table Locking

This set of scenarios uses simple CREATE TABLE and DROP TABLE requests to demonstrate how Teradata Database uses rowhash-level pseudo table locking.

The following example is an EXPLAIN report for a simple CREATE TABLE definition for table t4.

EXPLAIN CREATE TABLE t4 (a INTEGER, b INTEGER);
Explanation
-----------------------------------------------------------------------
  1) First,  we lock PLS.t4 for exclusive use.   
  2)  Next, we lock a distinct DBC."pseudo table" for write on a RowHash 
      for deadlock prevention, we lock a distinct DBC."pseudo table" for 
      write on a RowHash for deadlock prevention, we lock a distinct 
      DBC."pseudo table" for write on a RowHash for deadlock prevention, 
      and we lock a distinct DBC."pseudo table" for read on a RowHash 
      for deadlock prevention.   
  3)  We lock DBC.Indexes 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, and we lock DBC.AccessRights 
      for write on a RowKey.   
  4) We execute the following steps in parallel.
       1) We do a single-AMP ABORT test from DBC.DBase by way of the
          unique primary index "Field_1 = 'PLS'" with a residual
          condition of ("'00000104'XB= DBC.DBase.Field_2").
       2) We do a single-AMP ABORT test from DBC.TVM by way of the
          unique primary index "Field_1 = '00000104'XB,
          Field_2 = 'T4'".
       3) We do an INSERT into DBC.Indexes (no lock required).
       4) We do an INSERT into DBC.TVFields (no lock required).
       5) We do an INSERT into DBC.TVFields (no lock required).
       6) We do an INSERT into DBC.TVM (no lock required).
       7) We INSERT default rights to DBC.AccessRights for PLS.t4.
  5) We create the table header.
  6) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.

CREATE TABLE does not place a proxy lock before it applies the table-level lock for the table in step 1. That is unnecessary because the table does not exist until it has been created, so no other user could be trying to access it.

Step 2 places one pseudo lock for a rowhash in the pseudo table on one AMP. Every DDL request for the same rowhash must then go through this pseudo table, so deadlock does not occur from attempt to lock both the primary and fallback copies. Step 2 is an all-AMP lock step. Each AMP is passed the same list of rowhash locks and only the active primary AMP of the rowhash places the lock.

Step 3 places locks on the actual Indexes, TVFields, TVM, Dbase, and AccessRights dictionary tables. Step 3 is an all-AMP step and only the primary and fallback AMPs that own the rowhashes place locks.

Notice that the number of rowhash-level locks placed is fewer in step 2 than in step 3. The EXPLAIN text does not indicate which rowhash-level lock in step 3 corresponds to the lock in the step 2. Furthermore, the rowhash-level locks in step 2 are not placed in exactly the same order as in step 3, based on matching READ and WRITE locks, because of the way the locks are sorted, which is in table/rowhash order. For step 2, the table is the same for all the row hashes, but in step 3 the table is different for each row hash.

Step 2 places fewer rowhash-level locks than step 3 because DBC.TVFields and DBC.Indexes have the same primary indexes, so the rowhash is the same for both tables and only one rowhash-level lock is required on the pseudo table. But in step 3, two actual rowhash-level locks must be placed because they are for separate dictionary tables.

The following example is an EXPLAIN report for a simple DROP TABLE request where no privileges have been granted explicitly to a user or database on the table:

EXPLAIN DROP TABLE t4;
Explanation
-----------------------------------------------------------------------
  1) First, we lock PLS.t4 for exclusive use on a reserved rowHash to
     prevent global deadlock.
  2) Next, we lock PLS.t4 for exclusive use.
  3)  We lock a distinct DBC."pseudo table" for write on a RowHash for 
      deadlock prevention, we lock a distinct DBC."pseudo table" for 
      write on a RowHash for deadlock prevention, we lock a distinct 
      DBC."pseudo table" for write on a RowHash for deadlock prevention, 
      we lock a distinct DBC."pseudo table" for write on a RowHash for 
      deadlock prevention, we lock a distinct DBC."pseudo table" for 
      read on a RowHash for deadlock prevention, and we lock a distinct 
      DBC."pseudo table" for write on a RowHash for deadlock prevention. 
  4)  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 RowKey.   
  5) We drop the table header and the data in the table PLS.t4.
  6) We execute the following steps in parallel.
       1) We do a single-AMP ABORT test from DBC.DBase by way of the
          unique primary index "Field_1 = 'PLS'" with a residual
          condition of ("'00000104'XB= DBC.DBase.Field_2").
       2) We do a single-AMP ABORT test from DBC.TVM by way of the
          unique primary index "Field_1 = '00000104'XB, Field_2 = 'T4'"
          with a residual condition of ("'0000270B0000'XB=
          DBC.TVM.Field_5").
       3) We do a single-AMP ABORT test from DBC.TVM by way of the
          unique primary index "Field_1 = '00000104'XB, Field_2 = 'T4'"
          with a residual condition of ("DBC.TVM.Field_33 > 0").
  7) We lock DBC.StatsTbl for write on a RowHash.
  8) We lock DBC.ObjectUsage for write on a RowHash.
  9) We do a single-AMP DELETE from DBC.StatsTbl by way of the primary
     index "{LeftTable}.Field_2 = '0000270B0000'XB" with no residual
     conditions.
 10) We do a single-AMP DELETE from DBC.ObjectUsage by way of the
     primary index "{LeftTable}.Field_1 = '00000104'XB,
     {LeftTable}.Field_2 = '0000270B0000'XB" with a residual condition
     of ("DBC.ObjectUsage.UsageType = 'STA'").
 11) We execute the following steps in parallel.
      1) We do a single-AMP DELETE from DBC.TVFields by way of the
         primary index "Field_1 = '0000270B0000'XB" with no residual
         conditions.
      2) We do a single-AMP DELETE from DBC.Indexes by way of the
         primary index "Field_1 = '0000270B0000'XB" with no residual
         conditions.
      3) We do a single-AMP DELETE from DBC.DBCAssociation by way of
         the primary index "Field_1 = '0000270B0000'XB" with no
         residual conditions.
      4) We do a single-AMP DELETE from DBC.TVM by way of the unique
         primary index "Field_1 = '00000104'XB, Field_2 = 'T4'" with no
         residual conditions.
      5) We do a single-AMP DELETE from a single partition of
         DBC.AccessRights by way of the primary index "Field_1 =
         '00000104'XB, Field_2 = '00000104'XB, Field_3 =
         '0000270B0000'XB" with a residual condition of (
         "DBC.AccessRights.Field_3 = '0000270B0000'XB") (no lock
         required).
      6) We do a single-AMP DELETE from DBC.Dependency by way of the
         primary index "Field_1 = '0000270B0000'XB" with no residual
         conditions.
      7) We do a single-AMP DELETE from DBC.ObjectUsage by way of the
         primary index "Field_1 = '00000104'XB, Field_2 =
         '0000270B0000'XB" with no residual conditions.
      8) We do an INSERT into DBC.RCEvent.
 12) We spoil the statistics cache for the table, view or query.
 13) We end logging on PLS.t4.
 14) We spoil the parser's dictionary cache for the table.
 15) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.

Unlike the CREATE TABLE request, multiple users might be trying to access or DROP table t4, so it is necessary to coordinate them using a proxy lock, that is, locking a reserved rowhash in step 1.

Step 3 places one pseudo lock for a rowhash in the pseudo table on one AMP and every DDL request for the same rowhash must then go through this pseudo table, so deadlock does not occur from attempts to lock both the primary and fallback copies. Step 2 is an all-AMP lock step. Each AMP is passed the same list of rowhash locks and only the active primary AMP of the rowhash places the lock.

Step 4 places rowhash-level locks on the actual TVM, TVFields, Indexes, Dbase, AccessRights, and so on dictionary tables. This is an all-AMPs step, but for a particular rowhash that is mentioned in the step, only the primary and fallback AMPs that own that rowhash place a lock for that rowhash, even though the step is assigned to all AMPs.

For this request, the number of rowhash-level locks placed in step 3 is fewer than the number placed in step 4. The EXPLAIN text does not indicate which rowhash-level lock in step 3 corresponds to the lock in the step 2. Furthermore, the rowhash-level locks in step 3 are not placed in exactly the same order as in step 4 based on matching READ and WRITE locks, because of the way the locks are sorted, which is in table/rowhash order.

Step 3 places fewer rowhash-level locks than step 4 because DBC.TVFields and DBC.Indexes have the same primary indexes, so the rowhash is the same for both tables and only one rowhash-level lock is required on the pseudo table. But two actual rowhash-level locks must be placed in step 4 because they are for separate dictionary tables.

For both the CREATE TABLE and DROP TABLE cases, the locking becomes increasingly more complicated if there are referential integrity constraints defined for the table because of the additional parent and child and dictionary tables that must be locked. Options that involve such things as journals and other dictionary tables that must be locked complicate the picture still further.