Update with nolock - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update with nolock

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 04:30:47

What is the difference between these two statements -

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

Vabhav T

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-24 : 04:38:52

NOLOCK is used for reading uncommitted data.

As far as i know, this hint is not allowed in update statement. Your first update statement will give an error.

Regards,
Bohra

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 04:41:54

But what is the difference as both statements doing same thing but second statement is getting executed successfully and first one is not....

Vabhav T

vikky
Yak Posting Veteran

54 Posts

Posted - 2010-02-24 : 04:56:53

hi,

WITH( nolock) option only used when retrieving the date from database.

select * from tablename with(nolock)
it reads uncommitted data from database.( without waiting already used objects)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-24 : 05:01:26

quote:
Originally posted by vaibhavktiwari83

But what is the difference as both statements doing same thing but second statement is getting executed successfully and first one is not....

Vabhav T

NoLock hint is supported only with Select statement and not with update, insert and delete. NoLock is used when we don't want to apply any kind of lock and read dirty (uncommitted ) data.

For DML actions, Locks needs to be applied for Data consistence.

In your example:
In first statement you are trying to use NoLock with Update (and it will give error)

In second statement, NoLock is applied for the select part and it will work.

For more details, you can refer the below link:
http://www.sqlteam.com/article/introduction-to-locking-in-sql-server
http://msdn.microsoft.com/en-us/library/aa213026(SQL.80).aspx

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 05:03:08

Ultimately we are updating uncommited data right???

Vabhav T

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-24 : 05:14:49

No.

--
Gail Shaw
SQL Server MVP

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 05:40:31

Hey GilaMonster

Please explain how...???

Vabhav T