What is Teradata Concurrency Control?

Concurrency control prevents simultaneous updates, deletions, or insertions of rows by distinct sessions. Locking and transactions are the two mechanisms employed for concurrency control.

Teradata Transactions

Transactions represent logical units that encapsulate one or multiple statements. The success of all statements within a transaction is mandatory. Otherwise, the entire transaction and its statements will be rolled back.

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.

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 row (identified by the row hash)

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 is means
Exclusive LockOnly the requestor has access to the object. No other session can access the object until the lock is released.
Write LockWhat it means
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
 Whole DatabaseExclusive
 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
Access LockGrantedGrantedGrantedGrantedWait
Read LockGrantedGrantedGrantedWaitWait
Write LockGrantedGrantedWaitWaitWait
Exclusive LockGrantedWaitWaitWaitWait

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

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like