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 experimenting with snapshot isolation level. Whenever I try to access a linked server, I get an error:

Remote access is not supported for transaction isolation level "SNAPSHOT"

I wasn't able to find anything meaningful in BOL or Google on this issue.

The limitation seems rather frustrating. Is there a workaround, other than caching all the remote data locally?

share|improve this question

migrated from stackoverflow.com Oct 30 '11 at 12:39

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 4 down vote accepted

If you dig into the internals of how snapshot isolation is implemented in SQL Server, the reason why remote access is not supported becomes clear.

When snapshot isolation is enabled, up to 14 bytes of versioning information are appended to each row that is modified. The additional bytes contain the transaction sequence number of the transaction that modified the row, along with a pointer to the versioned row in tempdb.

A transaction running at snapshot isolation will see the version of a row that existed at the point it started (that exists at the time the first statement is executed, rather than BEGIN TRAN). It is the transaction sequence number that is used to identify the correct version in the version store. As there is no correlation between the sequence numbers on your local and remote server, it's impossible for the remote server to retrieve the correct version.

There are undoubtedly ways a SQL Server to SQL Server connection could manage this first issue, should the SQL development team decide it was worth doing. But there is a second problem, how would you deal with removing versions from the version store?

The background process responsible for cleanup again uses the transaction sequence number to determine if a row version is still required. This requires nothing more than identifying the oldest running transaction sequence number and removing any versions that are older than this. With a remote connection... sounds ugly, which is why it isn't supported.

Could a different approach be taken, perhaps enforcing REPEATABLE READ or SERIALIZABLE on the remote connection? No, not without compromising consistency as the row at the remote server could not be locked at the time your local transaction started. You could not rely on the behaviour, compromising ACID compliance.

Row Versioning Resource Usage and SQL Server 2005 Row Versioning-Based Transaction Isolation contain most of the salient detail.

How best to workaround this will depend on what you're trying to achieve. If you can expand on your specific scenario we can explore the alternative approaches.

share|improve this answer
    
Thanks for the explanation. The remote data is unlikely to be modified, so just any isolation level will do in this case. I tried to specify hints like (readcommitted) or (nolock) for the remote table, but got the same error. –  VladV Oct 31 '11 at 7:13
    
If the remote data will not be modified and you can accept a lower isolation level, can you read prior to starting the local snapshot transaction? –  Mark Storey-Smith Oct 31 '11 at 8:17
    
Yes, surely I can read the remote data first and start snapshot transaction afterwards. I was hoping to avoid that, because it makes the code more complicated. –  VladV Oct 31 '11 at 9:19

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.