15.10 - Teradata Database Locking Levels and Severities - 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

Teradata Database locks have two dimensions: level and severity. The level of a lock refers to its scope or granularity: the type and, by inference, the size of the object locked. For example, a database lock is a higher, less finely grained level lock than a rowkey-level lock.

The selection of lock granularity is always a tradeoff between the conflicting demands of concurrency and overhead. Concurrency increases as the choice of locking level becomes increasingly granular. Exerting a rowhash‑ or rowkey-level lock permits more users to access a given table than exerting a table‑level lock on the same table. This is why Teradata Database provides multiple levels of locking granularity. See “About Locking Levels” on page 687.

The severity of a lock refers to its degree of restrictiveness or exclusivity, such as WRITE lock being more restrictive than an ACCESS lock, or an EXCLUSIVE lock being more restrictive than a READ lock. See “About Locking Severity” on page 689 and “Default Lock Assignments and Lock Upgradeability” on page 704 for more information about locking severities.

The hierarchy of locking levels for a database management system is a function of the available granularities of locking, with database‑level locks having the coarsest granularity and rowkey‑level locks having the finest granularity. Depending on the request being processed, the system places a certain default lock level on the object of the request, which can be one of the following database objects:

  • Database
  • Table
  • See “Proxy Locks” on page 713 for a description of a special category of table‑level locking.

  • View
  • Partition
  • RowHash
  • RowKey (Partition and RowHash)
  •  

    This lock level …

    Locks …

    Database

    all rows of all tables in the specified database and their associated secondary index subtables.

    Table

    all rows in the specified base table and in any secondary index and fallback subtables associated with it.

    View

    all underlying tables accessed by the specified view.

    Partition

    the primary and fallback copy of rows in a partition for the specified table or single-table view. The table must be row partitioned.

    This lock permits other users to access the data in the table that are not in the same partition.

    PartitionRange

    the primary and fallback copy of rows in a range of partitions for the specified table or single-table view. The table must be row partitioned.

    This lock permits other users to access the data in the table that are outside the specified partition range.

    RowHash

    the specified primary or fallback copy of rows sharing the same row hash value for the specified table or single-table view. For a row-partitioned table, this lock level applies to the row hash value for all partitions.

    The rowhash-level lock permits other users to access the data in the table that do not have the same rowhash.

    The rowhash-level lock applies to a set of rows that shares the same hash code. It does not necessarily lock only a single row, since multiple rows may have the same rowhash.

  • A rowhash-level lock is applied whenever a non-row-partitioned table is accessed by using a unique primary index (UPI) or a nonunique primary index (NUPI).
  • For an update or delete that accesses a data row by using a unique secondary index (USI), the appropriate rowhash of the USI subtable is locked, as well as the indexed data rowhash or rowkey.
  • Rowhash locks on a table's nonunique secondary index (NUSI) subtables are usually not needed. First, a query or DML that uses a NUSI access path locks the whole table. Second, DML that does not lock the whole table uses task locks rather than rowhash locks on any NUSI subtables that require index maintenance.
  • RowHash in a PartitionRange

    the specified primary or fallback copy of rows sharing the same row hash value for the specified table or single-table view in a range of partitions. The table must be row partitioned.

    This lock permits other users to access other data in the table that do not have the same rowhash or are outside the specified partition range.

    The rowhash-level lock applies to a set of rows that shares the same hash code. It does not necessarily lock only one row since multiple rows may have the same rowhash in the same partition or in more than one partition in the partition range.

    This lock level is not used on rows in a USI or NUSI index subtable, as these subtables are never partitioned.

    RowKey

    the specified primary or fallback copy of rows sharing the same rowkey (partition and row hash value) for the specified table or single-table view. The table must be row partitioned.

    A rowkey-level lock permits other users to access other data in the table that do not have the same rowhash or partition value.

    A rowkey-level lock applies to a set of rows that shares the same partition and rowhash. It does not necessarily lock only one row since there could be multiple rows with the same rowhash in a partition.

  • A rowkey-level lock is applied whenever a row-partitioned table with a primary index (UPI or NUPI) is accessed by specifying the primary index and partitioning column values.
  • The rowkey-level lock is not used on rows in a USI or NUSI subtable, as these subtables are never partitioned.
  • The locking level determines whether other users can access the target object.

    Locking severities and locking levels combine to exert various locking granularities. The less granular the combination, the greater the impact on concurrency and system performance, and the greater the delay in processing time.

    The available lock severities, from most restrictive to least restrictive, are described in the following table:

     

           Lock Severity

                                                         Description

                                                                             Most Restrictive

    EXCLUSIVE

    EXCLUSIVE locks are placed only on a database or table when the object is undergoing structural changes (for example, a column is being created or dropped).

    You can also place an EXCLUSIVE lock explicitly using the LOCKING request modifier (see “LOCKING Request Modifier” in SQL Data Manipulation Language).

    HUT EXCLUSIVE

    HUT EXCLUSIVE locks are placed only on a database or table when a database is being restored, copied, rolled back, rolled forward, or built by an Archive/Recovery utility command.

    An EXCLUSIVE lock restricts access to the object by any other user. No other process can read from, write to, or access the locked resource in any way. See Teradata Archive/Recovery Utility Reference.

    WRITE

    Placed in response to an INSERT, UPDATE, or DELETE request.

    A WRITE lock restricts access by other users (except for applications that are not concerned with data consistency and choose to override the automatically applied WRITE lock by specifying a less restrictive ACCESS lock).

    You can also place this lock explicitly using the LOCKING request modifier (see “LOCKING Request Modifier” in SQL Data Manipulation Language).

    HUT WRITE

    Placed when a set of database objects is being recovered or restored by an Archive/Recovery utility command.

    READ

    A READ lock is placed in response to a SELECT request and restricts access by users who require EXCLUSIVE or WRITE locks.

    Several users can hold READ locks on a resource, during which the system permits no modification of that resource. READ locks ensure consistency during READ operations such as those that occur during a SELECT statement.

    You can also place the READ lock explicitly using the LOCKING request modifier (see “LOCKING Request Modifier” in SQL Data Manipulation Language).

    HUT READ

    HUT GROUP READ

    Archive requests can also place HUT READ and HUT GROUP READ locks on database resources. You must release any HUT READ or HUT GROUP READ locks you set either by submitting the RELEASE LOCK Archive utility command after the command that set those locks completes, or by specifying the RELEASE LOCK option in the command text itself. See Teradata Archive/Recovery Utility Reference for syntax and usage details.

    The CHECKSUM, ACCESS, and HUT ACCESS locking severities are all at the same level in the restrictiveness hierarchy.

    CHECKSUM

    Placed in response to a user-defined LOCKING FOR CHECKSUM modifier (see “LOCKING Request Modifier” in SQL Data Manipulation Language) when using cursors in embedded SQL.

    CHECKSUM locking is identical to ACCESS locking except that it adds checksums to the rows of a spool to allow a test of whether a row in the cursor has been modified by another user or session at the time an update is being made through the cursor.

    See also “Cursor Locking Modes” on page 742, SQL Data Manipulation Language, and SQL Stored Procedures and Embedded SQL.

    ACCESS

    Placed in response to a user-defined LOCKING FOR ACCESS modifier (see “LOCKING Request Modifier” in SQL Data Manipulation Language), or by setting the session default isolation level to READ UNCOMMITTED using the SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL statement (see SQL Data Definition Language).

    Permits a user to have a form of read access to an object that might already be locked for READ or WRITE. An ACCESS lock does not restrict access by another user except when an EXCLUSIVE lock is required; therefore it is sometimes referred to as a dirty READ lock.

    An ACCESS lock Read operation on a geospatial NUSI. If Teradata Database discovers while attempting to perform an ACCESS locked Read operation that the current state of the Hilbert R‑Tree for the NUSI does not permit all non‑updated rows to be returned to the requestor, it returns a retryable error to that requestor.

    See SQL Geospatial Types.

    The global application of ACCESS locking for read operations when SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL is set to READ UNCOMMITED depends on the setting of the DBS Control field AccessLockForUncomRead.

    When the parameter is set FALSE, SELECT operations within INSERT, DELETE, MERGE, and UPDATE requests set READ locks, while when the parameter is set TRUE, the same SELECT operations set ACCESS locks.

    A user requesting an ACCESS lock disregards all data consistency issues. Because ACCESS and WRITE locks are compatible, the data might be undergoing updates while the user who requested the access is reading it. Therefore, any query that requests an ACCESS lock might return incorrect or inconsistent results.

    An ACCESS lock is also placed in response to a user-defined LOAD COMMITTED locking modifier.

    See “LOCKING Request Modifier” in SQL Data Manipulation Language.

    For information about designating tables for ISOLATED LOADING, see SQL Data Definition Language Syntax and Examples.

    For a load-isolated table, this modifier allows users to read from committed rows in tables, even while the table is being loaded with data. If the table that is being read is not a load-isolated table, this severity results in an ACCESS lock.

    For information about load isolation, see “Load Isolation” on page 676.

    HUT ACCESS

    The GROUP READ lock set by the Archive utility also places a HUT ACCESS lock internally on its subject table for the duration of the GROUP READ lock.

    See Teradata Archive/Recovery Utility Reference for details.

                                                                             Least Restrictive

    The Teradata Lock Manager controls the interaction of following types of lock, when placed at specific levels:

  • ACCESS (see “ACCESS” on page 691).
  • CHECKSUM (see “CHECKSUM” on page 690).
  • READ (see “READ” on page 690).
  • WRITE (see “WRITE” on page 690).
  • EXCLUSIVE (see “EXCLUSIVE” on page 689).
  • Various commands of the Archive/Recovery utility place the following types of lock:

  • HUT ACCESS
  • HUT READ
  • HUT GROUP READ
  • HUT EXCLUSIVE
  • See Teradata Archive/Recovery Utility Reference for descriptions of the various HUT locks.

    The following notation is used to describe the locking severity compatibilities.

     

                                  Notation

                                          Definition

    Q

    A locking queue associated with a database object.

    L

    A list of locks currently held.

    lock(<object>,<lock requested>)

    A database object‑locking severity requested pair.

    Each database object has an associated locking queue Q and list of currently held locks L. All requests perform a locking operation before they access any database objects.

     

    IF lock(<object>,<lock requested>) is …

    THEN …

    queued for any lock in L

    the transaction is placed in Q and waits there as long as lock(<object>,<lock requested>) is queued.

    A request in this state is said to be blocked (see “Blocked Requests” on page 710).

    granted

    lock(<object>,<lock requested>) is added to L with <lock requested> and the transaction resumes processing.

    After the transaction finishes with an object by either committing or rolling back, its lock is removed from L.

    The table on the following page summarizes the action taken when a requested locking severity competes with an existing locking severity. Note that the system enforces the identical compatibilities with other locks for HUT ACCESS, HUT READ, HUT GROUP READ, and HUT EXCLUSIVE locks as it does for the comparably named database locks.

     

                                   Severity of

                         Requested Lock

                                                              Severity of Held Lock

    None

    ACCESS

    HUT ACCESS

    CHECKSUM

    READ

    HUT READ

    HUT GROUP READ

    WRITE

    EXCLUSIVE

    HUT EXCLUSIVE

    ACCESS

     

    HUT ACCESS

     

    CHECKSUM

    Lock Granted

    Lock Granted

    Lock Granted

    Lock Granted

    Request Queued

    If you specify a LOCKING FOR … NOWAIT request modifier, the transaction aborts if it is blocked instead of queueing.

    READ

     

    HUT READ

     

    HUT GROUP READ

     

    HUT GROUP READ locks are implemented internally as RANGE READ locks. This lock places a HUT ACCESS lock on the subject table for the duration of the Read operation and a series of rolling HUT READ locks on the component row sets of the table as rows are read in sequence from a set of data blocks ranging between system‑determined starting and ending rowhash values. See “About HUT Lock Severities” on page 698.

    Lock Granted

    Lock Granted

    Lock Granted

    Request Queued

    If you specify a LOCKING FOR … NOWAIT request modifier, the transaction aborts if it is blocked instead of queueing.

    Request Queued

    If you specify a LOCKING FOR … NOWAIT request modifier, the transaction aborts if it is blocked instead of queueing.

    WRITE

    Lock Granted

    Lock Granted

    Request Queued

    If you specify a LOCKING FOR … NOWAIT request modifier, the transaction aborts if it is blocked instead of queueing.

    Request Queued

    If you specify a LOCKING FOR … NOWAIT request modifier, the transaction aborts if it is blocked instead of queueing.

    Request Queued

    If you specify a LOCKING FOR … NOWAIT request modifier, the transaction aborts if it is blocked instead of queueing.

    EXCLUSIVE

     

    HUT EXCLUSIVE

    Lock Granted

    Request Queued

    If you specify a LOCKING FOR … NOWAIT request modifier, the transaction aborts if it is blocked instead of queueing.

    Request Queued

    If you specify a LOCKING FOR … NOWAIT request modifier, the transaction aborts if it is blocked instead of queueing.

    Request Queued

    If you specify a LOCKING FOR … NOWAIT request modifier, the transaction aborts if it is blocked instead of queueing.

    Request Queued

    If you specify a LOCKING FOR … NOWAIT request modifier, the transaction aborts if it is blocked instead of queueing.

    Because other client utilities such as BTEQ, FastExport, FastLoad, MultiLoad, Teradata Parallel Data Pump, and Teradata Parallel Transporter use standard database locks, the interactions of those locking severities with those of other database locks are identical. See the following manuals for details of the locks set by those utilities.

  • Basic Teradata Query Reference
  • Teradata FastExport Reference
  • Teradata FastLoad Reference
  • Teradata MultiLoad Reference
  • Teradata Parallel Data Pump Reference
  • Teradata Parallel Transporter Reference
  • A queued request is in an I/O wait state and is said to be blocked (see “Blocked Requests” on page 710).

    A WRITE or EXCLUSIVE lock on a database, table, or view restricts all requests or transactions except the one holding the lock from accessing data within the domain of that object.

    Because a lock on an entire database can restrict access to a large quantity of data, the Parser ensures that default database locks are applied at the lowest possible level and severity required to secure the integrity of the database while simultaneously maximizing concurrency.

    Table‑level WRITE locks on dictionary tables prevent contending tasks from accessing the dictionary, so the Parser attempts to lock dictionary tables at the rowhash or rowkey (partition and rowhash) level whenever possible.

    For information about how load isolation affects compatibility among locking severities, see “Load Isolation” on page 676.

    When you specify the NOWAIT option for the SQL LOCKING request modifier, Teradata Database aborts a transaction that makes a lock request that cannot be fulfilled immediately. For details on how to use the NOWAIT option with the LOCKING request modifier, see “LOCKING Request Modifier” in SQL Data Manipulation Language.

    Teradata Database uses a slight variation of this code internally to avoid blocking on DDL operations. Instead of aborting the request, the system instead downgrades rowhash lock severities from READ to ACCESS. See “DDL and DCL Requests, Dictionary Access, and Locks” on page 734.

    There are two utilities related to logging.

  • Lock Viewer Viewpoint portlet
  • This portlet produces a report of miscellaneous database lock delay information that you can use to detect blocked transactions and global deadlocks. Teradata Database extracts the data reported by the Lock Viewer portlet from various DBQL transaction logs.

  • Show Locks
  • This utility produces a report on the various HUT locks (see Teradata Archive/Recovery Utility Reference and “Client Utility Locks and Teradata Database” on page 697) currently in place on various Teradata Database resources.