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!

You might also like...

Comments

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Hofstadter's Law: It always takes longer than you expect, even when you take into account Hofstadter's Law.”