SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 The Great no lock Debate
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 02/12/2010 :  15:04:55  Show Profile  Reply with Quote
We were discussing select ... with (no lock) today.

Is it that select ... with (no lock) ignores any locks placed on the table(s)?

OR

select ... with (no lock) avoids placing any read locks on the table(s)

OR

Both???

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/12/2010 :  15:23:36  Show Profile  Reply with Quote
Both. As far as I know NOLOCK or READ UNCOMMITED places no locks and it ignores all existing locks so you can get all sorts of wierd behavior becasue you don't know what state the data is in (deleted, uncommited, etc) This leads to possible duplicate or even missing data.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 02/12/2010 :  16:15:03  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
No lock takes no locks (other than schema stability) and honours no locks (other than schema modification). It's a way to tell SQL 'Get me my data, regardless of anyone doing anything with it, I don't care if the data is slightly inaccurate'

See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 02/13/2010 :  03:33:57  Show Profile  Reply with Quote
"Both???"

Neither, you'll get a syntax error </pedantic mode>

In 99% of the cases of NOLOCK being used in the consultancy work I have done it has been used by people who did not know the consequences and used it to fix a problem - usually DEADLOCK - and made no provision for the dirty rears issues, had no means of testing the scenarios that it occasionally raises, and could not reproduce the errors that annoyed customers / users reported to their support department.

Avoid!

A better quick-fix (from SQL2005 onwards) is to use READ_COMMITTED_SNAPSHOT but, as ever, test first

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=132825
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 02/13/2010 :  03:50:47  Show Profile  Reply with Quote
Another interesting read on Locks:

http://www.sqlteam.com/article/transaction-isolation-and-the-new-snapshot-isolation-level
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 02/16/2010 :  12:43:40  Show Profile  Reply with Quote
Thanks to all for clearing that up.

I agree that the READ_COMMITTED_SNAPSHOT would be ideal. But we have some clients still using 2000.

As pointed out, the no-locks were used to avoid Deadlocks. But some thought that no-lock avoids placing locks but honours exising locks, shows how things get misunderstood. I haven't heard of any reported problems from the customer related to using no-lock. Maybe because we have quick transactions and few rollbacks. Of course just because problems are reported does not rule out that they haven't been happening.

We still have a few select statements that are called outside of transactions and do not have no-lock and then get involved in Deadlocks. (I mentioned this in a previous post: ) I still can't understand how that can happen.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000