SQL Server allow snapshot isolation

Question

two snapshot isolation transaction , and doing the update on the same records , will the first transaction blocking the 2nd transaction ?

Answer 1

Data modifications against disk-based tables acquire locks regardless of transaction isolation level. So a second session attempting to update rows modified by the first session will be blocked until the first session performs a commit or rollback.

Answer 2

Hi sakuraime,
Please refer to this article:

The first one completed normally, but look at what happened to the second one! The full text of the error message is:
Msg 3960, Level 16, State 5, Line 7
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘Orders. Orders’ directly or indirectly in database ‘MyOrders’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement

The most important part is the first sentence. “Transaction aborted due to update conflict.” The problem is that both queries tried to update the same row. When trying to commit the version of the row in the second update, SQL Server noticed that the first transaction was also trying to update the same row. Since there is no way for the database engine to know who should win (that is actually a business decision) it had to kill one transaction.

This is similar in some ways to a deadlock that can occur under pessimistic, transactional isolation. With transactional isolation, a deadlock is detected when two or more transactions have a lock conflict since they want to acquire one or more incompatible locks. Using row versioning, you can get an update conflict, as I did. The difference is that a lock conflict happens before the transaction is committed, whereas an update conflict happens during the commit, as the database engine checks the rows in the version store against the committed rows and finds a mismatch.

Best Regards,
Amelia