September 22, 2014 at 10:41 am
Just starting to use Snapshot Isolation. Allow Snapshot Isolation is turned on.
I created this proc
Create proc _test1 as
set transaction isolation level snapshot
-- read table
select * from _BMTEST
-- fool around for ten seconds
declare @a datetime = dateadd(s,10,getdate())
while @a > GETDATE() select @a = @a
-- read again
select * from _BMTEST
----
Now, if I run this proc, and after about 3-4 seconds I add a new row to _BMTEST, when the proc _test1 completes I see two result sets. The second result has the extra row.
But it shouldn't, should it? I thought the whole point of snapshot isolation was so that you were working with a consistent version of the data?
September 22, 2014 at 12:53 pm
Yes it should, because you have no explicit transactions.
In snapshot isolation, read queries will show the data version as of the time their transaction started. You have no explicit transaction, so every select is in its own individual transaction and hence shows the data as of the point that statement started.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2014 at 12:56 pm
Perfect - that was it - and it worked.
Thank you so much!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply