Pseudo Table Locks - Teradata Vantage

A pseudo table represents a physical table. You can think of a pseudo table as an alias for the physical table. (The word pseudo modifies the table, 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 must lock a rowhash for both the primary and fallback of a dictionary table, the system places a rowhash-level pseudo table lock. Think of a rowhash-level pseudo table lock as an intention lock (that is, a lock that 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, the requests are likely to arrive at the primary and fallback AMPs holding the rowhash in different sequential orders. One request may get the rowhash lock on the primary AMP first and the other on a fallback AMP first, creating a deadlock.

Rowhash-Level Pseudo Table Locking

This set of scenarios uses simple CREATE TABLE and DROP TABLE requests to demonstrate how Vantage 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 DB1.T4 in TD_MAP1 for exclusive use.
  2) Next, we lock a distinct DBC."pseudo table" in
     TD_DATADICTIONARYMAP for read on a RowHash for deadlock prevention,
     we lock a distinct DBC."pseudo table" in TD_DATADICTIONARYMAP for
     write on a RowHash for deadlock prevention, and we lock a distinct
     DBC."pseudo table" in TD_DATADICTIONARYMAP for write on a RowHash
     for deadlock prevention.
  3) We lock DBC.AccessRights in TD_DATADICTIONARYMAP for write on a
     reserved RowHash in a single partition to prevent global deadlock.
  4) We lock DBC.DBase in TD_DATADICTIONARYMAP for read on a RowHash,
     we lock DBC.Maps in TD_DATADICTIONARYMAP for read on a RowHash, we
     lock DBC.TVFields in TD_DATADICTIONARYMAP for write on a RowHash,
     we lock DBC.Indexes in TD_DATADICTIONARYMAP for write on a RowHash,
     we lock DBC.TVM in TD_DATADICTIONARYMAP for write on a RowHash,
     and we lock DBC.AccessRights in TD_DATADICTIONARYMAP for write on
     a single partition.
  5) We execute the following steps in parallel.
       1) We do a single-AMP ABORT TEST step from DBC.DBase by way of
          the unique primary index "Field_1 = 'DB1'" with a residual
          condition of ("'00001904'XB= DBC.DBase.Field_2").
       2) We do a single-AMP ABORT TEST step from DBC.TVM by way of the
          unique primary index "Field_1 = '00001904'XB, Field_2 = 'T4'".
       3) We do an INSERT step into DBC.Indexes (no lock required).
       4) We do an INSERT step into DBC.TVFields (no lock required).
       5) We do an INSERT step into DBC.TVFields (no lock required).
       6) We do a two-AMP ABORT TEST step in TD_DATADICTIONARYMAP from
          DBC.Maps by way of unique index # 4 "Field_3 = 1025" with no
          residual conditions.
       7) We do an INSERT step into DBC.TVM (no lock required).
       8) We INSERT default rights to DBC.AccessRights for DB1.T4.
  6) We create the table header in TD_MAP1.
  7) 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 applying the table-level lock for the table in step 1. That is unnecessary because the table does not exist until created, so no other user can be trying to access the table.

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 is a proxy lock for AccessRights on a single partition.

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

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

Step 2 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 in step 4, two rowhash-level locks must be placed because the rowhash-level locks 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 DB1.t4 in TD_MAP1 for exclusive use on a reserved
     RowHash to prevent global deadlock.
  2) Next, we lock DB1.t4 in TD_MAP1 for exclusive use.
  3) We lock a distinct DBC."pseudo table" in TD_DATADICTIONARYMAP for
     read on a RowHash for deadlock prevention, we lock a distinct
     DBC."pseudo table" in TD_DATADICTIONARYMAP for write on a RowHash
     for deadlock prevention, we lock a distinct DBC."pseudo table" in
     TD_DATADICTIONARYMAP for write on a RowHash for deadlock
     prevention, we lock a distinct DBC."pseudo table" in
     TD_DATADICTIONARYMAP for write on a RowHash for deadlock
     prevention, and we lock a distinct DBC."pseudo table" in
     TD_DATADICTIONARYMAP for write on a RowHash for deadlock
     prevention.
  4) We lock DBC.AccessRights in TD_DATADICTIONARYMAP for write on a
     reserved RowHash in a single partition to prevent global deadlock.
  5) We lock DBC.DBase in TD_DATADICTIONARYMAP for read on a RowHash,
     we lock DBC.TVFields in TD_DATADICTIONARYMAP for write on a
     RowHash, we lock DBC.Indexes in TD_DATADICTIONARYMAP for write on
     a RowHash, we lock DBC.TVM in TD_DATADICTIONARYMAP for write on a
     RowHash, we lock DBC.DBCAssociation in TD_DATADICTIONARYMAP for
     write on a RowHash, we lock DBC.RCEvent in TD_DATADICTIONARYMAP
     for write on a RowHash, we lock DBC.Dependency in
     TD_DATADICTIONARYMAP for write on a RowHash, we lock
     DBC.ObjectUsage in TD_DATADICTIONARYMAP for write on a RowHash,
     and we lock DBC.AccessRights in TD_DATADICTIONARYMAP for write on
     a single partition.
  6) We drop the table header and the data in the table DB1.t4.
  7) We execute the following steps in parallel.
       1) We do a single-AMP ABORT TEST step from DBC.DBase by way of
          the unique primary index "Field_1 = 'DB1'" with a residual
          condition of ("'00001904'XB= DBC.DBase.Field_2").
       2) We do a single-AMP ABORT TEST step from DBC.TVM by way of the
          unique primary index "Field_1 = '00001904'XB, Field_2 = 'T4'"
          with a residual condition of ("'0000470D0000'XB=
          DBC.TVM.Field_5").
       3) We do a single-AMP ABORT TEST step from DBC.TVM by way of the
          unique primary index "Field_1 = '00001904'XB, Field_2 = 'T4'"
          with a residual condition of ("DBC.TVM.Field_33 > 0").
  8) We lock DBC.StatsTbl in TD_DATADICTIONARYMAP for write on a
     RowHash.
  9) We execute the following steps in parallel.
       1) We do a single-AMP DELETE step in TD_DATADICTIONARYMAP from
          DBC.StatsTbl by way of the primary index "{LeftTable}.Field_2
          = '0000470D0000'XB" with no residual conditions.
       2) We do a single-AMP DELETE step in TD_DATADICTIONARYMAP from
          DBC.TVFields by way of the primary index "Field_1 =
          '0000470D0000'XB" with no residual conditions.
       3) We do a single-AMP DELETE step in TD_DATADICTIONARYMAP from
          DBC.Indexes by way of the primary index "Field_1 =
          '0000470D0000'XB" with no residual conditions.
       4) We do a single-AMP DELETE step in TD_DATADICTIONARYMAP from
          DBC.DBCAssociation by way of the primary index "Field_1 =
          '0000470D0000'XB" with no residual conditions.
       5) We do a single-AMP DELETE step in TD_DATADICTIONARYMAP from
          DBC.TVM by way of the unique primary index "Field_1 =
          '00001904'XB, Field_2 = 'T4'" with no residual conditions.
       6) We do a single-AMP DELETE step in TD_DATADICTIONARYMAP from a
          single partition of DBC.AccessRights by way of the primary
          index "Field_1 = '00001904'XB, Field_2 = '00001904'XB,
          Field_3 = '0000470D0000'XB" with a residual condition of (
          "DBC.AccessRights.Field_3 = '0000470D0000'XB") (no lock
          required).
       7) We do a single-AMP DELETE step in TD_DATADICTIONARYMAP from
          DBC.Dependency by way of the primary index "Field_1 =
          '0000470D0000'XB" with no residual conditions.
       8) We do a single-AMP DELETE step in TD_DATADICTIONARYMAP from
          DBC.ObjectUsage by way of the primary index "Field_1 =
          '00001904'XB, Field_2 = '0000470D0000'XB" with no residual
          conditions.
       9) We do an INSERT step into DBC.RCEvent.
10) We spoil the statistics cache for the table, view or query.
11) We end logging on DB1.t4.
12) We spoil the parser's dictionary cache for the table.
13) 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.

Unlike the CREATE TABLE request, multiple users may be trying to access or DROP table t4, so access must be coordinated using a proxy lock (that is, locking a reserved rowhash in step 1, followed by the lock of table t4 in step 2).

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 is a proxy lock for AccessRights on a single partition.

Step 5 places rowhash-level locks on the actual DBase,TVFields, and so on dictionary tables. This is an all-AMPs step, but for a 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 5. The EXPLAIN text does not indicate which rowhash-level lock in step 5 corresponds to the lock in the step 3. Furthermore, the rowhash-level locks in step 3 are not placed in exactly the same order as in step 5 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 5 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 rowhash-level locks must be placed in step 5 because the rowhash-level locks 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.