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.
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.
UPDATE Customers WITH (UPDLOCK) SET PriceDefinitionId = 1 WHERE Id = 1
gives the same Snapshot isolation transaction aborted due to update conflict on transaction 1COMMIT
. Tried every 3 combinations (1 with UPDLOCK, 2, and both), all gives conflict error.UPDLOCK
for the related table in the transaction, requiring a join. I haven't tried this but maybe aWITH (SERIALIZABLE)
query hint instead.UPDLOCK
for the related table? I've testedUPDATE PriceDefinitions WITH (SERIALIZABLE) SET Active = 0 WHERE Id = 1
thenUPDATE Customers WITH (SERIALIZABLE) SET PriceDefinitionId = 1 WHERE Id = 1
to no avail - the same error occurred.