Lock Contentions | Teradata Vantage - 17.00 - 17.05 - About Lock Contentions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval

SQL Engine automatically locks database objects accessed by each transaction, to prevent or limit simultaneous access by other transactions. SQL Engine requests a lock on the data before a transaction begins and releases the lock when the transaction is complete.

Lock Level Description
Read Prevents write and exclusive locks. Generally caused by a SELECT.

Ensures consistency during read operations. Several users may hold concurrent read locks on the same data, during which no modification of the data is permitted.

Write Prevents other read, write, and exclusive locks. Generally caused by an INSERT, UPDATE, or DELETE statement.

Enables users to modify data while locking out all other users except those using access locks. Until a write lock releases, no new read or write locks are allowed.

Exclusive Prevents any other type of concurrent access. Generally caused by statements that make structural changes, for example, using an ALTER TABLE statement to add, drop, or change a column, when the statement uses the LOCKING modifier.

Exclusive locks are the most restrictive type of lock. When you hold an exclusive lock, other users cannot read or write to the data.

Access Prevents exclusive locks only. Caused by the LOCKING…FOR ACCESS or LOCKING…FOR LOAD COMMITTED locking modifier.

When you use the LOCKING…FOR ACCESS locking modifier you can read data while changes are in process. However, if you use this modifier, concurrent requests may access data that has not been committed, causing inconsistent results. Use this modifier for decision support on large tables that are updated only by small, single-row changes.

When you use the LOCKING…FOR LOAD COMMITTED locking modifier on load-isolated tables, you can read committed rows while concurrent changes are in process. Unlike LOCKING…FOR ACCESS, this modifier returns only committed data. For more information on Load Isolation, see About Reading Committed Data While Loading to the Same Table.