"
Only one of the isolation level options can be set at a time, and
it remains set for that connection until it is explicitly changed."
"With one exception,
you can switch from one isolation level to another at any time during a transaction."
"When you change a transaction from one isolation level to
another,
resources that are read after the change are protected
according to the rules of the new level. "
"If you issue SET TRANSACTION ISOLATION LEVEL
in a stored
procedure or trigger, when the object returns control the isolation
level is reset to the level in effect when the object was invoked."
What about nested transactions? Is the isolation level reset when one of those complete?
It seems that if a C# method in the middle of a transaction calls another C# method that starts a nested transaction on the same connection, the nested transaction could suddenly alter the transaction isolation level of the caller for the remainder of its operations after the method call.
I think there should be an option, at the very least in the .NET SqlTransaction class (if it's not already there), for the transaction isolation level to be restored automatically when the [nested] transaction completes. Even though nested transactions don't actually commit their changes from what I understand, it seems that their alterations to the transaction isolation level silently persist no matter what was happening before the nested transaction started. If such an option is not available or is not made available, then I will have to manually implement a save/restore transaction isolation level (using dbcc useroptions) before and after every transaction I use in C# to avoid surprises when using transactional methods in my database API that call each other.
Below is the workaround for setting it back. Notice that you can't set it back with dynamic SQL using the exec command, because it creates a new scope which resets the isolation level to whatever it was when it was called. The only way I can see to do it is to use TSQL conditional statements so the command executes in the same scope. Also, I thought about leaving out the condition to reset to 'snapshot', since you cannot switch back to that mode from another isolation level once things start happening (I think), but you probably want that error to be thrown, so your outermost transactions don't silently slip out of snapshot isolation before they finish! You can test this out in SQL Server Management Studio:
set transaction isolation level read committed; --reset original isolation level to any test value you wish (it will be restored)
create table #useroptions ([Set Option] varchar(max), Value sql_variant);
insert into #useroptions exec( 'dbcc useroptions' );
declare @iso_level sql_variant;
select @iso_level = Value from #useroptions where [Set Option] = 'isolation level'; --save current isolation level
drop table #useroptions;
set transaction isolation level serializable; --change isolation level
dbcc useroptions; --display changed isolation level to confirm that it was changed
--declare @restore_IL nvarchar(max) = 'set transaction isolation level ' + CAST(@iso_level as nvarchar(max));
--select 'attempting to reset isolation using exec: ' + @restore_IL;
--exec( @restore_IL ); --don't bother, executes in a context that undoes any isolation changes you try to make
IF @iso_level = 'read uncommitted' BEGIN set transaction isolation level read uncommitted; END
ELSE IF @iso_level = 'read committed' BEGIN set transaction isolation level read committed; END
ELSE IF @iso_level = 'repeatable read' BEGIN set transaction isolation level repeatable read; END
ELSE IF @iso_level = 'serializable' BEGIN set transaction isolation level serializable; END
ELSE IF @iso_level = 'snapshot' BEGIN set transaction isolation level snapshot; END
dbcc useroptions; --confirm that original isolation level was restored dynamically
Really though, I don't even know if that will work, because the same problem with 'exec' may apply to statements executed with SqlCommand. Seems to me that we have a serious design flaw somewhere...