DML / DDL operations and their Locking mechanism in Teradata - How to avoid dead locks in Teradata

DML/DDL operations and the Locks acquired

Deadlocks and Deadlock Resolution

A deadlock occurs when transaction 1 places a lock on resource A, and then needs to lock resource B. But resource B has already been locked by transaction 2, which in turn needs to place a lock on resource A. This state of affairs is called a deadlock.

Both the Transaction need the resource of the other for completion

 To resolve a deadlock, Teradata Database aborts the younger transaction, the one that has held the resource the least amount of time, and performs a rollback.

In the diagram below, the Transaction T2 will have to release its lock as its the younger transaction.

Deadlocks in Teradata and their resolution

If you used BTEQ to submit the transaction, the database reports the deadlock abort to BTEQ. BTEQ resubmits only the request that caused the error (the default behavior), not the complete transaction. Because this can result in partially committed transactions, you must take care when writing a BTEQ script to ensure that the transaction is one request. For example, a statement in BTEQ ends with a semicolon (;) as the last non‑blank character in the line. Thus, BTEQ sees the following example as two requests:

    sel * from x;

    sel * from y;

However, if you write these same statements in the following way, BTEQ sees them as only one request:

   sel * from x

   ; sel * from y;

If you want to know more about our online/classroom classes, kindly send us your details and we will call you back.

Locks in Teradata - how to effectively do Insert and Select on the same table:

There are 4 kind of locks in Teradata:


Exclusive - Exclusive locks are placed when a DDL is fired on the database or table, meaning that the Database object is undergoing structural changes. Concurrent accesses will be blocked.

Compatibility: NONE


Write - A Write lock is placed during a DML operation. INSERT, UPDATE and DELETE will trigger a write lock. It may allow users to fire SELECT queries. But, data consistency will not be ensured.

Compatibility: Access Lock - Users not concerned with data consistency. The underlying data may change and the user may get a "Dirty Read"


Read - This lock happens due to a SELECT access. A Read lock is not compatible with Exclusive or Write locks.

Compatibility: Supports other Read locks and Access Locks


Access - When a user uses the LOCKING FOR ACCESS phrase. An Access lock allows users to  read a database object that is already under write-lock or read-lock. An access lock does not support Exclusive locks . An access lock does not ensure Data Integrity and may lead to "Stale Read"


Categorised on Levels, we can have locks at Database, Table or Row-level.


Row Hash Lock: A Row Hash lock is a 1-AMP operation where the Primary Index is utilized in the WHERE clause of the query. 

How it helps: Rather than locking the entire table, Teradata locks only those rows that have the same Row Hash value as generated in the WHERE clause.

Syntax: Locking Row for Access

Practical Example:

This is the situation faced today:

SQL1: Insert into FACT_CUST (col1,col2) Select(col1,col2) from WRK_CUST 

SQL2: Select * from FACT_CUST

Since, SQL1 was submitted first, there is a Write lock on FACT_CUST. SQL2 needs a Read Lock on FACT_CUST. So, it will wait until SQL1 is complete.


When inserting or updating rows from a query (Insert/ Select or Update where the primary index is not specified), a Write lock is placed on the table.

If you Insert Values or Update where PIVal = 12345 (via SQL or Load Utility) then a Write lock is placed at the row level.

If you do a select without specifying the PI values, a Read lock is placed at table level. A Read Lock will not read through an existing write lock (and vice versa) so the one who gets in second will be delayed or bounced if NOWAIT is specified.


If you put a LOCKING ROW (or Tablename) FOR ACCESS on the Select query, the Select will read through any Write lock at row or table level. (So-called "Dirty Read".) This only applies to Select - a Write lock cannot be downgraded to an Access Lock.

To overcome "Stale Read", we can allow read access through Views - put the LOCKING FOR ACCESS clause in the views.

Let me explain the strategy I use:

I have a view that is refreshed everyday.
View definition: 

replace view test_v
as
locking row for access
(
select * from test1
);

Now I load the delta rows into test2. Then once the processing completes and test2 table is ready, refresh the view as:

replace view test_v
as
locking row for access
(
select * from test2
);

Next step will be to move the contents of test2 into test1:

delete from test1 all;
insert into test1 select * from test2;

This will always give consistent data and very little downtime (required only during view refresh)