I have been looking though some stored procedured on our system today when I saw the following structure:
(pseudocode indicative of actual SP, server is using the default isolation level):
CREATE PROCEDURE ... AS BEGIN IF NOT EXISTS(SELECT ...) BEGIN RETURN; END BEGIN TRAN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; DML... DML... DML... COMMIT END
I have not seen the transaction isolation level being declared inside a transaction boundary and was wondering what the effect of it was?
Is the isolation level restored once the transaction is committed (so if there were additional statements after the commit - what would their isolation level be)?
In this case, I assume there wouldn't be a difference if the isolation level were to be declared just before the transaction started. Is that the case?
Mostly, I was somewhat startled to see this, as I have only ever seen transaction isolation declared outside of a transaction, not within in and was wondering - why? What are the benefits of doing so.