I have an ASP.NET MVC application using EF6 and SQL Server with up to 15 or so concurrent users. To ensure the consistency of data between different queries during each page request, I have everything enclosed in transactions (using System.Transactions.TransactionScope).
When I use IsolationLevel.ReadCommitted and .Serializable, I get deadlock errors like this:
Transaction (Process ID #) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.
When I use IsolationLevel.Snapshot, I get errors like this:
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.#' directly or indirectly in database '#' 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.
These errors are the least frequent when using IsolationLevel.Snapshot (one to three per day, roughly).
My understanding of the issue leads me to believe that the only ways to guarantee zero transaction failures is to either:
And I can't do 1 because some tasks and requests take a while to run, while other parts of the application need to stay reasonably responsive.
I'm inclined to think retry could be implemented by getting MVC to re-run the controller action, but I don't know how to go about doing such a thing.
I also don't know how to reproduce the errors that my users are causing. All I get right now are rather uninformative exception logs. I could set up EF to log all SQL that gets run on the DB, now that EF6 lets you do that, but I'm not sure how helpful that would actually be.
Any ideas?
Regardless of isolation level, there are two categories of locks. EXCLUSIVE for INSERT, DELETE, UPDATE and shared for SELECT.
You should try the limit the transaction time for EXCLUSIVE locks to a minimum. The default isolation level is READ COMMITTED. If you are writing/running reports against the OLTP systems, writers will block readers. You might get blocking issues.
In 2005, READ COMMITTED SNAPSHOT ISOLATION was introduced. For readers, the version store in tempdb is used to capture a snapshot of the data to satisfy the current query. A-lot less overhead than SNAPSHOT ISOLATION. In short readers are now not blocked by writers.
This should fix your blocking issues. You need to remove any table hints or isolation commands you currently have.
See article from Brent Ozar.
Will it fix your deadlock? Probably not.
Deadlocks are causes by two or more resources exclusive lock in opposite order.
Check out MSDN = way cooler pictures and mentions deadlock flags.
http://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx
Process 1
DEBIT BANK ACCOUNT
CREDIT VENDOR ACCOUNT
Process 2
CREDIT VENDOR ACCOUNT
DEBIT BANK ACCOUNT
In short, change the order of your DML to have consistent access to the tables. Turn on a trace flag to get the actual TSQL causing the issue.
Last but not least, check out application locks as a last resort. The can be used as MUTEX's on code that might be causing deadlocks.
http://www.sqlteam.com/article/application-locks-or-mutexes-in-sql-server-2005
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments