Snapshot isolation

[Throckmorton]

Does anyone have experience with snapshot isolation level on sql server? We are getting a lot of deadlocks

with read committed.

Thanks!

[CSharper]

With isolation level Read Committed without snapshot isolation enabled you generate non-exclusive locks even when just reading data. When writing you need an exclusive lock which you only get if there is no read-lock on the same item yet.

So if you do parallel reading and writing of data you risk dead locks, e.g. in the following sequence

T1: read A

T2: read B

T1: write B' over B (T1 goes to waiting state)

T2: write A' over A (deadlock!)

 

So if you experience such situations, just turn snapshot isolation on (I don't know why they don't do that by default).

The result is that

T1: read A

T2: read B

T1: write B' over B (succeeds and keeps a copy of B for transaction T2)

T2: write A' over A (succeeds and keeps a copy of A for transaction T1)

 

It's not very expensive if you usually have short transactions and it really helps.

 

By the way: snapshot isolation is not an isolation level by itself but a concept that makes the read committed isolation level usable.

[Jason Meckley]

Something else to consider is evaluating the queries causing the deadlocks and the indexing of the tables used by the queries. I have been able to resolve deadlock issues by improving the queries and indexing rather than altering the connection/command settings.

[Throckmorton]

On Wednesday, July 11, 2012 12:55:08 PM UTC-4, Jason Meckley wrote:

Something else to consider is evaluating the queries causing the deadlocks and the indexing of the tables used by the queries. I have been able to resolve deadlock issues by improving the queries and indexing rather than altering the connection/command settings.

Thanks, yes, we need to get to that eventually. But, right now, we have a large complex application that needs to be rolled out this fall,

and we simply don' t have time for this analysis.