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.
| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2010-02-12 : 15:04:55
|
| We were discussing select ... with (no lock) today.Is it that select ... with (no lock) ignores any locks placed on the table(s)?ORselect ... with (no lock) avoids placing any read locks on the table(s)ORBoth??? |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-02-12 : 15:23:36
|
| 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 |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-12 : 16:15:03
|
| 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 - [url]http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx[/url]--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-13 : 03:33:57
|
"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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-13 : 03:50:47
|
| Another interesting read on Locks:http://www.sqlteam.com/article/transaction-isolation-and-the-new-snapshot-isolation-level |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-02-16 : 12:43:40
|
| 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. |
|
|
|
|
|
|
|
|
The Great no lock Debate