0

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 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.

I read other issues mentioned here but mine is bit different, I am just trying to read some data with select statement but if those selected rows are updated outside with another transaction then I get above conflict error.

If I try to use LDPLOCK hint then it work but it slows it down. any solution for this?

Practical example below.

using (SqlConnection cn = new SqlConnection(connectionString))
                {
                    await cn.OpenAsync();
                    using (SqlTransaction tran = cn.BeginTransaction(System.Data.IsolationLevel.Snapshot))
                    {
            "select top 10 * from Employee where type = 1"
        }
        }

Now if I also perform update on employee table before the above transction commit is done, it throws the above error. Which I am not sure why, as it's only select statement. I read the microsoft blogs that this will create issue but could not find solution for it anywhere.

update employee set IsActive = 1 where type = 1
EzLo
  • 13,780
  • 10
  • 33
  • 38
girish kolte
  • 93
  • 2
  • 9
  • 1
    Your code doesn't actually execute any statements at all and won't compile, but I assume that's just bad paraphrasing. Your scenario is unclear, though: are you saying code that *only* performs a `SELECT` statement and nothing else in a transaction is getting an update conflict error? That doesn't gel with how snapshot isolation works. Can you reduce this to a situation others can reproduce? – Jeroen Mostert Jan 25 '19 at 11:00
  • Hi, I just added that code as sample, to replicate this issue, you need to perform update operation on table and also perform select within transaction scope on table that can be updated outside of this transaction scope – girish kolte Jan 26 '19 at 05:20
  • @girishkolte with what isolation level this query is run `update employee set IsActive = 1 where type = 1` ? – Sairam Cherupally Jan 27 '19 at 23:23
  • I tried the update employee query in both with and without snapshot isolation level but it doesn't work, If I use readcommitted on both placed then it works but I need it to work with snapshot isolation – girish kolte Jan 29 '19 at 04:07

1 Answers1

0

Turn out I can not make update on table that is being used in the snapshot isolation transaction. I split my table into two, moved the columns I need to update in second table and did not add any FK relationship. It sorted the problem.

girish kolte
  • 93
  • 2
  • 9