locked
SQL Server deadlock issue RRS feed

  • Question

  • User-1525009598 posted

    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.

    Any idea how I can get around this, and allow the concurrent access to this table record between the two processes?

    Thanks in advance

    Thursday, September 22, 2016 6:23 PM

Answers