1

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?

Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164

3 Answers3

5

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;
JNevill
  • 46,980
  • 4
  • 38
  • 63
  • Using views is a good advice. Unfortunately, I am not allow to perform changes, but I can ask MDW developers to wrap the tables I am accessing into "lock row for access" views. – Alexei - check Codidact Apr 27 '17 at 18:37
  • @Alexei: In a Teradata environment most end users only got access to 1:1 views including LOCK ROW ACCESS, thus those views might already exist. Additionally, unless you Select using the PI of the source table there's no difference between LOCK ROW/TABLE. Btw, MDW = a big retailer from Germany? – dnoeth Apr 27 '17 at 19:54
  • @dnoeth - oh, sorry. MDW = master data warehouse. I have seen it used as an acronym in several local companies and I thought it is a known acronym, but you and Google proved me wrong. And yes, it's about a big retailer from DE's data warehouse. – Alexei - check Codidact Apr 27 '17 at 19:57
  • @dnoeth - about the views, I will check with warehouse devs. The tables I am working contain metadata information about installations, ETL objects tables/view etc. and are not used by any normal user. Thanks. – Alexei - check Codidact Apr 27 '17 at 20:02
  • @Alexei: The **M** in *MDW* is not **Master**, it's the company name :-) – dnoeth Apr 27 '17 at 20:07
2

Not a direct answer, but it's always been my understanding that this is one of the reasons your users/applications/etc should access data through views. Views lock for access, which does not prevent inserts/updates. Selecting from a table uses read locks, which will prevent inserts/updates.

The downside is with access locks, the possibility for dirty reads exists.

Andrew
  • 8,445
  • 3
  • 28
  • 46
0

Change your query as below and you should be good.

LOCKING TABLE PersistentTable FOR ACCESS 
INSERT INTO SomeVolatile
SELECT Cols
FROM PersistentTable ;
Nishanth
  • 5
  • 2