Default Lock Assignments and Lock Upgradeability | Teradata Vantage - Default Lock Assignments and Lock Upgradeability - Analytics Database

Vantage assigns locking levels and severities to SQL requests by default.

When necessary, Vantage 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 Vantage commits the transaction.

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.

Default Lock Assignments

The following table lists default lock assignments for SQL requests and their access strategies.

SQL Statement	Access Type		Default Lock Type Assigned
SQL Statement	UPI or USI	NUSI or FTS	Default Lock Type Assigned
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 may downgrade a rowhash READ lock to an ACCESS lock to avoid blocking. Fore more information, see DDL and DCL Requests, Dictionary Access, and Locks.
SELECT AND CONSUME	Row hash	Row hash	WRITE Vantage 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 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.

Changing Lock Assignments Using the LOCKING Request Modifier

Depending on the assigned lock and the individual SQL request, you can change default lock assignments using the LOCKING request modifier (for details, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146). 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.

Default Assignment Lock	User-Specified Lock	Change
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 a CHECKSUM lock to a higher severity lock. This is because Vantage never specifies CHECKSUM as a default lock severity.	ACCESS	Redundant but valid.
READ	READ
WRITE	WRITE
EXCLUSIVE	EXCLUSIVE
ACCESS	READ	Valid upgrade.
	WRITE
	EXCLUSIVE
READ	WRITE
READ	EXCLUSIVE
WRITE	EXCLUSIVE
READ	ACCESS	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:

Locking Request Modifier Severity Specification	Applicable SQL DML Statements
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 compromises 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.

Rules for Upgrading Locks

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 upgrading 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 Teradata Vantage™ - Database Utilities, B035-1102 for details on how to use Query Session).

Guidelines for Changing Default Lock Assignments and Changing Intratransaction Request Orders to Maximize Concurrency

The following are guidelines 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.
A SELECT request that requires a READ lock on a table cannot run concurrently with a running 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 (for details, see the information about the LOCKING request modifier in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146).
If the CREATE INDEX or ALTER TABLE ... FALLBACK LOCKING request modifier specifies WRITE (or if there is no LOCKING request modifier specified), specify an ACCESS lock in a LOCKING request modifier on your SELECT request to permit concurrency (see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146).
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, 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 the information about the LOCKING request modifier in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
When running long transactions concurrently with CREATE INDEX or ALTER TABLE ... FALLBACK, the CREATE INDEX or ALTER TABLE request may not complete until the long running transactions have completed.
To avoid deadlocks with other DML transactions or DDL requests, consider writing your DML transactions to immediately obtain the highest severity lock needed rather than trying to upgrade a less severe lock later during transaction processing.
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 enters a delayed state while holding locks on resources that may be needed by other requests.

Default Lock Assignments and Lock Upgradeability | Teradata Vantage - Default Lock Assignments and Lock Upgradeability - Analytics Database - Teradata Vantage

SQL Request and Transaction Processing