My musings on database deadlock issues part 2

Sometimes we can just throw(not waste) money at the problem to solve programming woes. It’s good that stackoverflow don’t have to buy Oracle in order to solve their database deadlocks issues, stackoverflow was built in the fortunate time(2007), it was built when SQL Server already has an MVCC option(SQL Server 2005), contrast that to Oracle which uses MVCC since version 3(built in 1983). The decision-makers at stackoverflow just flick a switch(switch their SQL Server database to use MVCC) to solve their database deadlock issues.


Oracle using MVCC as its only database transaction operation tells a lot, I can’t see MVCC as just all about row-versioning, they know the advantages far outweighs the disadvantages, hence them making MVCC the only database transaction operation supported by their database. If Oracle know the disadvantages can outweighs the advantages, they will not hardwire the MVCC architecture to their database.

We can’t fault companies like airlines and financial institutions if they choses Oracle over other RDBMSes; information correctness far matter most than anything else, which MVCC delivers properly, there are no dirty reads with MVCC. And performance is the natural byproduct of MVCC’s writers not blocking the readers and vice versa, sweet deal. MVCC delivers the trifecta of a very scalable database: correct information (delivers the I in ACID), no intermittent deadlocks, performance.


We can see for ourselves the mayhem that nolock can do to our queries(providing correct reports for that matter), it doesn’t even solve the deadlock issues, the very problem most of us are trying to solve with it: http://blogs.msdn.com/b/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx

And we are already aware of the evil that SET TRANSACTION ISOLATION READ UNCOMMITTED can commit in the name of performance.


<microsoft-bashing> I will not expect Microsoft will put MVCC literature on SQL Server BOL, Microsoft doesn’t want to be looked upon as mere copycat or a johnny-come-lately. They will avoid things that will give them those impressions. Case in point, Materialized Views is the de facto nomenclature for er.. Materialized Views, but how do Microsoft named their materialized views feature? Microsoft want to be different, they named their Materialized Views as Indexed Views! I’m not surprised Microsoft didn’t put MVCC nor Materialized Views on their SQL Server documentation. For finding good information on something cannot be found on Microsoft’s official documentation, we can just use our trusted search engines</microsoft-bashing>


I’ll stop here, I’m turning into an MVCC salesman now, hahah :D



As with most things in life, we just have to decide which option that offers advantages that far outweighs its disadvantages. Some are even loading their whole database to memory in order to achieve performance, hardware is getting cheaper, I don’t even see that trend reversing ツ


Happy Coding! ツ