DWHPro Insights

Understanding Teradata Locking: Types and Granularity

By Roland Wenzlofsky ·

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.

Watch: Teradata locking in 5 minutes

This video shows the lock levels and lock types from this article in action: when Teradata locks a row hash instead of the table, why a SELECT can wait behind a queued UPDATE, and what LOCKING FOR ACCESS and NOWAIT cost.

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 and duration depend on the request, release and transaction mode. Do not assume a general row-to-table escalation rule; inspect the plan and documented locking behavior.

Which Objects can be locked?

  • Database Locks: All Objects in a database are locked
  • Table Locks: All table rows are locked
  • Access through views locks the underlying objects needed by the request.
  • 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.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

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

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 Table Write
DELETE Row Hash Table Write
INSERT Row Hash Write
CREATE/DROP/MODIFY DATABASEDatabaseExclusive
CREATE/DROP/ALTER TABLETable 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 requests needing incompatible READ, WRITE or EXCLUSIVE locks can wait until the lock is released. Locks are distinct from privileges, and lifetime depends on transaction boundaries.

Locking Modifiers

-- Syntax outline: replace object type, object name and lock mode.
LOCKING TABLE YourDatabase.YourTable FOR READ NOWAIT
SELECT * FROM YourDatabase.YourTable;

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

Also refer to:

A Warning Regarding Teradata Locking

Teradata supports database, table, partition and rowhash locking; it is not limited to rowhash locks. In the controlled concurrency test, ACCESS read an uncommitted value, READ NOWAIT failed with error 7423, and rollback restored the original value. Those results do not establish every lock lifetime or escalation scenario.

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

Locking – The official Teradata documentation

Locks are taken on the row hash, which makes hash distribution part of the picture: what happens when different values share a row hash.

Related storage-level reading: the I/O each index type causes and ALTER TABLE against rebuilding with INSERT.

On the row hash itself: what a hash collision costs.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

1 thought on “Understanding Teradata Locking: Types and Granularity”

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam