July 28


Teradata Concurrency Control

By Roland Wenzlofsky

July 28, 2015

access lock, exclusive, locking, read lock, transaction, write lock

What is Teradata Concurrency Control?

Concurrency control prevents that different sessions are updating, deleting or inserting rows at the same time. Two mechanisms are used to for concurrency control: Locking and Transactions.

Teradata Transactions

Transactions define logical units which are encapsulating one or several statements. All statements of the same transaction have to succeed. Otherwise, the transaction (and therefore all statements of the transaction) will be rolled back.

Teradata Locks

Locks define usage rights on database objects like databases, tables, views, rows, etc.

Depending on the object type and the activity, different kind of lock levels and granularities (i.e. which rows of an object are locked) available.

Usually, The Teradata lock manager locks the objects automatically. Implicit locks are done by the lock manager on database, tables, views and rows (based on the row hash value).

There are four different levels of locking available: Exclusive, Write, Read, Access.

The Teradata lock manager decides automatically which lock level will be used (this depends on the target object and the operation taking place).

Database objects are locked when a transaction is initiated and unlocked as soon as the transaction finishes, ensuring data consistency.

Depending on the object being locked and the activity (SQL statement), one of the following locking granularities will be automatically used:

Object Type Locked Automatic Lock Handling
Database Locks all tables located in the database
Table Locks all table rows and secondary indexes
View Locks all tables used in the view
Row Locks the row (identified by the row hash)

Besides defining the locking granularity, each lock has to be based on one of the below lock levels. Each of them has some limitations if other locks on the same object are already held by another session:

Lock Level What is means
Exclusive Lock Only the requestor has access to the object. No other session can access in any way the object until the lock is released.
Write Lock The object can be read by any other session, but data may be inconsistent as the lock owner may be changing data at the same time.
Read Lock Unlimited sessions can hold read locks, during which no modification of that object is permitted.
Access Lock Similar to the read lock, but it will be granted even if a Write Lock is held by another session. This may result in inconsistent data being read.

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

SQL Statement Granularity with Indexed Access Granularity with FTS or NUSI Access Lock Level Mode
SELECT Row hash Whole Table Read
UPDATE Row hash Whole Table Write
DELETE Row hash Whole Table Write
INSERT Row hash Write
Whole Database Exclusive
Whole Table Exclusive

One warning: The Teradata lock manager has a limited amount of resources to lock on row hash granularity.  The attempt to lock a huge amount of rows on row hash can cause that the transaction aborts.
Depending on the currently hold lock level, additional lock requests for the same objects can be granted or will have to wait:

Requested Lock Level Active Lock Level
None Access Read Write Exclusive
Access Lock Granted Granted Granted Granted Wait
Read Lock Granted Granted Granted Wait Wait
Write Lock Granted Granted Wait Wait Wait
Exclusive Lock Granted Wait Wait Wait Wait

Locks are released as soon as the session’s transaction is finished.

Roland Wenzlofsky

Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 20 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

You might also like

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

Never miss a good story!

 Subscribe to our newsletter to keep up with the latest trends!