1

Question is:

Is there any way to avoid conflict in SNAPSHOT isolation when setting foreign key to table updated/inserted in another transaction, having in mind I never use delete?

I mean something like query OPTION or HINT or connection parameter, maybe database setting?

Example:

Field Customers.PriceDefinitionId has relation to PriceDefinitions.Id which is a clustered PK. I can't easy change PK/indexes and structure because it is third party database (in reality many of them on multiple machines) and my changes could be overwritten.

# Transaction 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION

UPDATE PriceDefinitions SET Active = 0 WHERE Id = 1;
# Transaction 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION

UPDATE Customers SET PriceDefinitionId = 1 WHERE Id = 1

Then, COMMIT transaction 1 gives in second:

Msg 3960, Level 16, State 2, Line 4
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.PriceDefinitions' directly or indirectly in database 'APP' 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.


Repeated question:

Is there any way to avoid conflict in SNAPSHOT isolation when setting foreign key to table updated/inserted in another transaction, having in mind I never use delete?

I mean something like query OPTION or HINT or connection parameter, maybe database setting?

Please avoid answers mentioning changing structure or indexes/PK, because of third party database (in reality many of them on multiple machines).


Question is similar to: Why am I getting “Snapshot isolation transaction aborted due to update conflict”?, but there is only an explanation to the nature of behaviour in case of DELETE, which I'm not using. Also i would like to have any solution to the problem, not only why is that.

4
  • Using Lock Hints with Snapshot Isolation Commented Nov 7, 2019 at 11:39
  • Executing UPDATE Customers WITH (UPDLOCK) SET PriceDefinitionId = 1 WHERE Id = 1 gives the same Snapshot isolation transaction aborted due to update conflict on transaction 1 COMMIT. Tried every 3 combinations (1 with UPDLOCK, 2, and both), all gives conflict error. Commented Nov 7, 2019 at 11:45
  • @SylwesterZarębski, I think you would need to also add UPDLOCK for the related table in the transaction, requiring a join. I haven't tried this but maybe a WITH (SERIALIZABLE) query hint instead.
    – Dan Guzman
    Commented Nov 7, 2019 at 12:30
  • Thanks Dan, but could You explain what do You mean by add UPDLOCK for the related table? I've tested UPDATE PriceDefinitions WITH (SERIALIZABLE) SET Active = 0 WHERE Id = 1 then UPDATE Customers WITH (SERIALIZABLE) SET PriceDefinitionId = 1 WHERE Id = 1 to no avail - the same error occurred. Commented Nov 7, 2019 at 13:47

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.