Library tutorials & articles
SQL Server Locks
- Introduction
- Lock Contention Described
- Lock Contention Solved
- NOLOCK and ROWLOCK
- Conclusion
Lock Contention Solved
If you used Streamload.com at all during June, July, and August, you probably got a "You were the deadlock loser" error, or a "Lock timeout" error, or an "Object required" error. These were all caused by lock contention. After scouring the documentation and talking to a few people, I learned what I summarized above and will say again here:
- SQL Server starts with row-level locks, but often escalates these to page
and table locks, causing deadlocks
- SQL Server requires locks for reading from the database (
SELECTs), so even folks not trying to modify the database are affected by the lock system.
Fortunately, I stumbled across some obscure keywords from the SQL Server lexicon:
NOLOCK and ROWLOCK. They are used like this:
SELECT COUNT(UserID)
|
and
UPDATE Users WITH (ROWLOCK)
|
What do these extra incantations do? We'll examine these two hints - NOLOCK
and ROWLOCK - on the next page!
Related articles
Related discussion
-
how to find second largest number through a query in sql
by austin83 (30 replies)
-
High-Performance .NET Application Development & Architecture
by Manjot Bawa (0 replies)
-
Permutations and combinations of multiple return codes
by actuszeus (1 replies)
-
help needed on tree level sql?
by kareen (0 replies)
-
dtPicker date format query
by konikula (1 replies)
Related podcasts
-
Stack Overflow: Podcast #28
This is the twenty-eighth episode of the StackOverflow podcast, where Joel and Jeff discuss Windows Azure, SQL Server 2008 full text search, Bayesian filtering, porn detection, and project management — among other things. Jeff met the inestimable Joey DeVilla aka Accordion Guy...
To get more information about Isolation levels in SQL Server including Locks and its impact on database in detail with examples, refer the below link: http://www.sqllion.com/2009/07/transaction-isolation-levels-in-sql-server/
I've implemented the [WITH (NOLOCK)] hint and it's been a reasonable success. The only additional note of caution I would add is that we have one table that is occasionally updated (batch inserts).
The problem is that the data is inserted and then the index is updated. If the index is read after the data has been updated but before the index is updated then this can cause the following error:
Number = -2147217900, Description = Could not continue scan with NOLOCK due to data movement., Source = Microsoft OLE DB Provider for SQL Server, SQLState = 42000, Native Error = 601.
Great.
This little hint with those little word (nolock) saved my life - and the life of our application. (http://www.gpsoverip.de)
Thank you VERY much ...
You say (with regard to locking): "For financial code and denormalized aggregates (those little counters of related data that you stash away and try desperately to keep accurate), you should play it safe and not use this technique. But I think you'll find that for better than 90% of your application, it would not be that big of a deal if a user (or even intermediate code) saw an uncommitted modification"
What do you mean? If data is being interrogated, then the returned result set MUST be consistent (ACID).
Seeing uncommitted code is a no-no, whether Financial, denormalized or whatever.
Please contribute your suggestion in relation with various issues related with log file management in SQL server. Such as
1. Methods to Shrinking the log file.
2. Recover the database form Log file
3. Recovery mode etc
This thread is for discussions of SQL Server Locks.