3

We recently upgraded our SQL production environment from 2008R2 to 2017. I am finally at a point where I want to try optimistic locking in the form of using snapshot isolation. I read Brent Ozar's article suggesting to SET ALLOW_SNAPSHOT_ISOLATION ON at the database level, and then alter queries to take advantage of the new to us feature. To my surprise, my manager who is very knowledgeable in sql, did not want to move in this direction. I understood him to say that we depended on the deadlocks to prevent us from reading dirty data, and saw no reason to solve a problem that did not exist. I did not know how to respond to him. I have never considered deadlocks as anything other than something to try and eliminate, not depend on as a way of life. I get that they do an important function, but are a sign of something that can be improved.

My question is how many others rely on deadlocks to such an extant that you would not consider trying to eliminate them?

Improve this question
3
  • Are you sure your manager said "deadlocks" and not "locks"?
    – mustaccio
    Commented Oct 3, 2018 at 19:34
  • We were quite specifically talking deadlocks, not blocking. We have one particular database that generates 90% of the deadlocks in our environment, with a small handful of tables involved. I did not design it, but essentially many write operations deadlocking with read operations that cannot use nolock.
    – JohnH
    Commented Oct 3, 2018 at 19:41
  • No.it's not - if anything, the deadlocks are too random. It seems your manager is not that knowledgeable after all.
    – dean
    Commented Oct 3, 2018 at 21:27

1 Answer 1

Reset to default
4

Snapshot and Read Committed Snapshot isolation levels do not prevent deadlocks from happening. They make the engine using row versioning, so readers do not block writers and writers do not block readers as SQLServer does by default. That improves concurrency , but requires changes to the applications that rely on "readers block writers and writers block readers" . I guess you misunderstood your manager , and your system relies on that SQLServer default blocking behavior, not on deadlocks which in most cases can be avoided by application design.

If I happen to work with SQLServer on a new system, I'll surely switch it to use row versioning instead of locking.

However, for an existing system, it is usually safer and cheaper not to fix what is not broken (especially when it's not broken).

Improve this answer
2
  • I read learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/… , and definitely get the idea that this would prevent a deadlock from killing a reader when a write is happening. This is what I was trying to solve. Using SentryOne I can see a nice graphical rendition of the deadlock, and it seems to be the read process that gets killed.
    – JohnH
    Commented Oct 3, 2018 at 20:08
  • In such case yes, deadlock won't happen with snapshot isolation levels. But even without snapshot, the deadlock is not guaranteed to happen - the order of locking objects (rows,pages,tables) identified by internal process controlled by optimizer - so during one execution write transaction will wait until read is completed, during other execution you can get a deadlock. So application cannot rely it always happen; it rather expects it to happen and reacts with (possibly) re-issuing transaction.
    – a1ex07
    Commented Oct 3, 2018 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.