Question about ISOLATION LEVEL.

(SQL Server 2000, SP3a)
Hello, all!
I've got a question about setting the transaction isolation level within a stored
procedure.
In essence I want to have a particular piece of code in a SP use the SERIALIZABLE level.
However, I don't want this to "stick" for the rest of the connection as this BOL blurb
suggests:
<Quote>
Only one of the 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.
<Quote>
Does the specified level really persist even if it's been invoked from a SP? Is there any
way to get the "old" level if I change it?
Alternatively, I *think* I could put this in the FROM clause (can it be in the
<table_hint_limited> section of an UPDATE instead?):
update mt with (rowlock, readcommitted)
set MyField = 1
from MyTable as mt
where MyField = 2
Or does it have to be in the FROM clause like:
update mt with (rowlock)
set MyField = 1
from MyTable as mt with (readcommitted)
where MyField = 2
Or does it matter?
Thanks for any help you can provide! :-)
John PetersonJohn,
It will stay that way for the life of the connection if you don't change it.
But why don't you just issue a SET TRANSACTION ISOLATION LEVEL Read
Committed when done?
--
Andrew J. Kelly
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OkRbjfWfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello, all!
> I've got a question about setting the transaction isolation level within a
stored
> procedure.
> In essence I want to have a particular piece of code in a SP use the
SERIALIZABLE level.
> However, I don't want this to "stick" for the rest of the connection as
this BOL blurb
> suggests:
> <Quote>
> Only one of the 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.
> <Quote>
> Does the specified level really persist even if it's been invoked from a
SP? Is there any
> way to get the "old" level if I change it?
>
> Alternatively, I *think* I could put this in the FROM clause (can it be in
the
> <table_hint_limited> section of an UPDATE instead?):
> update mt with (rowlock, readcommitted)
> set MyField = 1
> from MyTable as mt
> where MyField = 2
> Or does it have to be in the FROM clause like:
> update mt with (rowlock)
> set MyField = 1
> from MyTable as mt with (readcommitted)
> where MyField = 2
> Or does it matter?
> Thanks for any help you can provide! :-)
> John Peterson
>|||From the SQL Server 2000 Books Online:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_create_4hk5.htm">
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.
</Excerpt>
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OkRbjfWfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello, all!
> I've got a question about setting the transaction isolation level
within a stored
> procedure.
> In essence I want to have a particular piece of code in a SP use the
SERIALIZABLE level.
> However, I don't want this to "stick" for the rest of the connection
as this BOL blurb
> suggests:
> <Quote>
> Only one of the 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.
> <Quote>
> Does the specified level really persist even if it's been invoked from
a SP? Is there any
> way to get the "old" level if I change it?
>
> Alternatively, I *think* I could put this in the FROM clause (can it
be in the
> <table_hint_limited> section of an UPDATE instead?):
> update mt with (rowlock, readcommitted)
> set MyField = 1
> from MyTable as mt
> where MyField = 2
> Or does it have to be in the FROM clause like:
> update mt with (rowlock)
> set MyField = 1
> from MyTable as mt with (readcommitted)
> where MyField = 2
> Or does it matter?
> Thanks for any help you can provide! :-)
> John Peterson
>|||Dan,
It's my understanding that this does not hold true for the Isolation level
and it is retained for the entire session or until it is changed again. I
guess I will have to test this to see...
--
Andrew J. Kelly
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OlGwpUXfDHA.3284@.tk2msftngp13.phx.gbl...
> From the SQL Server 2000 Books Online:
> <Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_create_4hk5.htm">
> 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.
> </Excerpt>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OkRbjfWfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello, all!
> >
> > I've got a question about setting the transaction isolation level
> within a stored
> > procedure.
> >
> > In essence I want to have a particular piece of code in a SP use the
> SERIALIZABLE level.
> > However, I don't want this to "stick" for the rest of the connection
> as this BOL blurb
> > suggests:
> >
> > <Quote>
> > Only one of the 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.
> > <Quote>
> >
> > Does the specified level really persist even if it's been invoked from
> a SP? Is there any
> > way to get the "old" level if I change it?
> >
> >
> > Alternatively, I *think* I could put this in the FROM clause (can it
> be in the
> > <table_hint_limited> section of an UPDATE instead?):
> >
> > update mt with (rowlock, readcommitted)
> > set MyField = 1
> > from MyTable as mt
> > where MyField = 2
> >
> > Or does it have to be in the FROM clause like:
> >
> > update mt with (rowlock)
> > set MyField = 1
> > from MyTable as mt with (readcommitted)
> > where MyField = 2
> >
> > Or does it matter?
> >
> > Thanks for any help you can provide! :-)
> >
> > John Peterson
> >
> >
>|||Andrew, I ran the follow script to verify the BOL statement and it
seemed to held true, at least as reported by DBCC USEROPTIONS.
CREATE PROC MyProc
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DBCC USEROPTIONS
GO
DBCC USEROPTIONS
EXEC MyProc
DBCC USEROPTIONS
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O3ziOFYfDHA.2352@.TK2MSFTNGP12.phx.gbl...
> Dan,
> It's my understanding that this does not hold true for the Isolation
level
> and it is retained for the entire session or until it is changed
again. I
> guess I will have to test this to see...
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OlGwpUXfDHA.3284@.tk2msftngp13.phx.gbl...
> > From the SQL Server 2000 Books Online:
> >
> > <Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_create_4hk5.htm">
> > 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.
> > </Excerpt>
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:OkRbjfWfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello, all!
> > >
> > > I've got a question about setting the transaction isolation level
> > within a stored
> > > procedure.
> > >
> > > In essence I want to have a particular piece of code in a SP use
the
> > SERIALIZABLE level.
> > > However, I don't want this to "stick" for the rest of the
connection
> > as this BOL blurb
> > > suggests:
> > >
> > > <Quote>
> > > Only one of the 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.
> > > <Quote>
> > >
> > > Does the specified level really persist even if it's been invoked
from
> > a SP? Is there any
> > > way to get the "old" level if I change it?
> > >
> > >
> > > Alternatively, I *think* I could put this in the FROM clause (can
it
> > be in the
> > > <table_hint_limited> section of an UPDATE instead?):
> > >
> > > update mt with (rowlock, readcommitted)
> > > set MyField = 1
> > > from MyTable as mt
> > > where MyField = 2
> > >
> > > Or does it have to be in the FROM clause like:
> > >
> > > update mt with (rowlock)
> > > set MyField = 1
> > > from MyTable as mt with (readcommitted)
> > > where MyField = 2
> > >
> > > Or does it matter?
> > >
> > > Thanks for any help you can provide! :-)
> > >
> > > John Peterson
> > >
> > >
> >
> >
>|||Thanks guys, that helps! I didn't see that blurb, Dan -- can you tell me what section of
BOL you found that?
As it turns out, I decided to do it at the table level as in my example. Curiously, I had
READCOMMITTED at *both* the UPDATE clause and the FROM clause. That turned out to be
rather "heavy" for some reason. When I changed it to be just on the UPDATE clause, it was
substantially faster.
Thanks again!
John Peterson
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OnDkGhYfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> Andrew, I ran the follow script to verify the BOL statement and it
> seemed to held true, at least as reported by DBCC USEROPTIONS.
> CREATE PROC MyProc
> AS
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> DBCC USEROPTIONS
> GO
> DBCC USEROPTIONS
> EXEC MyProc
> DBCC USEROPTIONS
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O3ziOFYfDHA.2352@.TK2MSFTNGP12.phx.gbl...
> > Dan,
> >
> > It's my understanding that this does not hold true for the Isolation
> level
> > and it is retained for the entire session or until it is changed
> again. I
> > guess I will have to test this to see...
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:OlGwpUXfDHA.3284@.tk2msftngp13.phx.gbl...
> > > From the SQL Server 2000 Books Online:
> > >
> > > <Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_create_4hk5.htm">
> > > 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.
> > > </Excerpt>
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > SQL FAQ links (courtesy Neil Pike):
> > >
> > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > http://www.sqlserverfaq.com
> > > http://www.mssqlserver.com/faq
> > > --
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:OkRbjfWfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> > > > (SQL Server 2000, SP3a)
> > > >
> > > > Hello, all!
> > > >
> > > > I've got a question about setting the transaction isolation level
> > > within a stored
> > > > procedure.
> > > >
> > > > In essence I want to have a particular piece of code in a SP use
> the
> > > SERIALIZABLE level.
> > > > However, I don't want this to "stick" for the rest of the
> connection
> > > as this BOL blurb
> > > > suggests:
> > > >
> > > > <Quote>
> > > > Only one of the 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.
> > > > <Quote>
> > > >
> > > > Does the specified level really persist even if it's been invoked
> from
> > > a SP? Is there any
> > > > way to get the "old" level if I change it?
> > > >
> > > >
> > > > Alternatively, I *think* I could put this in the FROM clause (can
> it
> > > be in the
> > > > <table_hint_limited> section of an UPDATE instead?):
> > > >
> > > > update mt with (rowlock, readcommitted)
> > > > set MyField = 1
> > > > from MyTable as mt
> > > > where MyField = 2
> > > >
> > > > Or does it have to be in the FROM clause like:
> > > >
> > > > update mt with (rowlock)
> > > > set MyField = 1
> > > > from MyTable as mt with (readcommitted)
> > > > where MyField = 2
> > > >
> > > > Or does it matter?
> > > >
> > > > Thanks for any help you can provide! :-)
> > > >
> > > > John Peterson
> > > >
> > > >
> > >
> > >
> >
> >
>|||> Dan -- can you tell me what section of
> BOL you found that?
The excerpt is from the CREATE PROCEDURE topic. You can jump there
directly from the Bools Online menu bar by clicking Go --> URL and
pasting in 'tsqlref.chm::/ts_create_4hk5.htm'.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23RXiFnYfDHA.1760@.TK2MSFTNGP09.phx.gbl...
> Thanks guys, that helps! I didn't see that blurb, Dan -- can you tell
me what section of
> BOL you found that?
> As it turns out, I decided to do it at the table level as in my
example. Curiously, I had
> READCOMMITTED at *both* the UPDATE clause and the FROM clause. That
turned out to be
> rather "heavy" for some reason. When I changed it to be just on the
UPDATE clause, it was
> substantially faster.
> Thanks again!
> John Peterson
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%238x$R8dfDHA.1200@.TK2MSFTNGP09.phx.gbl...
> Hmmm. Thanks Dan. I was pretty sure that I remember reading otherwise
> somewhere so that's good to know. Not that I ever set an isolation level
> without setting it back anyway.
The problem with setting it back, IMO, is that there's not a convenient mechanism to
record the *old* isolation level with which to restore. Granted, 99.99% of the time it's
likely the default of READ COMMITTED. But we've got some clients whose default is
essentially READ UNCOMMITTED.
> Andrew J. Kelly
> SQL Server MVP
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OnDkGhYfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> > Andrew, I ran the follow script to verify the BOL statement and it
> > seemed to held true, at least as reported by DBCC USEROPTIONS.
> >
> > CREATE PROC MyProc
> > AS
> > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> > DBCC USEROPTIONS
> > GO
> >
> > DBCC USEROPTIONS
> > EXEC MyProc
> > DBCC USEROPTIONS
> > GO
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:O3ziOFYfDHA.2352@.TK2MSFTNGP12.phx.gbl...
> > > Dan,
> > >
> > > It's my understanding that this does not hold true for the Isolation
> > level
> > > and it is retained for the entire session or until it is changed
> > again. I
> > > guess I will have to test this to see...
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > > news:OlGwpUXfDHA.3284@.tk2msftngp13.phx.gbl...
> > > > From the SQL Server 2000 Books Online:
> > > >
> > > > <Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_create_4hk5.htm">
> > > > 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.
> > > > </Excerpt>
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > Dan Guzman
> > > > SQL Server MVP
> > > >
> > > > --
> > > > SQL FAQ links (courtesy Neil Pike):
> > > >
> > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > http://www.sqlserverfaq.com
> > > > http://www.mssqlserver.com/faq
> > > > --
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:OkRbjfWfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> > > > > (SQL Server 2000, SP3a)
> > > > >
> > > > > Hello, all!
> > > > >
> > > > > I've got a question about setting the transaction isolation level
> > > > within a stored
> > > > > procedure.
> > > > >
> > > > > In essence I want to have a particular piece of code in a SP use
> > the
> > > > SERIALIZABLE level.
> > > > > However, I don't want this to "stick" for the rest of the
> > connection
> > > > as this BOL blurb
> > > > > suggests:
> > > > >
> > > > > <Quote>
> > > > > Only one of the 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.
> > > > > <Quote>
> > > > >
> > > > > Does the specified level really persist even if it's been invoked
> > from
> > > > a SP? Is there any
> > > > > way to get the "old" level if I change it?
> > > > >
> > > > >
> > > > > Alternatively, I *think* I could put this in the FROM clause (can
> > it
> > > > be in the
> > > > > <table_hint_limited> section of an UPDATE instead?):
> > > > >
> > > > > update mt with (rowlock, readcommitted)
> > > > > set MyField = 1
> > > > > from MyTable as mt
> > > > > where MyField = 2
> > > > >
> > > > > Or does it have to be in the FROM clause like:
> > > > >
> > > > > update mt with (rowlock)
> > > > > set MyField = 1
> > > > > from MyTable as mt with (readcommitted)
> > > > > where MyField = 2
> > > > >
> > > > > Or does it matter?
> > > > >
> > > > > Thanks for any help you can provide! :-)
> > > > >
> > > > > John Peterson
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>