Teradata - how to select without locking writers? (LOCKING ROW FOR ACCESS vs. LOCKING TABLE FOR ACCESS)
Solution 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;
Solution 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.
Alexei - check Codidact
C#.NET & SQL Server developer with a strong Angular flavor. If you are interested in a software development community with less drama and more tolerance to "opinioned" questions, check Software Development Codidact.
Updated on December 02, 2022Comments
-
Alexei - check Codidact over 1 year
I am developing an application which fetches some data from a Teradata DWH. DWH developers told me to use
LOCK ROW FOR ACCESS
before allSELECT
queries to avoid delaying writes to that table(s).Being very familiar with MS SQL Servers's
WITH(NOLOCK)
hint, I seeLOCK ROW FOR ACCESS
as its equivalent. However,INSERT
orUPDATE
statements do not allow usingLOCK 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 almost 7 yearsUsing 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.
-
dnoeth almost 7 years@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?
-
Alexei - check Codidact almost 7 years@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 almost 7 years@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.
-
dnoeth almost 7 years@Alexei: The M in MDW is not Master, it's the company name :-)