April 26, 2005 at 9:33 am
Hi all,
BOL and all the documentation I can find for setting the transaction isolation level shows that setting it sets it for the duration of the session or connection until it's reset to something else.
I've included a script that runs that makes it appear that either my understanding is incorrect or the documentation is wrong. This script shows that setting this makes the setting for the duration of a sproc only. so if I have sproca that calls sprocb and sprocb sets this, the setting is only good for the duration of sprocb, then goes back to sproca's setting when sprocb returns. This is actually the behavior I would want and expect, but it's not what the documentation seems to say. Is my understanding wrong, is the documentation wrong, or is there something else that I'm overlooking? Any comments would be appreciated. You can copy and paste this whole script, it drops the objects it creates, so you can run it anywhere.
---------------------
set nocount on
GO
create table setoption (so varchar(64), val varchar(64), run int null, shouldbe varchar(32) null)
GO
create proc setreaduncommitted as begin
set transaction isolation level read uncommitted
insert into setoption (so, val) exec ('dbcc useroptions')
update setoption set run = (select isnull(max(run), 0) + 1 from setoption) where run is null
end
GO
create proc setreadcommitted as begin
set transaction isolation level read committed
insert into setoption (so, val) exec ('dbcc useroptions')
update setoption set run = (select isnull(max(run), 0) + 1 from setoption) where run is null
end
GO
create proc nestedcommitted as begin
set transaction isolation level read committed
insert into setoption (so, val) exec ('dbcc useroptions')
update setoption set run = (select isnull(max(run), 0) + 1 from setoption) where run is null
exec setreaduncommitted
insert into setoption (so, val) exec ('dbcc useroptions')
update setoption set run = (select isnull(max(run), 0) + 1 from setoption) where run is null
end
GO
create proc nesteduncommitted as begin
set transaction isolation level read uncommitted
insert into setoption (so, val) exec ('dbcc useroptions')
update setoption set run = (select isnull(max(run), 0) + 1 from setoption) where run is null
exec setreadcommitted
insert into setoption (so, val) exec ('dbcc useroptions')
update setoption set run = (select isnull(max(run), 0) + 1 from setoption) where run is null
end
GO
set transaction isolation level read committed
insert into setoption (so, val) exec ('dbcc useroptions')
update setoption set run = 0 where run is null --0 should be read committed
exec setreaduncommitted --1 should be read uncommitted
insert into setoption (so, val) exec ('dbcc useroptions')
update setoption set run = 100 where run is null --100 should be read committed
exec setreadcommitted --101 should be read committed
insert into setoption (so, val) exec ('dbcc useroptions')
update setoption set run = 200 where run is null --200 should be read committed
exec setreaduncommitted --201 should be read uncommitted
insert into setoption (so, val) exec ('dbcc useroptions')
update setoption set run = 300 where run is null --300 should be read committed
exec setreadcommitted --301 should be read committed
set transaction isolation level read uncommitted
insert into setoption (so, val) exec ('dbcc useroptions')
update setoption set run = 400 where run is null --400 should be read uncommitted
exec setreaduncommitted --401 should be read uncommitted
insert into setoption (so, val) exec ('dbcc useroptions')
update setoption set run = 500 where run is null --500 should be read uncommitted
exec setreadcommitted --501 should be read committed
exec setreaduncommitted --502 should be read uncommitted
exec setreadcommitted --503 should be read committed
insert into setoption (so, val) exec ('dbcc useroptions')
update setoption set run = 600 where run is null --600 should be read uncommitted
exec nestedcommitted --601 and 603 are read committed
--and 602 is read uncommitted
exec nesteduncommitted --604 and 606 are read uncommitted
--and 605 is read committed
GO
UPDATE setoption
set shouldbe =
case run
when 0 then 'read committed'
when 1 then 'read uncommitted'
when 100 then 'read committed'
when 101 then 'read committed'
when 200 then 'read committed'
when 201 then 'read uncommitted'
when 300 then 'read committed'
when 301 then 'read committed'
when 400 then 'read uncommitted'
when 401 then 'read uncommitted'
when 500 then 'read uncommitted'
when 501 then 'read committed'
when 502 then 'read uncommitted'
when 503 then 'read committed'
when 600 then 'read uncommitted'
when 601 then 'read committed'
when 602 then 'read uncommitted'
when 603 then 'read committed'
when 604 then 'read uncommitted'
when 605 then 'read committed'
when 606 then 'read uncommitted'
end
GO
select count(*) as different from setoption where so = 'isolation level' and rtrim(val) <> rtrim(shouldbe)
GO
select * from setoption where so = 'isolation level'
GO
drop procedure setreadcommitted
drop procedure setreaduncommitted
drop procedure nestedcommitted
drop procedure nesteduncommitted
drop table setoption
GO
April 26, 2005 at 11:05 am
someone found this for me:
in BOL under Create Procedure:
"If the logic of the stored procedure is dependent on a particular setting, include a SET statement at the start of the procedure to ensure the proper setting. When a SET statement is executed from a stored procedure, the setting remains in effect only until the stored procedure completes. The setting is then restored to the value it had when the stored procedure was called. This allows individual clients to set the options wanted without affecting the logic of the stored procedure."
so that answers my question...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply