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.
Part of the Teradata SQL Tuning and Query Rewriting guide.
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.
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 bookTransactions 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 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 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 Level | What it means |
| Exclusive Lock | Only the requestor has access to the object. No other session can access the object until the lock is released. |
| Write Lock | Only one session can hold a write lock at a time. Other sessions requesting write or read locks must wait until it is released. |
| Read Lock | Unlimited sessions can hold read locks, during which no modification of that object is permitted. |
| Access Lock | It 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 Statement | The granularity with Indexed Access | The 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 | |
| CREATE DATABASE DROP DATABASE MODIFY DATABASE | Whole Database | Exclusive | |
| CREATE TABLE DROP TABLE ALTER TABLE | Whole Table | Exclusive |
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 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 |
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.
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