September 22, 2016 at 1:39 pm
Hello all -
I'm having a concurrent deadlock issue, two batch jobs running simultaneously. Here is the background
SQL Server 2008 R2.
During processing a stored proc updates a specific record in a table, thus the deadlock occurs between the two processes
I Updated the database to allow committed snapshot and snapshot isolation:
ALTER DATABASE xxxxx SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE xxxxx SET ALLOW_SNAPSHOT_ISOLATION ON;
Visual studio C# code around the stored proc call reads:
using (var tscope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.Snapshot }))
The error log reads:
An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.yyyyyy' directly or indirectly in database 'xxxxxx' 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 best I could come up with via internet research is the notion that I should set nonclustered indexes on the primary table, however the stored proc is not inserting anything into child tables, just doing a very basic update to the primary table.
Any idea how I can get around this, and allow the concurrent update to this table record between the two processes?
Thanks in advance
September 25, 2016 at 8:08 pm
scottlackey2001 (9/22/2016)
During processing a stored proc updates a specific record in a table, thus the deadlock occurs between the two processes
Deadlocks do not happen when a single record gets updated.
There must be more than 1 record affected by the update(s).
The best I could come up with via internet research is the notion that I should set nonclustered indexes on the primary table, however the stored proc is not inserting anything into child tables, just doing a very basic update to the primary table.
Adding indexes may be as well adding to the problem as resolving it.
It all depends on which indexes you're adding.
Any idea how I can get around this, and allow the concurrent update to this table record between the two processes?
To get some ideas we need to know the case.
Please post the UPDATE statement(s) and DDL for all the tables involved in it.
Including all the indexes existing on the tables.
_____________
Code for TallyGenerator
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply