8

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?

8

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.

  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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