August 1, 2011 at 10:13 am
I hope this is the right forum for this type of question.
I am trying to understand how isolations levels are processed when specifying the READ UNCOMMITTED at the beginning of a stored procedure that processes multiple SELECT statement TRANS and which calles other stored procedures.
1) Does the scope of the setting carry over to other stored procedures that are called within the stored procedure?
2) Do other BEGIN TRAN statements within the main stored procedure function under the READ UNCOMMITTED level?
Appreciate any help you can provide...
Thanks
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
August 1, 2011 at 5:12 pm
It sticks. Transaction isolation level persists at the session level.
Here is a quick PoC to demonstrate how it works:
-- set to traditional default before we get started
SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;
GO
CREATE PROC dbo.inner_proc
AS
BEGIN
SELECT OBJECT_NAME(@@PROCID) AS inside_inner,
CASE transaction_isolation_level
WHEN 1 THEN 'READ UNCOMMITTED'
WHEN 2 THEN 'READ COMMITTED'
WHEN 3 THEN 'REPEATABLE READ'
WHEN 4 THEN 'SERIALIZABLE'
WHEN 5 THEN 'SNAPSHOT'
END
FROM sys.dm_exec_sessions
WHERE session_id = @@spid
END
GO
CREATE PROC dbo.outer_proc
AS
BEGIN
SELECT OBJECT_NAME(@@PROCID) AS before_reset,
CASE transaction_isolation_level
WHEN 1 THEN 'READ UNCOMMITTED'
WHEN 2 THEN 'READ COMMITTED'
WHEN 3 THEN 'REPEATABLE READ'
WHEN 4 THEN 'SERIALIZABLE'
WHEN 5 THEN 'SNAPSHOT'
END
FROM sys.dm_exec_sessions
WHERE session_id = @@spid
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT OBJECT_NAME(@@PROCID) AS after_reset,
CASE transaction_isolation_level
WHEN 1 THEN 'READ UNCOMMITTED'
WHEN 2 THEN 'READ COMMITTED'
WHEN 3 THEN 'REPEATABLE READ'
WHEN 4 THEN 'SERIALIZABLE'
WHEN 5 THEN 'SNAPSHOT'
END
FROM sys.dm_exec_sessions
WHERE session_id = @@spid
EXEC inner_proc
SELECT OBJECT_NAME(@@PROCID) AS after_call_to_inner,
CASE transaction_isolation_level
WHEN 1 THEN 'READ UNCOMMITTED'
WHEN 2 THEN 'READ COMMITTED'
WHEN 3 THEN 'REPEATABLE READ'
WHEN 4 THEN 'SERIALIZABLE'
WHEN 5 THEN 'SNAPSHOT'
END
FROM sys.dm_exec_sessions
WHERE session_id = @@spid
END
GO
EXEC outer_proc
GO
DROP PROC inner_proc
DROP PROC outer_proc
GO
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 1, 2011 at 9:08 pm
Alright! Thank you!
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
August 2, 2011 at 9:25 am
You're welcome, HTH 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply