What is locking in Teradata?
Locking database objects is a must in RDBMS to avoid multiple users changing and accessing the same data simultaneously in a way that is causing data inconsistencies.
In other words, the affected objects are locked to ensure integrity whenever a user is accessing or changing data.
For each access to data, Teradata decides how granular a lock must be and what type of activity other users are locked in.
Locking granularity can also be increased during execution (e.g., from row lock to table lock).
Which Objects can be locked?
- Database Locks: All Objects in a database are locked
- Table Locks: All table rows are locked
- View Locks: All table accesses by a view are locked
- Partition Locks: All rows of a partition or several partitions are locked
- Partition Range Locks: A range of a table’s partitions is locked
- Rowhash Locks: One or more rows of a table are locked
Rowhash Locking Details
If a RowHash is locked, this affects one or more Rows because all Rows with the same Primary Index Value have the same RowHash.
A RowHash lock is used in the following cases:
- An unpartitioned table is queried via the primary index.
- An UPDATE or DELETE statement is made using a primary or unique secondary index (the rowhash is locked in the USI subtable and the base table).
For example, take the following UPDATE statement:
UPDATE Customer SET Gender = 'm';
A write lock to the table is required in the absence of a WHERE condition.
If we change the query and use a WHERE condition on the primary index (CustomerId), a rowhash lock is used:
UPDATE Customer SET Gender = 'm' WHERE CustomerId = 1;
Rowhash Locking for Row-Partitioned Tables
If rowhash locking is performed on a row-partitioned table, the partition (or a range of partitions) can be locked simultaneously, i.e., it can be locked more specifically.
In total, the RowHash is then locked within one or more partitions. This means that other requests can access rows in the table in different partitions or have a different rowhash.
Teradata Locking Types
What is the Exclusive Lock?
Locks are only applied to databases or tables.
They are the strictest locks because all other users’ requests are queued if an object is exclusively locked. Exclusive locks are rarely used, usually when databases are restructured.
What is a Write Lock?
Write locks allow the lock’s owner to change data. All other users only read with a so-called “dirty read” because the data can be inconsistent. No new write or read locks are allowed as long as there is a write lock.
What is a Read Lock?
Whenever a user holds a read lock on an object, no write locks or exclusive locks can be obtained by any other users. However, unlimited read locks (and access locks) can be held simultaneously by other users on the same object.
What is an Access Lock?
Access locks allow a user to read “inconsistent” data. Access locks work even if another user already holds a write lock on the object. Access Locks are not allowed if there is an exclusive lock on the object.
How are Teradata Locks selected?
Teradata automatically takes care of which lock is best suited for a particular situation:
|SQL Statement||The granularity with Index Access||The granularity with FTS or NUSI||Lock Level Mode|
As a user can influence it. Here is an example:
SELECT * FROM Customer;
The above query typically causes a read lock.
If the data’s consistency is not essential, you can downgrade the lock to an access lock:
LOCKING Customer FOR ACCESS SELECT * FROM Customer;
The LOCKING FOR ACCESS modifier is particularly interesting because the data can be accessed even when the table is written.
Analogous to the previous example, you can upgrade the lock from a read lock to a write lock:
LOCKING Customer FOR WRITE SELECT * FROM Customer;
Any user who needs a read lock, write, or exclusive lock on the customer table must now wait until your query is finished.
NOWAIT is optional and means the SQL statement will be aborted if the desired lock is unavailable.
- Exclusive Lock: No concurrent access allowed
- Write Lock: No concurrent read, write, and exclusive locks allowed
- Read Lock: No concurrent write and exclusive locks allowed
- Access Lock: No concurrent exclusive locks allowed
A Warning Regarding Teradata Locking
The Teradata lock manager has limited resources to lock on row hash granularity. The attempt to lock many rows on a row hash can cause the transaction to abort.
More information on this topic can be found here: