Doing Teradata Locking The Right Way
What is locking?
Locking of database objects is a must in RDBMS to avoid that multiple users can change and access the same data at the same time in a way that is causing data inconsistencies.
In other words, whenever a user is accessing or changing data, the affected objects are locked to ensure integrity.
For each access to data, Teradata decides how granular a lock must be and for what type of activity other users are locked.
The granularity of locking 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 are locked
- Rowhash Locks: One ore 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 by using a primary index or unique secondary index (the rowhash is locked in the USI subtable and in the base table).
For example, take the following UPDATE statement:
UPDATE Customer SET Gender = 'm';
Without using a WHERE condition, a write lock to the table is required.
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 at the same time, 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 that are either in different partitions or have a different rowhash.
Teradata Locking Types
Locks are only applied to databases or tables.
They are the strictest locks because all requests of all other users are queued if an object is exclusively locked. Exclusive locks are rarely used, usually when databases are restructured.
Write locks allow the owner of the lock to change data, all other users only reading with a so-called “dirty read”, because the data can be inconsistent. As long as there is a write lock, no new write or read locks are allowed.
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 by other users on the same object at the same time.
Access locks allow a user to read “inconsistent” data. Access locks work even if another user holds already a write lock on the object. Access Locks are not allowed if there is an exclusive lock on the object.
How Teradata Locks are selected?
Teradata automatically takes care of which lock is best suited for a particular situation:
|SQL Statement||Granularity with Index Access||Granularity with FTS or NUSI||Lock Level Mode|
As a user can influence it. Here is an example:
SELECT * FROM Customer;
The above query normally causes a read lock.
But if the consistency of the data is not important, 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 being 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, a write lock or exclusive lock on the customer table must now wait until your query is finished.
NOWAIT is optional and means that the SQL statement will be aborted if the desired lock is not available.
- 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 a limited amount of resources to lock on row hash granularity. The attempt to lock a huge amount of rows on row hash can cause the transaction aborts.
More information on this topic can be found here: