Error starting audit table due to update conflict

Question

I have several tables that are updated through my application that return a lot of data or it is difficult to request changes. To get around this problem, I created a table "LastUpdated" with one row and has a trigger for these complex tables, which simply sets GetDate () to the corresponding column in the LastUpdated table:

CREATE TRIGGER [dbo].[trg_ListItem_LastUpdated] ON [dbo].[tblListItem] FOR INSERT, UPDATE, DELETE AS UPDATE LastUpdated SET ListItems = GetDate() GO 

Thus, customers only need to query this table for the last updated value, and then decide whether they need to update their data from complex tables. Complex tables use snapshot isolation to prevent dirty reads.

On busy systems, approximately once a day, we get errors when writing or updating data in complex tables due to update conflicts in "LastUpdated". Since this happens in a statement executed by a trigger, a corrupted complex table does not save data. The following error is being recorded:

Snapshot isolation operation canceled due to update conflict. You cannot use snapshot isolation to access the table 'dbo.tblLastUpdated' directly or indirectly in the 'devDB' database to update, delete, or insert a row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update / delete statement.

What should I do in a trigger to prevent this failure? Can I use some kind of tooltip for the tooltip on the trigger to avoid this - or can I just ignore the errors in the trigger? Updating data in LastUpdated is not critical, but correctly saving data in complex tables.

This is probably something very simple that I forgot or do not know. As always, thanks for any info.

Answer 1

I would say that you should learn Change Tracking ( http://msdn.microsoft.com/en-gb/library/cc280462%28v=sql.100%29.aspx ), which is SQL Server’s lightweight built-in functionality, use to monitor the fact that the table has changed, as opposed to registering each individual change (which you can also do with Change Data Capture ). This requires snapshot isolation that you already use.

Since your trigger works in the parent transaction and your snapshot is out of date, your whole transaction should start again. If this is a complex workload, keeping this latest updated data this way will be expensive.

Answer 2

The short answer is don't do it! Creating updated transactions that depend on one separate shared row makes it prone to deadlocks and updates conflicts in general due to nasty troubles.

You can use views to determine the latest update, for example:

 SELECT t.name ,user_seeks ,user_scans ,user_lookups ,user_updates ,last_user_seek ,last_user_scan ,last_user_lookup ,last_user_update FROM sys.dm_db_index_usage_stats i JOIN sys.tables t ON (t.object_id = i.object_id) WHERE database_id = db_id() 

Or, if you really insist on a solution with LastUpdate , you can implement its update using a trigger in offline transactions . Although SQL Server does not support offline transactions, it can be performed using your favorite servers: How to create an offline transaction in SQL Server 2008

Answer 3

Need to change the scheme. If you need to save the update table, create a row for each table. This will greatly reduce your locks, because each table can update its own row and not compete for a single row in the table.

Lastupdated

 table_name (varchar(whatever)) pk modified_date (datetime) 

New trigger for tblListItem

 CREATE TRIGGER [dbo].[trg_ListItem_LastUpdated] ON [dbo].[tblListItem] FOR INSERT, UPDATE, DELETE AS UPDATE LastUpdated SET modified_date = GetDate() WHERE table_name = 'tblListItem' GO 

Another option that I use a lot has a modified_date column in each table. Then people know exactly which records to update / insert to synchronize with your data, and not to discard and reload everything in the table every time one record is changed or inserted.

Answer 4

Alternatively, you can update the log table inside the same transaction that you use to update complex tables inside your application and generally avoid running.

Update You can also select a new row instead of updating the same row in the LastUpdated table. You can then request the maximum timestamp for the last update. However, with this approach, your LastUpdated table will grow every day, which you will need to take care of if the transaction volume is high.