Understanding Teradata Locking: Types and Granularity

What is locking in Teradata?

Locking database objects is essential in RDBMS to prevent multiple users from concurrently modifying or accessing the same data in ways that could cause inconsistencies.

The affected objects are protected to maintain their integrity while users access or modify data.

Teradata determines the necessary level of lock granularity and the corresponding restrictions on other users’ activities during each data access.

Lock granularity can escalate during execution, for example by transitioning from a row lock to a 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

Locking a RowHash affects multiple rows because rows with the same Primary Index value also share the same RowHash.

RowHash locks are used in these scenarios:

• An unpartitioned table is queried via the primary index.
• An UPDATE or DELETE statement uses a primary or unique secondary index (the rowhash is locked in the USI subtable and the base table).

Consider the following UPDATE statement:

UPDATE Customer SET Gender = 'm';

Without a WHERE clause, this statement requires a write lock on the entire table.

Using a WHERE condition on the primary index (CustomerId) results in a rowhash lock instead.

UPDATE Customer SET Gender = 'm' WHERE CustomerId = 1;

Rowhash Locking for Row-Partitioned Tables

Rowhash locking enables more precise locking of a partition or range of partitions in a row-partitioned table.

The RowHash is locked within one or multiple partitions, allowing other requests to access rows in separate partitions or with a different rowhash.

Teradata Locking Types

How are Teradata Locks selected?

Teradata selects the most appropriate lock automatically for every scenario.

SQL Statement	The granularity with Index Access	The granularity with FTS or NUSI	Lock Level Mode
SELECT	Row Hash	Table	Read
UPDATE	Row Hash	Table	Write
DELETE	Row Hash	Table	Write
INSERT	Row Hash		Write
CREATE/DROP/MODIFY DATABASE		Database	Exclusive
CREATE/DROP/ALTER TABLE		Table	Exclusive

Users can influence the lock level. For example:

SELECT * FROM Customer;

This query typically results in a read lock.

Lower the lock to an access lock if data consistency is not critical.

LOCKING Customer FOR ACCESS
SELECT * FROM Customer;

The ACCESS modifier allows data retrieval while the table is being written to.

You can also upgrade the lock by changing it from a read lock to a write lock.

LOCKING Customer FOR WRITE
SELECT * FROM Customer;

Other users who need access to the customer table with read, write, or exclusive privileges must wait for your query to finish.

Locking Modifiers

LOCK DATABASE/TABLE/VIEW/ROW 
FOR ACCESS/EXCLUSIVE/READ/WRITE 
[{NOWAIT}

The NOWAIT option is optional; when specified, the SQL statement will end immediately if the desired lock is unavailable.

Locking Summary

• 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

Also refer to:

A Warning Regarding Teradata Locking

Due to resource limitations, the Teradata lock manager can only apply locks at row hash granularity. If multiple rows map to the same row hash and locking fails as a result, the transaction may fail.

Please refer to the following resources for more information on this topic.

Locking – The official Teradata documentation

Understanding Deadlocks in Teradata: Prevention and Handling Strategies