Conflicted transactions

Question

I have this error when my some transactions conflicted. How can i find what is the thansaction in conflict? (Without sql server profiler).

I am using snapshot isolation level

System.Data.SqlClient.SqlException: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Users' directly or indirectly in database 'IUMobileDbRelease' 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.
    System.Data.Entity.Internal.InternalContext.SaveChanges()

You use SQL Server Profiler. Or Extended Events, the replacement of profiler. Better yet, just *don't* use transactions. Use optimistic concurrency instead. That's the standard advice since the 1990s. Code that uses transactions *can't* scale and will *always* have to handle deadlocks and retry. Since the 1990s, applications use optimistic concurrency and rowversion columns to detect conflicting changes *without* using transactions — Panagiotis Kanavos, Mar 29 '17 at 07:36
The other option is to use the SNAPSHOT transaction isolation level instead of repeatable read. It's slower than optimistic concurrency but ensures you won't get any deadlocks unless two applications try to modify the same row — Panagiotis Kanavos, Mar 29 '17 at 07:38
which SQL server version is it? consider this: http://stackoverflow.com/a/33272566/1132334 — Cee McSharpface, Mar 29 '17 at 07:38
@dlatikay all current versions support this (ie SQL Server 2012 and later). It's still slower than optimistic concurrency — Panagiotis Kanavos, Mar 29 '17 at 07:40
@GLeBaTi first, without the *query* it's impossible to help. It's not the server that caused the deadlock, it's the query or queries, the number of records the affect and the duration of the transaction. A transaction should be used **only** to save the modified data and committed immediatelly. Keeping a transaction open for the duration of a request is a common mistake. There is a reason that optimistic concurrency is preferred. Do you use an explicit transaction or the implicit transaction used by `SaveChanges()`? — Panagiotis Kanavos, Mar 29 '17 at 07:47
I want find slow transaction from my many transactions (~100 different transactions). How can i do this? Can i do this by information from exception? — Glebka, Mar 29 '17 at 07:56
not a 100% duplicate, but part of the "how can I find... ?" seems to be answered here already: http://stackoverflow.com/a/20757916/1132334 — Cee McSharpface, Mar 29 '17 at 08:37

score 1 · Accepted Answer · answered Mar 29 '17 at 08:11

1

In EF6, you can enable query logging:

https://msdn.microsoft.com/en-us/library/dn469464(v=vs.113).aspx

For example:

context.Database.Log = Console.Write;

Correlate its output with the exception you get, to narrow it down to a statement.

answered Mar 29 '17 at 08:11

Cee McSharpface

8,493
3
36
77

Conflicted transactions

1 Answers1