8

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

Ram Mehta
  • 449
  • 1
  • 6
  • 20
  • 4
    Usually `(NOLOCK)` is not normally what you want... there are side-effects that might surprise you. `READ COMMITTED SNAPSHOT ISOLATION` might do a better job of giving you what you (probably) want, but it requires database changes. See [this post](http://www.brentozar.com/isolation-levels-sql-server/) for more information. – bhamby Feb 18 '14 at 14:34
  • What are you trying to achieve? – GarethD Feb 18 '14 at 14:37
  • 1
    @GarethD There is a deadlock occring (SQL Error 1205) when there are multiple instances updating the table – Ram Mehta Feb 18 '14 at 14:54
  • I think you want to go the other way with this, rather than specify no locks, you want more restrictive locks, so that the second update won't start until the first one has finished if there is a chance of clashes. This will have knock on effects in terms of delays while waiting for other transactions to commit, but should reduce, if not complete stop deadlocks. – GarethD Feb 18 '14 at 15:44
  • 1
    An update without a lock is a contradiction in terms. –  Feb 18 '14 at 15:51
  • 1
    @GarethD any suggestion on how to accomplish what you described about more resctrictive locks? – Dmitri Caragheaur Jun 18 '21 at 12:42

3 Answers3

7

(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
Amir Keshavarz
  • 3,050
  • 1
  • 19
  • 26
  • 3
    This would get the same error - `NoLock hint is supported only with Select statement and not with update, insert and delete.` – GarethD Feb 18 '14 at 15:44
3

NOLOCK is a select (only) hint and it's much a bad habit form older programmers since it was almost mandatory in SQL Server 7 but since SQL Server 2000 it's most unnecessary. That hint in particular tell the engine the select can read rows even if it is in the middle of a uncommited transaction. Due to this you can experience dirty or ghost reads.

I strongly suggest you to read about isolation levels to know how to meet your particular system requirements.

Obs: Hints are not commands, you are only suggesting the engine to take a hint but the engine can decide to not use it.

jean
  • 4,159
  • 4
  • 31
  • 52
3

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.

Helder Gurgel
  • 590
  • 5
  • 4