All Forums Database
aolympio 25 posts Joined 04/09
03 Apr 2009
Problems with "WITH(NOLOCK"

Hi!I'm trying to perform a query like this:SELECT FIELD FROM SOME_TABLE WITH(NOLOCK) WHERE FILD > 0;But it returns this error:"5628: Column NOLOCK not found in MY_DATABASE."What can I do to perform my query using WITH(NOLOCK)?Is it possible in Teradata or is there another solution to get the same result?Thanks for help,Anderson

Fred 1096 posts Joined 08/04
03 Apr 2009

WITH(NOLOCK) is SQL Server syntax. Teradata equivalent is LOCKING modifier requesting downgrade to ACCESS locks.LOCKING ROW FOR ACCESS SELECT FIELD FROM SOME_TABLE WHERE FILD > 0;

aolympio 25 posts Joined 04/09
03 Apr 2009

Thank you so much for the answer!

rmarnao 1 post Joined 03/10
13 Oct 2010

ok, but only is valid for simple querys, not for joins querys, for example:

LOCKING ROW FOR ACCESS Select A.* from TABLE_1 A
LEFT OUTER JOIN (
LOCKING ROW FOR ACCESS Select * From TABLE_2
) B
ON A.FIELD = B.FIELD

but, in Transact-SQL :

Select A.* from TABLE_1 A WITH(NOLOCK)
LEFT OUTER JOIN (
Select * From TABLE_2 WITH(NOLOCK)
) B
ON A.FIELD = B.FIELD

Success!!

Ramiro Matos
Lima - Peru

dnoeth 4628 posts Joined 11/04
13 Oct 2010

Hi Ramiro,
you don't need to repeat the row lock, it just:

LOCKING ROW FOR ACCESS Select A.* from TABLE_1 A
LEFT OUTER JOIN (
Select * From TABLE_2
) B
ON A.FIELD = B.FIELD

Dieter

Dieter

SHERIN JEYABOSE 18 posts Joined 11/14
17 Dec 2014

Could you please guide me, if there is a situation that you did "LOCK ROW FOR ACCESS" on a table initially, but, in one point you dont need to lock the table/row, you just need to do a "Dirty select" like in SQL - WITH (NOLOCK) ... in that case... how do we tackle in TERADATA ?

ulrich 816 posts Joined 09/09
17 Dec 2014

LOCK ROW FOR ACCESS is the "dirty read" equivalent. So 
LOCK ROW FOR ACCESS 
select * from yourtab
will give you results even if insert, deletes or updates are ongoing during the same time.
Ulrich

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

SHERIN JEYABOSE 18 posts Joined 11/14
19 Dec 2014

Thank you Ulrich.

You must sign in to leave a comment.