0

Over the last few years, we have noticed an increase in the number of deadlocks and long-running transactions in our SQL server (2008r2) database. We currently run our database with the following settings ALLOW_SNAPSHOT_ISOLATION OFF and READ_COMMITTED_SNAPSHOT OFF. Deadlocks are managed from code using a process of retries/waits.

Our application code (c#) creates a SqlConnection using the default constructor (from decompiling appears to be IsolationLevel.Unspecified) which I believe allows SQL to do it's own thing, which in our case would be the same as READ COMMITTED (I think).

We are looking to introduce snapshots by setting ALLOW_SNAPSHOT_ISOLATION ON and READ_COMMITTED_SNAPSHOT ON and overriding the c# constructor with RepeatableRead. I believe that this combination of settings will allow our code to function the same as it is right now. This will allow us to piecemeal evaluate our codebase to ensure that the change in isolation level and snapshots have no negative effect on how we perceive the SQL procedures currently function. We would, of course, need to add SET TRANSACTION ISOLATION LEVEL SNAPSHOTto the beginning of any procedure that we want to benefit from the snapshot (to override the c# setting of IsolationLevel.RepeatableRead).

The reason for handling it this way is that the two database settings essentially force us to take our database offline which requires a maintenance window and we would like to prepare for making gradual changes but would like the code to continue to execute the same as it is immediately following the changes.

I'm just looking for some confirmation that my understanding is correct. If I'm wrong, what would do I need to do after making the 2 database changes in order to simulate our current working environment?

Steve
  • 1
  • 2
  • `REPEATABLE READ` is not the same as `COMMITTED`, though. You'll get *roughly* the same behavior (as in, code that locks) but you will still take more locks than you used to, with appropriate changes in behavior (and possibly more locks). I think a better approach is to work the other way around: set `ALLOW_SNAPSHOT_ISOLATION ON` (which, unless I'm very much mistaken, does *not* require the DB to go offline), modify code piecemeal to explicitly use snapshot isolation (prepared for retries) and set `READ_COMMITTED_SNAPSHOT` either not at all, or when you're mostly done. – Jeroen Mostert May 08 '18 at 13:33
  • You're right that setting `ALLOW_SNAPSHOT_ISOLATION ON` doesn't require the database to go offline. However it does require that no open connections exist, thus in a high use system, you have to set the database into `SINGLE_USER` or `RESTRICTED_USER` mode...which essentially means taking it offline. – Steve May 08 '18 at 13:40
  • The problem I have is that setting `ALLOW_SNAPSHOT_ISOLATION ON` will change the behaviour as well and I'd like a combination of settings that allows me to force the behaviour to mimic how things currently work. Unless I'm still misunderstanding? It's worth pointing out that due to the number of procedures that need reviewing, only a subset will be changed in the next 12 months, the rest may remain to behave as they currently do indefinitely! – Steve May 08 '18 at 13:45
  • I'm pretty sure that's not true either. `READ_COMMITTED_SNAPSHOT OFF`, yes. `ALLOW_SNAPSHOT_ISOLATION ON`, no. It should work even if there are open connections. It *does* have to wait for all transactions to finish, but if you have long-running transactions that never finish that's its own problem. – Jeroen Mostert May 08 '18 at 13:47
  • If you want to have `READ COMMITTED` semantics even with `READ_COMMITTED_SNAPSHOT` set to `ON`, you can use the `READCOMMITTEDLOCK` table hint in T-SQL. There is no isolation level that will do the same without such a hint. `ALLOW_SNAPSHOT_ISOLATION` does not *functionally* change the behavior of any client. It only increases the load on tempdb, which now has to track versioned rows. – Jeroen Mostert May 08 '18 at 13:49
  • Point taken on the offline aspect - I agree, once all current connections are closed it will take effect. This can (as you've stated) be a long time depending on how connections are managed and how long some of those remain open – Steve May 08 '18 at 13:50
  • Open transactions != open connections. Feel free to test this in a separate setup (in fact, I encourage you to if you want to take these things to production :-)) Even a heavily used database with many open connections typically has a few moments where it has no open *transactions*. I'm pretty sure I've toggled this option on an actual OLTP database, as in, dozens of inserts/updates going on every second. – Jeroen Mostert May 08 '18 at 14:00

0 Answers0