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.