Sql-server – Would using timestamps solve consistency issues with snapshot isolation

ms accesssnapshotsql server

I'm considering using Snapshot Isolation for some large queries being send from an Access 2007 frontend. These queries are occasionally causing ASYNC_NETWORK_IO waits, and some blocking as well. I'm working to try to solve this with application design, but since I can't control how the Jet engine loads data from the server, I haven't been able to completely eliminate the problem.

I know that one major concern with using snapshot isolation is the problem of updating dirty records, such as expressed in this blog post.

In my database, all of the tables have timestamp fields, which I would think would prevent the problem of updating dirty records. It might cause some update conflicts, but that seems like an acceptable outcome, while getting incorrect data is not. Am I right that having timestamps is a solution for potential data integrity problems, or could I still run into trouble?

EDIT: Max Vernon asked for an example. In the article I linked to, Craig Freedman uses this as an example:

We can demonstrate this outcome using SQL Server. Note that snapshot
isolation is only available in SQL Server 2005 and must be explicitly
enabled on your database:

alter database database_name set allow_snapshot_isolation on

Begin by creating a simple table with two rows representing two
marbles:

create table marbles (id int primary key, color char(5)) insert
marbles values(1, 'Black') insert marbles values(2, 'White')

Next, in session 1 begin a snaphot transaction:

set transaction isolation level snapshot begin tran update marbles set
color = 'White' where color = 'Black'

Now, before committing the changes, run the following in session 2:

set transaction isolation level snapshot begin tran update marbles set
color = 'Black' where color = 'White' commit tran

Finally, commit the transaction in session 1 and check the data in the
table:

commit tran select * from marbles

Here are the results:

id          color
1           White 
2           Black

This is an issue without using any sort of rowversioning. My question is, do timestamps solve this problem? Do they leave similar issues outstanding? Or does SQL Server's rowversioning when you enable Snapshot Isolation solve the problem and make timestamps unnecessary? I'm assuming it doesn't, based on Freedman's example.

Best Answer

I would suggest turning on READ_COMMITTED_SNAPSHOT at the database level - that will get rid of a large amount of the concurrency issues you are seeing with the Access front end.

Having TIMESTAMP fields in the tables that you are having issues with will help Access, but not with the ASYNC_NETWORK_IO waits. Those waits are showing up from the various combo boxes and list boxes you have in the front-end. Access does not request the entire dataset for those items until the combo-box or list-box etc actually needs all the data.

TIMESTAMP fields will be used by Access to determine if a record has been updated by some other process between the time Access fetched the record, and the time it (or the user) attempted to update the record. If there is no TIMESTAMP field, Access must inspect all the fields in the dataset prior to running the update statement to see if anything has changed.

Related Question