My musings on database deadlock issues

Dirty reads yields dirty information (pardon my tautology)



Making a correct program fast is easier than making a fast program correct. It’s the latter(fast, yet has incorrect information) what SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED delivers, it’s Microsoft’s work-around for deadlocks, instead of delivering true isolation.



Think of MVCC transaction isolation as users having their own database copy(or scratchpad? I’m not good at analogy) for their transactions, whatever writes they are doing to their database copy, deletes, inserts, updates, etc, just happens in their own database copy only and they doesn’t affect the official state of the database. This yields vast improvements when doing concurrent access(which is the norm) on your database, the writers(insert,update,delete) won’t ever affect other user’s SELECT, hence giving performance boost when accessing the official state of your database. Good for reports and information browsing, good for developers and clients alike.



And MVCC yields correct information to boot. It’s hard not to swoon on that points alone(correct information); plus it’s fast, there’s no waiting/deadlocks incurring between writers and readers. It eludes me why SQL Server didn’t make MVCC the default.



Correctness should rule everything around us



“I find that deadlocks are difficult to understand and even more difficult to troubleshoot. Fortunately, it's easy enough to fix by setting read committed snapshot on the database for our particular workload. But I can't help thinking our particular database vendor just isn't as optimistic as they perhaps should be.“ -- http://www.codinghorror.com/blog/2008/08/deadlocked.html



SET READ_COMMITTED_SNAPSHOT ON (turns on MVCC) is what fixes the stackoverflow website’s performance issues. Jeff Atwood is the author of www.codinghorror.com and http://www.stackoverflow.com




nolock is no joy too, being able to read a row is not guaranteed with nolock(a more granular SET TRANSACTION ISOLATION READ UNCOMMITTED):
http://blogs.msdn.com/b/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx

And try to reproduce the same scenario on the above link without nolock, and using MVCC settings, It Just Works™ :

ALTER DATABASE testDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE testDatabase SET READ_COMMITTED_SNAPSHOT ON;