Transaction Isolation Level Scopes

Question

What are the scoping rules for transaction isolation levels in SQL Server 2005? I know what the different levels mean, but not how to properly apply them outside of a manually run script. I can't find a guide for practical use in production-quality code.

Obviously, the scope begins when you use a command like this:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

But where does it end? If I set the iso level in a stored procedure and then that proc calls another, does the nested proc inherit it? Even better, if I escalate the iso level inside the nested proc is it going to carry back out into the calling proc? Do transaction commands like BEGIN TRAN, ROLLBACK, and COMMIT make any differences?

When a stored proc is called by an application or an agent job do the isolation level changes persist in some way? Do I always have to revert to the default READ COMMITTED at the end of each proc?

I would test it in different situations but I don't know how to read what the current isolation level is set to.

Answer 1

Isolation level does not roll back with the transaction.

Isolation level stays current even if you call into procedures and functions.

Answer 2

From books on line

Only one of the TRANSACTION ISOLATION LEVEL options can be set at a time, and it remains set for that connection until it is explicitly changed. This becomes the default behavior unless an optimization option is specified at the table level in the FROM clause of the statement.

Answer 3

DBCC USEROPTIONS will display the current isolation level, along with all of the other SET options.

Answer 4

Run the following and see for yourself:

CREATE PROCEDURE dbo.KeepsIsolation
AS
BEGIN
PRINT 'Inside sproc that does not change isolation level';
DBCC USEROPTIONS;
END
GO

CREATE PROCEDURE dbo.ChangesIsolation
AS
BEGIN
PRINT 'Inside sproc that changes isolation level';
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DBCC USEROPTIONS;
END
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DBCC USEROPTIONS;
EXEC dbo.KeepsIsolation;
DBCC USEROPTIONS;
EXEC dbo.ChangesIsolation;
-- demonstrates that isolation level restored to REPEATABLE READ after exiting the procedure
    DBCC USEROPTIONS;

Answer 5

To get more information about Isolation levels in SQL Server including Locks and its impact on database in detail with examples, refer the below link: http://www.sqllion.com/2009/07/transaction-isolation-levels-in-sql-server/