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 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) |
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 is 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 | What it means |
| 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.
