Lock Contentions | Teradata Vantage - Lock Contentions - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantage™

Analytics Database automatically locks database objects accessed by each transaction, to prevent or limit simultaneous access by other transactions. Analytics Database 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 Reading Committed Data While Loading to the Same Table.