LOCKS IN TERADATA – Explained

In Teradata , LOCKS are used to maintain integrity of data present in the tables. Teradata , itself decides the kind of lock to implement depending on the type of query however in some cases users can also decide what kind of lock, the query should use. In TERADATA, locks can be implemented at three levels: DATABASE , TABLE and ROW HASH.

types of locks in teradata

There are 4 types of LOCKS in TERADATA:
Exclusive
Write
Read
Access

Exclusive Lock

EXCLUSIVE LOCKS are applied on a table when it is undergoing any STRUCTURAL change. All DDL commands apply exclusive LOCK. If exclusive lock is applied then no other locks can be applied on that table.

Write Lock

WRITE LOCKS are applied when any tables data demography changes by either DELETE,INSERT or UPDATE operation on it. So whenever a DML operation is performed on the table , such locks are applied. Only ACCESS locks are permissible when WRITE locks are applied on the table. And ACCESS Locks may lead to STALE DATA READ from the table since data demography would be changed by DML operations holding WRITE LOCKS.

READ Lock

Read LOCKS are applied when user tries to READ data from the table and don’t want to fetch STALE DATA. READ locks maintains the data integrity as the DATA in the tables cannot be modified while READ LOCK is applied on the tables. READ LOCK only allow READ or ACCESS LOCK on the table. READ Locks are the default lock used by Teradata while fetching data from any Table.

ACCESS Lock

Access locks are applied when table needs to be accessed and the data demographics is not that important; even stale data is ok for the process. ACCESS LOCKS allows READ, WRITE or ACCESS Locks to be applied on the table. However EXCLUSIVE locks are not allowed.

Teradata Locks Summary Table

teradata locking table for access

Proper LOCKS on table can help in reducing delay time in the query. If the table is locked then other queries accessing the same table has to wait for earlier query to finish to free the lock.

It is a good practice to apply ACCESS locks on the source table when multiple SQL Queries are using same Source Table. Even if the TARGET table is different still it can lead to bad performance as by default TERADATA applied READ lock.

Understanding LOCKS and LOCKING table manually can be very useful in an environment where several tables are loaded in parallel. Teradata automatically blocks the query trying to apply incompatible LOCK on the table.

You can use below command before your actual DML commands to lock table for access in place of read lock.

LOCKING SOURCE_TABLE FOR ACCESS
INSERT INTO TARGET_TABLE
SELECT * FROM SOURCE_TABLE;

The other good practice is to use PRIMARY INDEX column while fetching data from SOURCE table. In such cases, the query becomes an AMP operation and Teradata applies lock at ROW HASH level and not at Table level.

teradata locking row for access

If you are running only SELECT statement and using Primary Index column in Filter condition then you can use row level lock. Primary Index leverages hashing and in this case you can apply only access locks on hashed rows.

LOCKING ROWS FOR ACCESS
SELECT * FROM SOURCE_TABLE 
WHERE PI_Column = 'some value';

how to release lock on table in teradata

Sometimes you may want to release lock on table in teradata. Then you can use RELEASE command to free the table from any lock.

RELEASE DB_Name.Table_Name;

The query you were running might have failed and left the lock on the table to support restart/resume capability. This generally happens when using Teradata utilities like Fastload or Multiload.

You may need some extra privileges to run RELEASE command in teradata.