0

I have a SQL Server 2012 database with Set Allow Snapshot Isolation = TRUE. Let's call it DBSnap.

I have another database with Set Allow Snapshot Isolation = FALSE. Let's call it DBRead.

In DBSnap, I have a created a view to a table in DBREAD (called the same name for ease of reference): 

Create View DBReadTable 
as 
    Select * 
    from DBRead.dbo.DBReadTable.

If I execute the following commands, they fail:

set transaction isolation level snapshot

Select * 
from DBRead.dbo.DBReadTable

Error:

Msg 3952, Level 16, State 1, Line 2
Snapshot isolation transaction failed accessing database 'DBRead' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.

However if I execute these commands, basically access a table in the DBRead database form a view in DDSnap:

set transaction isolation level snapshot

Select * 
from DBSnap.dbo.DBReadTable

It works.

What is actually happening in the second instance?

Is row versioning being done by DBSnap for the table in DBRead (i.e. does the DNSnap view have row version information)?

Or is a lock being placed on table DBRead?

Thank you,

Graeme Black

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Graeme Black
  • 1
  • 1

2 Answers2

0

I wasn't able to reproduce this on my end. Granted, I am running SQL Server 2016 instead of 2012. I tried creating the view as you suggested, and I received the same error as if I had queried the database directly.

Msg 3952, Level 16, State 1, Line 2
Snapshot isolation transaction failed accessing database 'DBRead' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.

The following MSDN quote seems to indicate that the behavior you described should not occur:

If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.

(from https://msdn.microsoft.com/en-us/library/ms173763(v=sql.110).aspx)

You might try starting with two fresh databases, enabling snapshot isolation in one of them, and then following the process you described above to see if you get the same results. If so, please save the SQL that you used and update your question. That way others may have better luck duplicating your situation.

Soukai
  • 463
  • 5
  • 8
0

My apologies, my posting is incorrect. I did not realize the view I was using to test had an embedded (NOLOCK) in it, thereby overriding the Snapshot.

Graeme Black
  • 1
  • 1
  • 1
    This did answer another related question about how to join between databases that support and do not support snapshot isolation. The transaction level isolation can be overridden by a table level hint, like (NOLOCK) or (READCOMMITTED) to allow the join to work. The portion of the query in the table with snapshot isolation will use row versioning (i.e. you can insert a row into that table without it being locked out), while the portion with the table hint will follow whatever isolation level specified. This is a good way to "mix" isolation levels within a join. – Graeme Black Sep 30 '16 at 17:35
  • I hadn't thought about this, it's a very interesting solution! I'm able to get this to work when querying the view using the table hint, but not when the hint is inside the view for some reason. I'm wondering if it may be dependent on whether or not SQL Server decides to follow the hint. Anyway, thanks for sharing! – Soukai Oct 01 '16 at 02:10