Update Query with Nolock hint

sql sql-server-2008-r2 nolock
Ram Mehta picture Ram Mehta · Feb 18, 2014 · Viewed 36.6k times · Source

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

Answer

Amir Keshavarz picture Amir Keshavarz · Feb 18, 2014

(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

Related questions

with(nolock) or (nolock) - Is there a difference?

Everything is based on the assumption that with(nolock) is entirely appropriate for the situtation. There are already plenty of questions out there debating whether or not to use with(nolock). I've looked around and haven't been able to find …

Read More
Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
Select SQL Server database size

how can i query my sql server to only get the size of database? I used this : use "MY_DB" exec sp_spaceused I got this : database_name database_size unallocated space My_DB 17899.13 MB 5309.39 MB It returns me several …

Read More