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.
