Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update Query with Nolock hint

I am trying to put an with(NOLOCK) on an update query:

UPDATE pth_patchLookup with(nolock) SET ScanDateTime = Getdate() WHERE RegID = 312

but I get the following message :

NoLock hint is supported only with Select statement and not with update, insert and delete.

Is there any manner in which I can apply a 'NOLOCK" on this update query ?

Thanks for any help

like image 985
Ram Mehta Avatar asked Feb 18 '14 14:02

Ram Mehta


2 Answers

(NOLOCK) disables shared locks and not exclusive locks. you can use Read Committed isolation level in order to place an exclusive lock on select statements.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
UPDATE pth_patchLookup SET ScanDateTime = Getdate() WHERE RegID = 312
like image 67
Amir Keshavarz Avatar answered Oct 10 '22 16:10

Amir Keshavarz


And this?

UPDATE TOP (1000) v 
SET idSupervisor = a.supervisor
FROM    dbo.Venda_2014 v WITH ( NOLOCK )
INNER JOIN #supervidores_presentes a WITH (NOLOCK) ON v.IdVendedor = a.vendedor AND v.idEmpresaOriginal = a.empresa
WHERE   IdDistribuidor IN ( 40 )
        AND ISNULL(v.idSupervisor,0) = 0
        AND datAnoMesRef >= '201501' 
Go

Working fine for me.

like image 33
Helder Gurgel Avatar answered Oct 10 '22 16:10

Helder Gurgel