DWHPro Insights

Teradata Concurrency Control: Locking and Transactions Explained

By Roland Wenzlofsky ·

Teradata Concurrency Control: Locking and Transactions Explained

What is Teradata Concurrency Control?

Concurrency control coordinates conflicting operations while allowing compatible work, including writes to disjoint resources. Locking and transactions determine the permitted overlap.

Teradata Transactions

Transactions define atomic work, but the rollback response to an individual error depends on session mode and error class. ANSI and BTET behavior must not be treated as identical.

Teradata Locks

Locks specify usage permissions for database entities such as tables, views, rows, and other objects.

Various lock levels and granularities are accessible depending on the object type and activity. These determine which rows of an object are locked.

The Teradata lock manager automatically locks objects such as databases, tables, views, and rows (determined by the row hash value).

There are four different types of locks: Exclusive, Write, Read, and Access.

The Teradata lock manager determines the appropriate lock level based on the operation and target object.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Transactions initiate locking of database objects, which are then unlocked upon completion of the transaction, thereby ensuring data consistency.

The locking granularity employed depends on the object being locked and the SQL statement executed. The available locking granularities are as follows:

Object Type LockedAutomatic Lock Handling
DatabaseLocks all tables located in the database
TableLocks all table rows and secondary indexes
ViewLocks all tables used in the view
RowLocks the rowhash resource, which can cover multiple rows

In addition to establishing the locking granularity, each lock must be based on one of the following lock levels. Each level has limitations when another session already holds locks on the same object:

Lock LevelWhat it means
Exclusive LockOnly the requestor has access to the object. No other session can access the object until the lock is released.
Write LockOnly one session can hold a write lock at a time. Other sessions requesting write or read locks must wait until it is released.
Read LockUnlimited sessions can hold read locks, during which no modification of that object is permitted.
Access LockIt will be granted even if another session holds a Write Lock like the read lock. This may result in inconsistent data being read.

Here are some examples of combinations the Teradata Lock Manager will use automatically:

SQL StatementThe granularity with Indexed AccessThe granularity with FTS or NUSI AccessLock Level Mode
SELECTRow hashWhole TableRead
UPDATERow hashWhole TableWrite
DELETERow hashWhole TableWrite
INSERTRow hash Write
CREATE DATABASE
DROP DATABASE
MODIFY DATABASE
 Whole DatabaseExclusive
CREATE TABLE
DROP TABLE
ALTER TABLE
 Whole TableExclusive

Beware: The Teradata lock manager has limited row hash locking resources. Attempting to lock numerous rows on a row hash may result in transaction termination. Additional lock requests for the same objects may be granted or delayed based on the currently held lock level.

Requested Lock LevelActive Lock Level
NoneAccessReadWriteExclusive
Access LockGrantedGrantedGrantedGrantedWait
Read LockGrantedGrantedGrantedWaitWait
Write LockGrantedGrantedWaitWaitWait
Exclusive LockGrantedWaitWaitWaitWait

Once the session’s transaction is completed, locks are released.

The lock tables are simplified examples, not an exhaustive modern lock matrix. Partition and range locking can narrow resources; a write lock conflicts at its actual scope, not with every write in the database. No multi-session lock test was performed for this article.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam