December 5

1 comments

Doing Teradata Locking The Right Way

By Roland Wenzlofsky

December 5, 2019

access lock, database lock, exclusive lock, locking, read lock, rowhash lock, table lock, transaction, write lock

What is locking?

Locking of database objects is a must in RDBMS to avoid that multiple users can change and access the same data at the same time 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 for what type of activity other users are locked.

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 by using a primary index or unique secondary index (the rowhash is locked in the USI subtable and in 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 at the same time, 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 that are 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 requests of all other users 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 owner of the lock 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 at the same time.

What is an Access Lock?

Access locks allow a user to read “inconsistent” data. Access locks work even if another user holds already 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 Table Write
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.

But if the consistency of the data 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

Roland Wenzlofsky


Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 20 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

You might also like

  • Avatar
    Rajanikanth says:

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

    Reply

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

    Never miss a good story!

     Subscribe to our newsletter to keep up with the latest trends!

    >