Thursday, January 10, 2013

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:

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


  1. I have been dealing with deadlock issues since SQL Server 6.5 (know I'm sounding like grandpa...)

    Last year I had to develop a .NET project in PostgreSQL. I knew Oracle for long time, since 8.1 and it was pretty much the same concept here.

    When I tested a deadlock scenario I was more than happy to see how elegant PostgreSQL handles them. I encourage you to spend 2 hours with PostgreSQL and test for yourself, it works out of the box. I can't stress how great DBMS it is.

    Nice blog by the way. Cheers.

    1. Nice to know you suggested Postgresql. That was the database we're using in my old company, I love Postgresql it's very capable, e.g. Plus it uses MVCC too. I spend about 4 years using Postgresql, its flexibility and features are very much advanced than what SQL Server can do

      I'm now working in a company that uses SQL Server, and they are reluctant to turn on its MVCC(already present since version 2005, albeit not turned on by default). They think Microsoft can't vouch SQL Server 2008's MVCC capability, just because it is not turned on by default