Understanding Teradata Locking: Types and Granularity

Roland Wenzlofsky

May 3, 2023

minutes reading time


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

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 if 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, other users can hold unlimited read locks (and access locks) simultaneously 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 selects the most appropriate lock automatically for every scenario.

SQL StatementThe granularity with Index AccessThe granularity with FTS or NUSILock Level Mode
SELECTRow HashTableRead
UPDATE Row Hash TableWrite
DELETE Row Hash Table Write
INSERT Row Hash Write
CREATE/DROP/MODIFY DATABASEDatabaseExclusive
CREATE/DROP/ALTER TABLETable Exclusive

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
teradata locking
Teradata locking queues

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

  • Avatar
    Rajanikanth says:

    Further to this, we have a full table lock or row hash lock to optimize the locking.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >