Understanding Teradata Locking: Types and Granularity

What is locking in Teradata?

Locking database objects is crucial in RDBMS to prevent multiple users’ concurrent modification and access to the same data, as this can result in data inconsistencies.

The items affected are safeguarded to maintain their integrity while users access or modify data.

Teradata determines the necessary level of granularity for locks and the corresponding limitations on other users’ activities during each data access.

Lock granularity can increase during execution 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 have 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';

A table requires a write lock without a WHERE clause.

Using a WHERE condition on the primary index (CustomerId) will result in using a rowhash lock for the query.

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 in one or multiple partitions, enabling requests to access rows in the table located 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.

Users can influence the system. For example:

SELECT * FROM Customer;

This query typically leads to a read lock.

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

LOCKING Customer FOR ACCESS
SELECT * FROM Customer;

The ACCESS modifier allows data retrieval during table writing.

As before, you can upgrade the lock by changing it from a read lock to a write lock.

LOCKING Customer FOR WRITE
SELECT * FROM Customer;

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, and the SQL statement will end 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 on row hash granularity. If multiple rows are attempted to be locked on a single row hash, it may result in transaction failure.

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

Locking – The official Teradata documentation