SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Oliiii SSCertifiable Points: 5328 More actions · Answer 1

Switching from any isolation level to snapshot isolation level rollback any open transaction, so it's best to set it before opening a transaction.

Now, snapshot isolation can generate a lot of IO in tempdb, so it's best to set it only when realy needed and not too long before.

In your case it all depend on where it's needed and if there are transactions open or not.

Inoon Jiffry SSC Veteran Points: 297 More actions · Answer 2

Thanks Grasshopper for the reply.

but my question is is it advicerble to set the SET TRANSACTION ISOLATION LEVEL SNAPSHOT in the calling SP ?

Oliiii SSCertifiable Points: 5328 More actions · Answer 3

Oliiii (9/17/2010)
In your case it all depend on where it's needed and if there are transactions open or not.

In other word, it depend on the procedures...

If you only need snapshot isolation level in SP2 and no open transaction wrap the SP2 call in SP1, then no, don't set it in the calling procedure.