#StackBounty: #sql-server #sql #isolation-level Is snapshot isolation good?

Bounty: 50

My situation:

Table: User { Id, Name, Stone, Gold, Wood }

I have “write” threads:

  • miningThread (every minute)

UPDATE User SET Stone = @calculatedValue WHERE Id=@id
UPDATE User SET Wood = @calculatedValue WHERE Id=@id

  • tradingThread (every minute)

UPDATE User SET gold = @calculatedValue WHERE Id=@id

  • constructionThread (every minute)

UPDATE User SET Wood = @calculatedValue WHERE Id=@id
UPDATE User SET Stone= @calculatedValue WHERE Id=@id

And have “write” requests from users:

  • SellResource

UPDATE User SET Stone(Wood,Gold) = @calculatedValue WHERE Id=@id

(calculatedValue is calculated by C# bussiness logic code)

In this case, I have a lot “lost update” problems if i set read_commited_snapshot isolation level.
But if i set serializable or snapshot level, all work fine

Question

I was looking at an “isolation levels comparison” table and saw that serializable and snapshot isolation solve all problems with concurrent transactions. However, serializable is very slow.

  1. Can I use snapshot isolation for all write transactions? I don’t
    want chaos in my tables. My business logic is hard and always changing.
  2. Does snapshot isolation have flaws?
  3. What isolation level is better for readonly transactions?


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.