Is snapshot isolation good?

Question

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?

Answer 1

Summary:

Can I use snapshot isolation for all write transactions?

Yes, but depending on usage read committed snapshot might be better.

Does snapshot isolation have flaws?

Yes. It needs to store row versions of all active transactions, which requires disk/memory.

What isolation level is better for readonly transactions?

snapshot isolation, or read committed snapshot depending if there are several statements depending on each other and the size of the transaction. read committed if there are many updates and disk space on tempdb is a concern.

Deeper information

Which isolation level to use depends on your use-case and how you use your data within the transactions. So let's start with a deeper comparison of the levels.

The SQL Server maintains consistency by utilizing two different techniques: Locking and Row versioning.

Locking isolation levels

Locking works by the SQL Server issues a shared lock on the tables/rows that it reads, blocking other transactions from updating the data. The same goes for updates, which are issued an exclusive lock, blocking other transactions from reading the data. Locking occurrs in different parts of the database, which I will not go into, but for instance tables, rows, indexes.

READ COMMITTED is using locking to ensure that it only reads committed data. And that no other transactions are updating the data while it's being read. This means that an update statement on rows that another transaction is currently selecting is being blocked. And, a select statement on rows that another transaction is updating is being blocked until that data is committed.

READ UNCOMMITTED ignores everything, and doesn't take any locks at all. This means that another transaction can perform an update on rows that the transaction is currently reading without being blocked, but it also have the effect that the transaction will receive data that the other transaction might not yet have committed.

SERIALIZABLE do the opposite, and locks everything. While READ COMMITTED releases the locks when it read the row, or when the statement is completed depending on the lock, SERIALIZABLE releases the locks once the transaction is committed. This means that another transaction that wants to update data that the transaction have read at least once, or another transaction wants to read data that the transaction has updated will be blocked until the transaction is committed.

Row versioned isolation levels

READ COMMITTED SNAPSHOT and SNAPSHOT ISOLATION uses row versioning instead. Row versioning means that every time a row is modified, the SQL Server stores a version of the row, ensuring that it will stay the same when read by another transaction.

SNAPSHOT ISOLATION works in the way that when a read is done on a table, it retrieves the last version of the rows that were committed at the time the transaction started. This provides a consistent snapshot of the data within the transaction. Data modified after the transaction started will not be visible, but at the same time the transaction will not be blocked. To protect against lost updates, if the transaction wants to update some rows that have been changed by another transaction after the transaction begun, it will terminate the transactions due to conflicting data.

READ COMMITTED SNAPSHOT works in the same way as snapshot isolation, but instead of keeping the snapshot the entire transaction, it only keeps it for the duration of the statement. This means that two read statements within one transaction could receive different results. However, when the transaction is doing an update, it is using the actual row instead of a previous row version and does not track if the row has been changed.

Since the SQL Server needs to keep each modified row that could be used by active transactions available, it stores them in tempdb. Due to this, tempdb needs to be large enough to incorporate all the changes. There is a background thread that checks which rows are still needed, and removes the rest, but if there is a long running transaction, it will prevent those rows to be removed. If tempdb will run out of space, no new row versions will be created, and any transactions attempting to access those (non-existing) rows will terminate.

Comments

READ COMMITTED SNAPSHOT is apart from READ UNCOMMITTED the most permissive when it comes to concurrency. It doesn't block any other DML statements, and keep a consistent view of the data within each statement.

• SNAPSHOT ISOLATION is permissive and maintains better consistency than READ COMMITTED SNAPSHOT, with the drawback that due to its conflict resolution may fail when doing updates. Multiple statements within the same transaction are guaranteed to be consistent with each other.

• READ COMMITTED is permissive when it comes to concurrent reads, not so much for updates. Due to locking, it is also slower than the row versioning levels.

• READ UNCOMMITTED is not recommended to use due to it will read dirty data, if that is no concern then it is best due to no locking and no need to keep old row versions.

• SERIALIZABLE is not recommended since it keeps locks on everything and therefore is slow and not good with concurrency.