8

I have a lot of deadlocks going on in my system.

I would like to use Snapshot Isolation to fix them, but my DBA has reserves about it.

One of his concerns is that Snapshot Isolation slows down writes. This is because it has to write to cache and then to the TempDb (row version) and then it can return to the caller.

A "normal" write can just write to the cache and then be done.

Is this how the row versioning works? Or is it more complex than that? Does it somehow do these in parallel?

Or are writes slower with Snapshot Isolation?

Vaccano
  • 2,550
  • 5
  • 31
  • 56

2 Answers2

14

is because it has to write to cache and then to the TempDb (row version) and then it can return to the caller.

No, this is incorrect. It somehow implies that writes in the presence of versioning have higher latency as each write has to touch the disk (for tempdb) which is not true. The write into the tempdb is also a write into 'cache'. The only 'wait' occurs at COMMIT time when the log has to be hardened. Is true that with versioning both the DB log and the tempdb log have to be hardened, but this does not necessarily implies higher latency (the IO should be paralel on different storage paths, the tempdb is stored on separate drive from your heavily used LDF, right?). For a full blown explanation read How It Works: Bob Dorr's SQL Server I/O Presentation I really hope your DBA understands this better than you are conveying it here.

As I mentioned in your other post: snapshot has no cost for INSERTS and the cost for updates and deletes can easily be mitigated. Row Versioning Resource Usage explains the trade offs. At this moment you should be probably testing with a realistic workload, which is the only way to properly asses the impact you would experience.

Glorfindel
  • 2,205
  • 5
  • 19
  • 26
Remus Rusanu
  • 52,054
  • 4
  • 96
  • 172
0

I believe there are other problems in the application if you are getting deadlocks. Snapshot isolation usually helps reduce wait locks but the root of deadlocks is usually different access methods in the application which should be prevented by adhering to a consistent pattern. Discussions on deadlocks are complicated and there are many resources dedicated to them.

Your DBA is right to have some concerns about changing the isolation level to snapshot since it increases the load on the tempDB. I recommend going to snapshot isolation in general, but I think that's only one piece of addressing your deadlocks. You may end up with dirty writes, where one transaction updates row A then B, and an other transaction updates row B then A.

Robert-Ryan.
  • 1
  • 1