Teradata - how to select without locking writers? (LOCKING ROW FOR ACCESS vs. LOCKING TABLE FOR ACCESS)

Question

I am developing an application which fetches some data from a Teradata DWH. DWH developers told me to use LOCK ROW FOR ACCESS before all SELECT queries to avoid delaying writes to that table(s).

Being very familiar with MS SQL Servers's WITH(NOLOCK) hint, I see LOCK ROW FOR ACCESS as its equivalent. However, INSERT or UPDATE statements do not allow using LOCK ROW FOR ACCESS (it is not clear for me why this fails, since it should apply for table(s) the statement selects from, not to the one I insert into):

-- this works
LOCK ROW FOR ACCESS 
SELECT Cols
FROM Table

-- this does not work
LOCK ROW FOR ACCESS 
INSERT INTO SomeVolatile
SELECT Cols
FROM PersistentTable

I have seen that LOCKING TABLE ... FOR ACCESS can be used, but it is unclear if it fits my need (NOLOCK equivalent - do not block writes).

Question: What hint should I use to minimize writes delaying when selecting within an INSERT statement?

Answer 1

You can't use LOCK ROW FOR ACCESS on an INSERT-SELECT statement. The INSERT statement will put a WRITE lock on the table to which it's writing and a READ lock on the tables from which it's selecting.

If it's absolutely imperative that you get LOCK ROW FOR ACCESS on the INSERT-SELECT, then consider creating a view like:

 CREATE VIEW tmpView_PersistentTable AS
 LOCK ROW FOR ACCESS
 SELECT Cols FROM PersistentTable;

And then perform your INSERT-SELECT from the view:

 INSERT INTO SomeVolatile
 SELECT Cols FROM tmpView_PersistentTable;