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
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.