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)