14

Following statement:

INSERT INTO dbo.Changes([Content], [Date], [UserId], [CompanyId]) 
  VALUES (@1, @2, @3, @4);
SELECT @@identity;

gives me this SQL error 3960:

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Companies' directly or indirectly in database 'myDatabase' 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.

As far as I understood, from the error message, I should not update, delete, or insert to table dbo.Companies during the time another connection is modifying dbo.Companies.

But why it occurs when I was inserting a new row to another table dbo.Changes (which has foreign key to dbo.Companies) and I was not deleting the referenced row in dbo.Companies, but I was just updating row in dbo.Companies and not the primary key? This should work ok, shouldn't it? (Is it a bug in SQL Server?)

UPDATE:

Tables looks like following:

dbo.Changes([Id] int PK, [Content] nvarchar, 
  [Date] datetime, [UserId] int, [CompanyId] int -> dbo.Companies.[Id])
dbo.Companies([Id] int PK, [Name] nvarchar)

Second update is doing:

UPDATE dbo.Companies WHERE [Id] = @1 SET [Name] = @2;
TN.
  • 18,874
  • 30
  • 99
  • 157

2 Answers2

9

It appears SQL Server will acquire update locks on any record it has to read even if it doesn't modify it.

More info on this microsoft.public.sqlserver.server thread:

Without a supporting index on CustomerContactPerson, the statement

DELETE FROM ContactPerson WHERE ID = @ID;

Will require a "current" read of all the rows in CustomerContactPerson to ensure that there are no CustomerContactPerson rows that refer to the deleted ContactPerson row. With the index, the DELETE can determine that there are no related rows in CustomerContactPerson without reading the rows affected by the other transaction.

Additionally, in a snapshot transaction the pattern for reading data which you are going to turn around and update is to take an UPDLOCK when you read. This ensures that you are making your update on the basis of "current" data, not "consistent" (snapshot) data, and that when you issue the DML, it the data won't be locked, and you won't unwittingly overwrite another session's change.

The fix for us was adding indexes to the foreign keys

In your example, I suspect adding an index to Changes.CompanyId will help. I'm not sure if this is a real solution. Can the SQL Server optimizer choose not to use the index?

TN.
  • 18,874
  • 30
  • 99
  • 157
Brian Low
  • 11,605
  • 4
  • 58
  • 63
  • 1
    Thank you for hints, but it does not seem to help. – TN. Dec 15 '12 at 21:40
  • Do both statements (INSERT and UPDATE) refer to the same Customer? If so, we are out of luck as far as I know. – Brian Low Dec 16 '12 at 22:43
  • Yes, insert is using id of currently updating company. – TN. Dec 17 '12 at 09:38
  • 2
    Reading through (the long) dbaspot thread, it sounds like this is a limitation of SQL Server. The indexes in this answer will only help where the two statments involve different Customers. – Brian Low Dec 18 '12 at 17:05
  • Thanks, happened to have a table with dozen FKs noone cared to create indexes, after creating the missing indexes got no more errors like that so far. – jean Apr 20 '17 at 14:55
  • I have the same issue with my SP , and I have below statement in my SP , SELECT somecolumn FROM Sometable s WITH (UPDLOCK) Sometable is being updated in the same SP , isolation level is set as SNAPSHOT or database level and in C# transaction are using SNAPSHOT isolation level , NO isolation level specified in the SP , what to do ?? – Sagar May 24 '19 at 10:57
  • I've added indexes in an attempt to fix my issue using this case, hoping it would work, but did not appear to do anything to resolve the issue by itself – Captain Prinny Feb 27 '20 at 19:15
3

SQL Server can see an update to a dependent table which COULD modify the behavior of the insert ... seems fair to me as SQL can not guess what other logic might be dependent on the [name] column (triggers etc.)

if your applications implement deadlock retry logic you can modify them to treat error no 3960 the same as error no 1205 and automatically retry ...

Jack
  • 4,684
  • 2
  • 29
  • 22
  • You make a good point.... except, I still don't get why having a non clustered, non unique index on the related table's foreign key column is enough to make this not happen - i.e. is it not the case that it still could modify the behaviour of the insert, even with the index? – Kram May 09 '17 at 19:25
  • @Kram I suppose the index helps granularity. Engine knows a dependent table needs to be locked but without a index it can try to lock the entire table instead a single row – jean Sep 19 '17 at 17:50