Вопрос по transactions, sql-server – Транзакция изоляции моментального снимка прервана из-за конфликта обновления

11

Error: User Rate Limit Exceeded

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

Error: User Rate Limit Exceeded

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.

Error: User Rate Limit Exceededdbo.CompaniesError: User Rate Limit Exceededdbo.Companies.

Error: User Rate Limit Exceededdbo.ChangesError: User Rate Limit Exceededdbo.CompaniesError: User Rate Limit Exceededdbo.CompaniesError: User Rate Limit Exceededdbo.CompaniesError: User Rate Limit Exceeded

UPDATE:

Error: User Rate Limit Exceeded

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

Error: User Rate Limit Exceeded

UPDATE dbo.Companies WHERE [Id] = @1 SET [Name] = @2;

Ваш Ответ

2   ответа
3

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
6

Error: User Rate Limit Exceededwill acquire update locksError: User Rate Limit Exceededeven if it doesn't modify it.

Error: User Rate Limit ExceededError: User Rate Limit Exceeded:

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.

Error: User Rate Limit Exceededadding indexes to the foreign keys

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded TN.
Error: User Rate Limit Exceeded TN.
Error: User Rate Limit Exceeded

Похожие вопросы