15.10 - Client Utility Locks and Teradata Database - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

The Teradata Tools and Utilities programs that perform tasks such as bulk data loads (FastLoad, MultiLoad, BTEQ), bulk data exports (FastExport, BTEQ), stream data loads (Teradata Parallel Data Pump), both data loads and data exports (Teradata Parallel Transporter), and archiving, recovering, or restoring tables and databases (Archive/Recovery) also place locks at different levels and severities on Teradata Database resources (see “About Locking Levels” on page 687 and “About Locking Severity” on page 689 for details of locking levels and severities).

The client utilities place 2 basic types of locks on Teradata Database resources.

  • Standard database locks
  • All of the client data loading and exporting utilities except Archive/Recovery use standard database transaction locks (see “Database Locks, Two-Phase Locking, and Serializability” on page 669).

    For details about when the various database locks that client utilities place and release on resources in Teradata Database, see the following Teradata Tools and Utilities manuals.

  • Basic Teradata Query Reference
  • Teradata FastExport Reference
  • Teradata Parallel Transporter uses the FastExport protocol for its EXPORT operator (see Teradata Parallel Transporter Reference for details).

  • Teradata FastLoad Reference
  • Teradata Parallel Transporter uses the FastLoad protocol for its LOAD operator (see Teradata Parallel Transporter Reference for details).

  • Teradata MultiLoad Reference
  • Teradata Parallel Transporter uses the MultiLoad protocol for its UPDATE operator (see Teradata Parallel Transporter Reference for details).

  • Teradata Parallel Data Pump Reference
  • Teradata Parallel Transporter uses the Teradata Parallel Transporter protocol for its STREAM operator (see Teradata Parallel Transporter Reference for details).

  • Teradata Parallel Transporter Reference
  • Host Utility (HUT) locks
  • HUT locks are the locks set by the Archive/Recovery utility and the various commands related to it. Unlike database locks, you must remove HUT locks explicitly by specifying the RELEASE LOCK command in your ARCHIVE, RESTORE, ROLLBACK, or ROLLFORWARD job or by specifying the RELEASE LOCK option with the Archive/Recovery‑related command you submit because the system does not release HUT locks automatically, even retaining them after a command aborts, a job terminates, or a system restart.

    Another difference between HUT locks and database locks is that for Archive operations, HUT locks are placed only on the tables or databases (or both) on the set of AMPs being archived, while database transaction table‑ and database‑level locks are placed on those objects across all AMPs in the system.

    Finally, HUT locks are associated with the user who submits the Archive/Recovery job, not with the job or with a transaction, while database locks are always associated exclusively with a transaction.

    See Teradata Archive/Recovery Utility Reference for details.

    The Archive/Recovery utility places HUT locks at the following levels only.

  • Table
  • Database
  • The Teradata Database Lock Manager does not place HUT locks at the rowhash level for any of the operations performed by the Archive/Recovery utility.

    Whether the locks are set at the table or database level, or both, depends on what you specify is to be archived, checkpointed, restored, or recovered (see Teradata Archive/Recovery Utility Reference for details).

    The system does not place HUT locks at the rowhash level or the CHECKSUM severity.

    The Archive/Recovery utility places HUT locks with the following locking severities only.

  • READ or GROUP READ for ARCHIVE operations.
  • Note that a GROUP READ lock sets a HUT ACCESS lock on its subject table internally for the duration of the Archive operation on that table as well as placing a series of rolling HUT READ locks on successive blocks of rows demarcated by a system‑determined range of rowhash values (or rowkey values, for row-partitioned tables). GROUP READ locks are implemented internally as ROW RANGE locks (see “About HUT GROUP READ Locks” on page 701 for details).

    GROUP READ locks are implemented internally as Row Range locks, and have the save level of severity as a standard READ lock (see “About Locking Severity” on page 689).

  • EXCLUSIVE for the following operations.
  • BUILD
  • Locks are placed at the following levels.

  • TABLE for a table‑level build.
  • DATABASE for a database‑level build.
  • COPY
  • Locks are placed at the TABLE level.

  • RESTORE
  • Locks are placed at the following levels for an all‑AMP restore, depending on both the ARCHIVE and RESTORE command specifications, as follows.

     

    IF the ARCHIVE is done at this level …

    AND the RESTORE is done at this level …

    THEN the system places an EXCLUSIVE HUT lock at this level …

    database

    database

    database.

    database

    table

    table.

    table

    database

    table on each of the tables being restored, one table at a time.

    table

    table

    table on each of the tables being restored, one table at a time.

  • REVALIDATE REFERENCES
  • Locks are placed at the table level.

  • ROLLBACK
  • Locks are placed at the table level on the journal.

  • ROLLFORWARD
  • Locks are placed at the table level on the journal.

  • WRITE for CHECKPOINT operations.
  • The following Archive/Recovery‑related SQL statements place database locks, not HUT locks.

     

              Statement

                         Database Locks Placed

                              Objects Locked

    DELETE DATABASE

     

    EXCLUSIVE at database level.

    Database being deleted.

    WRITE at table level.

    Data dictionary tables such as the following when necessary.

  • DBC.TVM
  • DBC.TVFields
  • DBC.Indexes
  • LOGGING ONLINE ARCHIVE OFF

     

    none.

    Object being logged.

    ROWHASH.

  • DBC.RCEvent
  • DBC.ArchiveLoggingObjsTbl
  • LOGGING ONLINE ARCHIVE ON

    READ at database or table level, depending on the object being logged and the number of tables contained by the database or user.

    Table‑level locks within a given database or user are escalated to a single database‑level lock whenever the number of tables locked exceeds 10,000.

    Table or database being logged.

    If a database or user contains more than 10,000 tables, the Lock Manager places only a single database‑level lock.

    HUT ACCESS, HUT READ, HUT GROUP READ, and HUT EXCLUSIVE locks interact with locks placed by Teradata Database in exactly the same manner as database locks of the same severity interact with other database locks.

    For example, the exclusion of other lock requests on the same database resource enforced by a HUT EXCLUSIVE lock placed during a Restore operation is identical to the exclusion of other lock requests enforced by an EXCLUSIVE locked placed by an operation within Teradata Database (see the table near the end of the topic “Compatibility Among Locking Severities” on page 692 for details).

    From a user perspective, there are only two important differences between HUT locks and database transaction locks: their duration and their scope.

     

    Lock Type

                                      Duration

                                            Scope

    Database transaction

    Held only for the time it takes for a transaction to either commit or roll back.

    Database transaction locks are released automatically as soon as the transaction that set them commits or rolls back.

    Session‑level, meaning that the lock applies to any request submitted by the session running the locking transaction.

    Another session running against the same set of objects must set its own locks, even if it is running under the same user.

    HUT

    A HUT lock is only released if you issue an explicit RELEASE LOCK command, or if the batch locking operation placing the lock completes successfully and also specifies the RELEASE LOCK command.

    HUT locks remain even after any of the following events occur.

  • The utility command that placed the HUT lock fails.
  • The ARCHIVE job in which the HUT lock was placed terminates.
  • Teradata Database experiences a restart.
  • Because there is only one lock, a RELEASE LOCK command against a set of HUT‑locked database objects releases all existing HUT locks, even if other jobs submitted by that user continue to access the previously HUT‑locked object.

    See Teradata Archive/Recovery Utility Reference for warnings about releasing HUT locks while other jobs are running. Such actions can lead to an inconsistent archive.

    User‑level, meaning that the lock applies to any ARCHIVE operation a user is performing on the object.

    A user can only place one HUT lock on a database object at a time. An exerted lock allows any ARCHIVE operation from that user to access the database object set on which it holds locks, but blocks other users from acquiring conflicting HUT locks on that object set.

    HUT locks block all conflicting transaction lock attempts on the object set, even if they are requested by the same user.

    The HUT GROUP READ lock is a hybrid type that combines an ACCESS lock on the entire subject table with a moving READ lock that locks successive sets of system‑determined ranges of rowhash values (or rowkey values, for row-partitioned tables).

    The following graphic illustrates how a GROUP READ lock works.

    In terms of their interaction with database locks, GROUP READ LOCKS combine the properties of table‑level ACCESS locks on the AMP currently being archived throughout the duration of their application, and rowhash‑level READ locks as the system successively locks, reads, archives, and releases the READ lock on each range of row hash values on that AMP.

    The process is as follows.

    1 Lock the subject table for ACCESS on the first AMP that contains rows to be archived.

    2 Place a rowhash‑level READ lock on the first block of rows belonging to the system‑determined range of rowhash values in the subject table on the current AMP.

    3 Read that block of rows for Archive.

    4 Write the block of rows to the archive medium.

    5 Release the READ lock on the first block of rows.

    6 Place a READ lock on the second block of rows belonging to the system‑determined range of rowhash values in the subject table on the current AMP.

    7 Make the following check.

     

    IF all the rows in the subject table on the current AMP …

    THEN …

    have been archived

    move to the next AMP in the archive sequence and lock the subject table for ACCESS.

    have not been archived

    place a READ lock on the next block of rows belonging to the system‑determined range of rowhash values in the subject table on the current AMP.

    8 Iterate stages 2 through 7 until all the rows in the subject table on all AMPs have been archived.

    9 Release the ACCESS lock on the subject table.