Library tutorials & articles
SQL Server Locks
Conclusion
Results
It's difficult to quantify the performance gain had by applying these techniques
to Streamload.com, and impossible to speculate as to the effects this would have
on your site. Before we did it, the site was slow, often unusable, and always
unreliable. After we did it, the site was fast, usable, and reliable. Truly,
it was a night and day improvement. And you won't find this if you go searching
through the documentation for help with lock contention. The docs recommend rewriting
your app so that tables are referenced - and hence, locks are attained - in the
same order throughout (yeah, right!), keeping transactions short and in one batch
(a good idea, but in practice "yeah, right!"), use a low isolation level (also
a good idea: NOLOCK takes this to an extreme), and use bound connections to allow
processes to (share locks and) cooperate (sounds like a very complicated bad
idea). I don't get the sense the consultants of the world are aware of (or comfortable
with?) this technique either. But you heard it here, and it's worked great for
Streamload.com. If you're having lock contention problems with SQL Server, it
could work for your site, too.
Big Disclaimer
Use these techniques with caution and discretion. The way I approached it was
to look at all my stored procedures and ad hoc queries, and based on my understanding
of where and how they were used, I decided whether it would be acceptable for
the caller or user to get possibly incorrect results for NOLOCK,
and whether it was likely that more than a few dozen rows would be locked with
ROWLOCK. In almost all cases it was fine, but maybe for your code
you should be more careful. You might need to produce separate procedures based
on whether or to lock, and how to lock. There are other incantations (PAGLOCK,
TABLOCK) which you might want to use when you know the UPDATE
or DELETE query will affect many rows.
Happy Programming!
Related articles
Related discussion
-
how to find second largest number through a query in sql
by om_java2009 (29 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.