Database Locking Levels and Severities | Transaction Processing | Vantage - 17.10 - Database Locking Levels and Severities - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

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 trade-off 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 Vantage provides multiple levels of locking granularity.

The severity of a lock refers to its degree of restrictiveness or exclusivity, such as a WRITE lock being more restrictive than an ACCESS lock, or an EXCLUSIVE lock being more restrictive than a READ lock. See also Default Lock Assignments and Lock Upgradeability.

About Locking Levels

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 for a description of a special category of table-level locking.

  • View
  • Partition
  • RowHash
  • RowKey (Partition and RowHash)
Lock Level … What is Locked
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.

About Locking Severity

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 Teradata Vantage™ - SQL Data Manipulation Language, B035-1146).

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 Teradata Vantage™ - SQL Data Manipulation Language, B035-1146).

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 Teradata Vantage™ - SQL Data Manipulation Language, B035-1146).

The CHECKSUM and 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 Teradata Vantage™ - SQL Data Manipulation Language, B035-1146) 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, Teradata Vantage™ - SQL Data Manipulation Language, B035-1146, and Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148.

ACCESS Placed in response to a user-defined LOCKING FOR ACCESS modifier (see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146), or by setting the session default isolation level to READ UNCOMMITTED using the SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL statement (see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144).

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.

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 Teradata Vantage™ - SQL Data Manipulation Language, B035-1146).

For information about designating tables for ISOLATED LOADING, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

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.

                                                                         Least Restrictive

Compatibility Among Locking Severities

The Teradata Lock Manager controls the interaction of following types of lock, when placed at specific levels:
  • ACCESS.
  • CHECKSUM.
  • READ.
  • WRITE.
  • EXCLUSIVE.

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

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.

Severity of Requested Lock                                                           Severity of Held Lock
None ACCESS CHECKSUM READ WRITE EXCLUSIVE
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 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 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, B035-2414
  • Teradata® FastExport Reference, B035-2410
  • Teradata® FastLoad Reference, B035-2411
  • Teradata® MultiLoad Reference, B035-2409
  • Teradata® Parallel Data Pump Reference, B035-3021
  • Teradata® Parallel Transporter Reference, B035-2436

A queued request is in an I/O wait state and is said to be blocked (see Blocked Requests).

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.

Using the NOWAIT Option for the SQL LOCKING Request Modifier

When you specify the NOWAIT option for the SQL LOCKING request modifier, Vantage 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 Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

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

AMP-Based Utilities and Logging

Following are the 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. Vantage extracts the data reported by the Lock Viewer portlet from various DBQL transaction logs.