fbpx

Doing Teradata Locking The Right Way

By Roland Wenzlofsky

December 5, 2019


What is locking?

Locking of database objects is a must in RDBMS to avoid that multiple users can change and access the same data simultaneously in a way that is causing data inconsistencies.

In other words, whenever a user is accessing or changing data, the affected objects are locked to ensure integrity.

For each access to data, Teradata decides how granular a lock must be and what type of activity other users are locked in.

The granularity of locking 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 are locked
  • Rowhash Locks: One ore 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 index 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';

Without using a WHERE condition, a write lock to the table is required.

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 either in different partitions or have 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 reading with a so-called “dirty read” because the data can be inconsistent. As long as there is a write lock, no new write or read locks are allowed.

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, unlimited read locks (and access locks) can be held by other users on the same object simultaneously.

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 Teradata Locks are selected?

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

SQL StatementGranularity with Index AccessGranularity 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

As a user can influence it. Here is an example:

SELECT * FROM Customer;

The above query normally causes a read lock.

If the data’s consistency is not important, 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 being 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, a write lock, 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 that the SQL statement will be aborted if the desired lock is not available.

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

See also:

A Warning Regarding Teradata Locking

The Teradata lock manager has a limited amount of resources to lock on row hash granularity.  The attempt to lock a huge amount of rows on row hash can cause the transaction aborts.

More information on this topic can be found here:

Locking – The official Teradata documentation

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy the Book Teradata Query Performance Tuning

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

  • 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

    >