Detecting row changes since last accessed in SQL Server

Question

We have a multi-user system with saving users in the central database of SQL Server 2005. We encountered a problem when a user is updated with changes from db and another user saves new data. As we are now collecting the changes, we have a timestamp column in each table, which is filled with each row insertion / update. Another user will have a timestamp stored on the client that he last pulled from the database.

Each save is performed in a transaction. The example we have in mind is this:

• User1 starts saving, opens a transaction, and inserts / modifies rows, changing his timestamps.
• User2 is updated from the database before User1 has made the change, causing the User2 timestamp to be updated.
• User 1 commits the transaction and all changes.
• User2 is again updated from the database, however, due to the fact that its timestamp was updated earlier, only the second half of the changes made by user1 caused errors during application crashes.

This makes us think that timestamps are not necessarily the best method for determining database changes since the last access to the front-end system. What would be the best solution?

Further example

• User1 starts saving, opening a transaction, and inserting / changing rows and updating their timestamps.
• User2 starts another save, opens a transaction, inserts / changes OTHER rows, updating his timestamps, and completes the transaction.
• User3 is updated from the database and retrieves all the data that User2 has committed, updating its LastRefreshTimestamp to the last timestamp created in User2 db.
• User1 is making a transaction.
• User 3 is updated again from the database, but pulls out all the changes between the completion of the User2 transaction and the completion of the User1 transaction based on LastRefreshTimestamp, skipping all the changes made by the User1 transaction before the User2 transaction began.

Answer 1

An interesting problem, and I can’t think of a simple clean solution based on T-SQL, but this is exactly the type of synchronization task with which change tracking in SQL 2008 was created for ... http://msdn.microsoft.com/en -us / library / bb933875.aspx

Good very high level review of change tracking and data change in this blog / article: http://blogs.technet.com/b/josebda/archive/2009/03/24/sql-server-2008-change-tracking-ct- and-change-data-capture-cdc.aspx

And you can combine this with the Microsoft Sync Framework if your common goal is to keep client copies of the repository: http://msdn.microsoft.com/en-us/sync/bb887608

Answer 2

I think I understand your question:

Each client maintains a disconnected dataset and periodically updates its local copy of the dataset.

You use the SQL-92 timestamp (which is different from the SQL Server timestamp, one is the date-time, one is the binary version of the row) to track updates so you can find the delta in the dataset and update the clients.

This approach causes problems because your timestamps are calculated until the transaction is fully committed, and subsequent transactions calculate new timestamps, but can commit transactions to older transactions, and your "last timestamp" skips some or all of the records.

So what can be done?

The fact that this is such a “hard nut to crack” is a good sign that this is an atypical design template, although I assume that changing this model is out of the question.

Here are some solutions that might work.

• Create a “margin of error” in your updates. If your last update was 2011-08-06 23:14:05, subtract a few minutes (you need to find out what the error is) from this and get these updates. This will be your quick group help solution. If you want to refine this solution, use the SQL Server timestamp (an automatic binary version of the row), which calculates the checksum of all rows and stores it in your local dataset and compares the rows during the update.

• Lazy refreshing. Again, use the SQL Server timestamp (rowversion) to control row versions and check for changes before the user is allowed to edit if the timestamps do not match, and then update. When checking, use the NOWAIT hint to determine if the row is currently being edited. Typically, you repeat this check when saving to avoid conflict (http://www.mssqltips.com/tip.asp?tip=1501), this is a more typical approach.

Ultimately, you do not need to maintain the entire dataset on the client. SQL Server is very good at handling concurrency and multiple users. If you need to search on local data, it might be better to perform these searches on the server. If you have blocking issues, it’s best to focus on trying to shorten the duration of the transaction. All in all, this is a terrible idea for an open transaction that is awaiting user input. Your question indicates that this may happen, but I do not want to accept anything.

Also, ideas / options are getting worse / crazy (for example, you could call SQL instances (Express?) On clients and use replication to push changes and use those instances for local data storage. But replication latency can be a serious problem , and I'm sure this will cause more problems than it solves.)

There is another possibility

Perhaps I am mistaken in my interpretation of your question. You can calculate the update timestamp on the client, and then later save it on the server. If so, instead of passing explicit date-time to the server, go to GETDATE (). This will calculate the date and time on the fly.

If there is too much code in the refactor, you can also process it with a trigger:

A simple example:

CREATE TRIGGER Trig_MyTable_Update ON dbo.MyTable FOR INSERT, UPDATE AS IF @@ROWCOUNT=0 RETURN UPDATE U SET UpdateDT = GETDATE() FROM MyTable U INNER JOIN INSERTED I ON U.Id = I.Id 

Answer 3

I assume the timestamp is rowversion (8) binary. This increases for any old reason: if you do update-.. set col1 = col1.. for example

We found too many false positives that rely on timestamps.

Let User5 say it loads the data, but the User4 changes are then returned (after all, they are the end users ...), you will have 2 timestamp changes, but the same basic data.

A real system based on date and time, even with an accuracy of 1 second or less, ultimately fails at high load, but you have the same problem.

We solved it with

• do not use timestamps (they are incremented by dummy updates)
• using isolation REPEATABLE_READ
• each save also sends old values.
• comparing each row value to update

Thus, User3 loads one data set that changes on the server. When they are saved, any change in values ​​causes an error.

If you can disable UPDATE where the actual data has not changed so that the timestamp does not work, you will not need it.

You can add additional dummy update tracking columns if you want the custom “save” to be confirmed, even if it is not a “real” update .. but this changes the rowversion values

Therefore, our solution is higher ...

Answer 4

Add timestamp update for any line created / updated immediately before commit.

In any case, user-side checks do not replace server-side checks (and restrictions), so this mechanism is intended only for the convenience of users, and not as a last resort means of data verification ...

Answer 5

Setting a client-side time stamp on the date / time of the last pull, combined with time stamping during a server-side transaction, is the cause of your problem. Either make the last “update / influence of time stamps of all affected records” as the last action in your transaction, although you may still encounter the problem of resolving the timestamp or changing the traction logic to select records based on the differences between the timestamps between the client and server instead of comparing all recording timestamps with one date / time "date".

Answer 6

Look at the AFTER triggers http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/triggers.htm#CNCPT017 , the database trigger is a special type of stored procedure that is called automatically when a predefined event occurs for user example saves new data.

 USE database GO CREATE TRIGGER on_table_update_insert_delete ON table AFTER update, delete, insert AS DECLARE @string AS char(1) IF EXISTS(SELECT * FROM inserted) IF EXISTS(SELECT * FROM deleted) SET @string = 'U'; ELSE SET @string = 'I'; ELSE SET @string = 'D' INSERT INTO event_logs(username, event_type, event_time, table_name) SELECT SYSTEM_USER @string, getdate(), 'Table' GO 

To enable the trigger, you will need to enter this code

 USE database GO ENABLE TRIGGER on_table_update_insert_delete ON DATABASE GO 

Thanks:)

Answer 7

In an interactive system, timestamps with a resolution of a second usually correspond. Higher resolution timestamps reduce the risk of update failure. A failure often indicates non-atomic updates or cases involving multiple transactions on the same data for the same request.

Your problem looks like a non-atomic update or isolation level, allowing you to see uncommitted changes. In any case, the application should process the data update without fail. Usually some data should be presented updated with another user error.

The most recent timestamp of the data considered for updating can be used with the application to ensure that the data will not be changed after the display, but before the update. Batch updates can cause timestamps because they can be completed very quickly.

Transaction counters can be used, but the counter for each record needs to be tracked.

Answer 8

The timestamp type suffers in some sql implementations when potentially multiple transactions occur during the same “second”. For sql implementations with high resolution timers, such as nanoseconds, this is unlikely to be a problem.

Instead, how about storing a transaction counter somewhere? Each time an update is performed, increase it.