Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
1  
By switching to snapshot iso, you're potentially resolving concerns about dirty reads since your forcing a snapshot view of the data. I'm not understanding the concern. You'd have a much greater concern about dirty reads without snapshot iso. –  Eric Higgins Apr 11 '13 at 20:39
1  
Agreed, Eric. Notwithstanding my comments about records being held at the client end, of course. –  Max Vernon Apr 11 '13 at 20:40
1  
With snapshot iso users will be attempting to update whatever they see in the snapshot. The engine will ultimately handle conflicts as it always does. msdn.microsoft.com/en-us/library/tcbchxcb.aspx –  Eric Higgins Apr 11 '13 at 20:49
1  
Not here! I agree with Max that it can solve many problems. When you've got an app you know not much about that's deadlocking itself into oblivion, it's darn near a silver bullet. No one has wrote more about this that I know of than Kendra Little brentozar.com/isolation-levels-sql-server I've shared that Iso poster with peers many times. I'll add that I don't know what you mean by "overwritten data", because that's exactly what an update of any sort does, right? So I'd suggest digging into Kendra's material, and post back if you find you have more questions. –  Eric Higgins Apr 11 '13 at 21:34
1  
I agree Eric. RCSI cannot in and of itself lead to overwritten data. Only poorly thought-out design will do that. Kendra's doc and blog entry are excellent places to start! –  Max Vernon Apr 11 '13 at 21:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.