15.10 - Default Lock Assignments and Lock Upgradeability - 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 assigns locking levels and severities to SQL requests by default.

When necessary, Teradata Database upgrades locks while processing system- or user-generated transactions. The most frequent upgrade is from a READ lock to a WRITE lock. This occurs whenever you select a row and then make an update request for the same row before Teradata Database commits the transaction.

Note: For load-isolated tables, a WRITE lock is used for concurrent load-isolated modifications and an EXCLUSIVE lock is used for nonconcurrent load-isolated modifications. For details about concurrent and nonconcurrent load-isolated modifications, see “Load Isolation” on page 676.

The following table lists some of the default lock assignments for various SQL requests and their access strategies:

 

          SQL Statement

 

                              Access Type

                    Default Lock Type

                           Assigned

 

        UPI or USI

         NUSI or FTS

CREATE DATABASE
DROP DATABASE
MODIFY DATABASE

Not applicable

Database

EXCLUSIVE

CREATE TABLE
DROP TABLE
ALTER TABLE

Not applicable

Table

EXCLUSIVE

DELETE

row hash

Table

WRITE

INSERT

row hash

Not applicable

WRITE

MERGE

row hash

  • Not applicable for INSERT
  • Table for UPDATE
  • WRITE

    SELECT

    row hash

    Table

    READ

    If a SELECT operation is part of a DDL operation, the system might downgrade a rowhash READ lock to an ACCESS lock to avoid blocking. See “DDL and DCL Requests, Dictionary Access, and Locks” on page 734 for further information.

    SELECT AND CONSUME

    Row hash

    Row hash

    WRITE

    Teradata Database does not grant the lock if the request is delayed because there are no rows in the queue table. As soon as a row is inserted into the table, the system grants the lock, and transaction processing resumes.

    UPDATE

    row hash

    Table

    WRITE

    Load-isolated tables lock defaults differ somewhat from standard defaults. Nonconcurrent load-isolated modifications that use INSERT/DELETE/UPDATE/MERGE statements block the reader sessions from selecting committed rows. Nonconcurrent load-isolated modifications performed using SQL use EXCLUSIVE locks instead of WRITE locks.

    For more information, see “Load Isolation” on page 676.

    The following table lists the default lock assignments for various Archive/Recovery utility commands. A lock severity not preceded by the keyword HUT is a database transaction lock.

     

    FOR this Archive/Recovery command …

    The system places this lock by default …

    At this level …

    Which can be downgraded to this lock …

  • ARCHIVE
  • DUMP
  • DUMP is a deprecated synonym for ARCHIVE.

    HUT READ

  • Table for a table‑level archive
  • Database for a database‑level archive
  • HUT GROUP READ

    A HUT GROUP READ lock is a downgrade from a HUT READ lock because it sets a HUT ACCESS lock at the table level, then applies a rolling HUT READ lock on a sequence of row sets within the ACCESS-locked table.

    BUILD

    HUT EXCLUSIVE

  • Table for a table‑level build
  • Database for a database‑level build
  • none

    COPY

    Table

    RESTORE

  • Table for a table‑level restore
  • Table for a partition‑level restore
  • Database for a database‑level restore
  • REVALIDATE REFERENCES

    Table

    ROLLBACK

    Table

    ROLLFORWARD

    Table

    LOGGING ONLINE ARCHIVE OFF

  • none on the logged object set
  • WRITE on DBC.RCEvent and DBC.ArchiveLoggingObjsTbl
  • Rowhash

    LOGGING ONLINE ARCHIVE ON

    READ on the logged object set

    Depends either on the locked object set and the aggregate number of tables per request or the number of tables contained per user or database.

    The level is always one of the following.

  • Table
  • Database
  • none

    HUT ACCESS

    Database

     

    WRITE on DBC.RCEvent and DBC.ArchiveLoggingObjsTbl

    Rowhash

     

    DELETE DATABASE

    EXCLUSIVE

    Database

    none

    WRITE on DBC tables as necessary for dropping tables in the database being deleted.

    Table

    Note: No HUT lock can be upgraded, but several can be downgraded in selected situations.

    Depending on the assigned lock and the individual SQL request, you can change some default lock assignments using the LOCKING request modifier (see “LOCKING Request Modifier” in SQL Data Manipulation Language for syntax and usage details). You can upgrade any lower severity lock to a higher severity lock, but the only downgrade permitted is from a READ lock to an ACCESS lock.

    The following table provides a summary of the allowable changes for database locks:

     

    A change from this default assignment lock …

    TO this user‑specified lock …

    IS …

    ACCESS

    Even though a CHECKSUM lock is essentially identical to an ACCESS lock, you can only specify CHECKSUM locks explicitly using the LOCKING request modifier; you cannot upgrade them to a higher severity lock. This is because Teradata Database never specifies CHECKSUM as a default lock severity.

    ACCESS

    redundant, but valid.

    READ

    READ

    WRITE

    WRITE

    EXCLUSIVE

    EXCLUSIVE

    ACCESS

    READ

    a valid upgrade.

    WRITE

    EXCLUSIVE

    READ

    WRITE

    EXCLUSIVE

    WRITE

    EXCLUSIVE

    READ

    ACCESS

    a valid downgrade.

    The following table combines the information from the two previous tables to indicate associations between individual SQL DML statements and lock upgrades and downgrades at the rowhash, view, and table database object levels.

     

    This LOCKING request modifier severity specification …

    Is available for this SQL DML statement …

    EXCLUSIVE

  • DELETE
  • INSERT
  • MERGE
  • SELECT
  • UPDATE
  • WRITE

  • SELECT
  • SELECT AND CONSUME
  • READ

    SELECT

    ACCESS

    SELECT

    The reason you can only specify the LOCKING FOR EXCLUSIVE modifier for DELETE, INSERT, MERGE, and UPDATE requests is that the default lock severity for these statements is WRITE. You cannot downgrade a WRITE lock because doing so would compromise the integrity of the database. Because the SELECT statement does not update data, and therefore its actions cannot compromise database integrity, you are permitted to change its default locking severity to any other severity. This option does not extend to its SELECT AND CONSUME variant, for which the severity can only be upgraded to WRITE or EXCLUSIVE.

    You cannot upgrade any of the HUT locks, but you can downgrade several of them (see “About Default Lock Assignments” on page 704 for details).

    Upgrading system locks helps to minimize deadlocks, but lessens concurrency, which can downgrade system performance if not done selectively. The Lock Manager uses the following rules when it upgrades locks.

  • If two transactions concurrently hold READ locks for the same data and the first transaction enters an update request, then its READ lock cannot be upgraded to a WRITE lock until the READ lock for the second transaction is released.
  • If other transactions are awaiting locks for the same data when the first transaction enters its update request, its READ lock is upgraded before the waiting transactions are given locks. Thus, upgrading an existing lock has higher priority than does granting a new lock.
  • You can determine the current status of operations such as request blocking and transaction aborts for a particular session using the Query Session utility (see Utilities for details on how to use Query Session).

    The following set of guidelines lists some rules of thumb for maximizing concurrency with your database transactions.

  • Use ACCESS locks in place of READ locks whenever an application can tolerate dirty reads.
  • If you have load-isolated tables, however, you can use the LOAD COMMITTED locking modifier to read committed rows without being blocked and without blocking the concurrent isolated modifications. Using load-isolated tables allows you to obtain committed reads instead of dirty reads. For more information about load-isolated tables, see “Load Isolation” on page 676.

    For the case where an ACCESS locked Read operation on a geospatial index does not permit all non‑updated rows to be returned to the requestor, Teradata Database returns a retryable error to that requestor.

  • A SELECT request that requires a READ lock on a table cannot run concurrently with an executing CREATE INDEX or ALTER TABLE … FALLBACK request for the same table.
  • Instead, specify a READ lock for the CREATE INDEX or ALTER TABLE request to permit concurrency (see “LOCKING Request Modifier” in SQL Data Manipulation Language for information about how to do this).

  • If the CREATE INDEX or ALTER TABLE … FALLBACK LOCKING request modifier specifies WRITE (or if there is no LOCKING request modifier specified), then specify an ACCESS lock in a LOCKING request modifier on your SELECT request to permit concurrency (see “LOCKING” in SQL Data Manipulation Language for information about how to do this).
  • Note that the ALTER TABLE operation can be to add FALLBACK only; if other table attributes are added, then ALTER TABLE cannot run concurrently with SELECT.

  • Because requests for WRITE locks can result in transactions being blocked, and can also result in deadlocks, you should consider running only read-only transactions (or access-only if a LOCKING FOR WRITE clause is specified) concurrently with ALTER TABLE … FALLBACK or CREATE INDEX statements. See “LOCKING Request Modifier” in SQL Data Manipulation Language for information about how to do this.
  • Be aware that when running long transactions concurrently with CREATE INDEX or ALTER TABLE … FALLBACK, the CREATE INDEX or ALTER TABLE request might not complete until the long running transactions have completed.
  • To avoid the chance of deadlocks with other DML transactions or DDL requests, consider writing your DML transactions to immediately obtain the highest severity lock they will require rather than attempt to upgrade a less severe lock at a later time during transaction processing.
  • You should always place SELECT AND CONSUME requests as early as possible in a transaction to avoid conflicts with other database resources. This is to minimize the likelihood of a situation where a SELECT AND CONSUME TOP 1 request would enter a delayed state while holding locks on resources that might be needed by other requests.