Reputation: 1
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 SNAPSHOT
to 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?
Upvotes: 0
Views: 156