16.10 - Client Utility Locks and Teradata Database - 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

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) place locks at different levels and severities on Teradata Database resources. See About Locking Levels and About Locking Severity for details of locking levels and severities. For more information about HUT locks placed by the Archive/Recovery utility, see Teradata Archive/Recovery Utility Reference.

Locks Placed on Teradata Database Resources by Teradata Tools and Utilities

The client utilities place the following 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).

    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.

    For more information about HUT locks, see Teradata Archive/Recovery Utility Reference.

About HUT Lock Interactions With Database Transaction Locks

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.

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.