16.10 - About Lock Contentions - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

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