0

I have read committed snapshot isolation and allow isolation ON for my database. I'm still receiving a deadlock error. I'm pretty sure I know what is happening...

  1. First transaction gets a sequence number at the beginning of its transaction.
  2. Second one gets a later sequence number at the beginning of its transaction, but after the first transaction has already gotten its (second sequence number is more recent than first).
  3. Second transaction makes it to the update statement first. When it checks the row versioning it sees the record that precedes both transactions since the first one hasn't reached the update yet. It finds that the row's sequence number is in a committed state and moves on it's merry way.
  4. The first transaction takes it's turn and like the second transaction finds the same committed sequence number because it won't see the second one because it is newer than itself. When it tries to commit it finds that another transaction has already updated records that are trying to be committed and has to roll itself back.

Here is my question: Will this rollback appear as a deadlock in a trace?

Eric J. Price
  • 2,740
  • 1
  • 15
  • 21
  • If the error you get is a deadlock error then yes the deadlock graph will be traceable. RE: Your description the table structures and queries would be useful as the description is somewhat ambiguous but anyway sounds like something you can easily test out yourself by `BEGIN TRAN` in two separate SSMS windows then running the individual statements in the order of your theory. – Martin Smith Mar 12 '13 at 19:55
  • @MartinSmith I can prove that a deadlock is occurring; that is an absolute known. The question is whether my understanding of what is happening is correct or if different logic is causing the deadlock. The query isn't relevant because it happens in multiple different queries in multiple places in the stored procedure when multiple instances of the sproc are run in parallel. I'm just wondering if an update conflict will appear as a deadlock or if it will appear as something different. If it does appear as a deadlock then I have my explanation; if it does not it's back to the drawing board. – Eric J. Price Mar 12 '13 at 20:02

3 Answers3

2

In a comment attached to the original question you said: "I'm just wondering if an update conflict will appear as a deadlock or if it will appear as something different." I actually had exactly these types of concerns when I started looking into using snapshot isolation. Eventually I realized that there is significant difference between READ_COMMITTED_SNAPSHOT and isolation level SNAPSHOT.

The former uses row versioning for reads, but continues to use exclusive locking for writes. So, READ_COMMITTED_SNAPHOT is actually something in between pure pessimistic and pure optimistic concurrency control. Because it uses locks for writing, update conflicts are not possible, but deadlocks are. At least in SQL Server those deadlocks will be reported as deadlocks just as they are with 'normal' pessimistic locking.

The latter (isolation level SNAPSHOT) is pure optimistic concurrency control. Row versioning is used for both reads and writes. Deadlocks are not possible, but update conflicts are. The latter are reported as update conflicts and not as deadlocks.

Terry Coatta
  • 595
  • 4
  • 14
0

The snapshot transaction is rolled back, and it receives the following error message:

 Msg 3960, Level 16, State 4, Line 1
 Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot
 isolation to access table 'Test.TestTran' directly or indirectly in database 'TestDatabase' 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.
dinhokz
  • 895
  • 15
  • 36
-1

To prevent deadlock enable both

ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT

ALTER DATABASE [BD] SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE [BD] SET ALLOW_SNAPSHOT_ISOLATION ON;

here explain the differences http://technet.microsoft.com/en-us/sqlserver/gg545007.aspx

user2744708
  • 1