Teradata Concurrency Control

By Roland Wenzlofsky

July 28, 2015

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 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 kinds 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 a 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 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)

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

Lock LevelWhat is means
Exclusive LockOnly the requestor has access to the object. No other session can access in any way the object until the lock is released.
Write LockAny other session can read the object, but data may be inconsistent as the lock owner may be changing data simultaneously.
Read LockUnlimited sessions can hold read locks, during which no modification of that object is permitted.
Access LockLike the read lock, it will be granted even if another session holds a Write 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 hashWrite
Whole DatabaseExclusive
Whole TableExclusive

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

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

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

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

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

You might also like