Pseudo Table Locks - Advanced SQL Engine - Teradata Database

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.

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 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 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 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.

Notice that 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 the step 2. Furthermore, the rowhash-level locks in step 2 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. For step 2, the table is the same for all the 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 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 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 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, 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 AccesssRights 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 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 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 actual rowhash-level locks must be placed in step 5 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.