Problems with "WITH(NOLOCK"

Database
Enthusiast

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
7 REPLIES
Teradata Employee

Re: Problems with "WITH(NOLOCK"

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;
Enthusiast

Re: Problems with "WITH(NOLOCK"

Thank you so much for the answer!

Re: Problems with "WITH(NOLOCK"

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
Junior Contributor

Re: Problems with "WITH(NOLOCK"

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
Enthusiast

Re: Problems with "WITH(NOLOCK"

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 ?

Senior Supporter

Re: Problems with "WITH(NOLOCK"

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

Enthusiast

Re: Problems with "WITH(NOLOCK"

Thank you Ulrich.