SQL Azure default isolation level
I came across Bob Beauchemin’s post, stating that the SQL Azure default isolation level is read committed snapshot.
This is generally pretty cool as it increases database concurrency, but should be taken into account when migrating your on-premise database or writing new code that will run on SQL Azure.
For example, suppose you have a process that updates a large table, and the update takes a few minutes.
When working in read committed, reading sessions are blocked, and get the updated value when the upgrade is finished.
When working in read committed snapshot, reading sessions get the last consistent value before the update.
What’s the correct way to work? Both can be the answer, as it depends on the business scenario.
Bear in mind that you can still override this default at the session level using set options.