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

How are Teradata Locks selected?

Teradata automatically takes care of which lock is best suited for a particular situation:

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.

Locking Modifiers

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

NOWAIT is optional and means the SQL statement will be aborted 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

See also:

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:

Locking – The official Teradata documentation