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!
Comments