What is Teradata Concurrency Control?
Concurrency control prevents different sessions from updating, deleting, or inserting rows simultaneously. 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 transaction statements) 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 lock levels and granularities (i.e., which rows of an object are locked) are 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, and 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 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 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 | Any other session can read the object, but data may be inconsistent as the lock owner may be changing data simultaneously. |
| 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 |
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 many rows on a row hash can cause the transaction’s abort.
Depending on the currently held lock level, additional lock requests for the same objects can be granted, or they 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.