Determining row changes since last access in SQL Server

Question

We have a multiuser system with users saving to a central SQL Server 2005 database. We have encountered an issue where a user refreshes in changes from the db while another user saves new data. The way we are currently collecting the changes is we have a timestamp column on each table which is filled at every row insert/update. Another user will have a timestamp stored on the client which is the last time he did a pull from the database.

Each save is done in a transaction. The example we are dealing with is as follows:

• User1 starts a save, opening a transaction and inserting/modifying rows, changing their timestamps.
• User2 refreshes from the database before Users has committed the changes, somehow causing User2's timestamp to update.
• User 1 commits the transaction and all changes.
• User2 refreshes from the database again, however because his timestamp was updated previously, only the second half of the changes committed by User1 and pulled in causing errors and application crashes.

This is making us think that timestamps aren't necessarily the best method to use to determine database changes since the last access by the front-end system. What would a better solution be?

Further example

• User1 starts a save, opening a transaction and inserting/modifying rows and updating their timestamps.
• User2 starts another save, opens a transaction, inserts/modify rows updating their timestamps, and commits his transaction.
• User3 refreshes from the database and pulls down all the data that User2 committed, updating his LastRefreshTimestamp to the last timestamp created in the db by User2.
• User1 commits his transaction.
• User 3 refreshes again from the database but is pulling all changes between the end of User2's transaction and the end of User1's transaction based on its LastRefreshTimestamp, missing out on all the changes committed by User1's transaction before User2's transaction began.

Answer 1

The timestamp type suffers in some sql implementations when potentially multiple transactions occur during the same "second". For sql implementations with high resolution timers, for example, nanoseconds, it's unlikely to be a problem.

Instead, how about just keeping a transaction counter somewhere? Each time an update is done, increment it.

Answer 2

In an interactive system, timestamps with a resoltion of a second are usually adequate. Higher resolution timestamps lessen the risk of update failure. Failure often points to non-atomic updates, or cases involving multiple transactions against the same data for the same request.

Your problem looks like a non-atomic update, or an isolation level allowing non-committed changes to be seen. In any case the application should handle having the data updated without crashing. Normally, some sort of data updated by another user error should be presented.

The most recent timestamp on the data being considered for update, can be used with an application to ensure data isn't modified after being displayed but before being updated. Batch updates may cause problems with timestamps as they can be done extremely rapidly.

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

Answer 3

Add an update of the timestamps for any row created/updated just before the commit.

Anyway user-side checks do not replace server-side checks (and constraints), so this mechanism is only for the comfort of the users, not as the last mean of data validation...

Answer 4

Setting the time stamp on the client side to the date/time of the last pull, combined with time stamping during the transaction on the server side, is where your problem originates. Either do a last "update/affect time stamps of all affected records" as the last action in your transaction - though you may still run into the time stamp resolution problem, or change your pull logic to select records based on time stamp differences between client and server instead of comparing all record time stamps to a single "pull" date/time.