restore transaction isolation level

Within a store procedure I would like to pump up the isolation level to
serializable just for one particular transaction and restore it back to the
previous level when it's done even if there is a fatal error, would someone
know how to do that? Thank you very much
Here is what I thought the implemenation/body of my store procedure should
be but I see problems
--STORE PROCEDURE --
--If I set transaction isolation level here before the transaction begins,
it will stay there through out the life of the connection, right? I don't
want that.
BEGIN TRANSACTION
-- If I set transaction isolation level here, it might not be
applicable for this transaction, right?
-- DOING SOME QUERYING AND UPDATING HERE
COMMIT TRANSACTION
-- If I restore the isolation level here, there are 2 issues? 1) How to read
it and restore it 2) If my update fails,this will never get executedZeng
You can set the isolation level back to READ COMMITTED:
SET TRANSACTION ISOLATION SERIALIZABLE
BEGIN TRANSACTION
... trap errors!
If errors,
ROLLBACK
else
COMMIT
SET TRANSACTION ISOLATION READ COMMITTED
RETURN
You can read the current isolation level with DBCC USEROPTIONS, but you only
see the isolation level if it's been changed once.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
the
> previous level when it's done even if there is a fatal error, would
someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
read
> it and restore it 2) If my update fails,this will never get executed
>|||That won't work well for me because:
1) Fatal error doesn't go through the trap
2) Read Committed is not always what my store proc might start out with.
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Zeng
> You can set the isolation level back to READ COMMITTED:
> SET TRANSACTION ISOLATION SERIALIZABLE
> BEGIN TRANSACTION
> ... trap errors!
> If errors,
> ROLLBACK
> else
> COMMIT
> SET TRANSACTION ISOLATION READ COMMITTED
> RETURN
> You can read the current isolation level with DBCC USEROPTIONS, but you
only
> see the isolation level if it's been changed once.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> the
> someone
should[vbcol=seagreen]
begins,[vbcol=seagreen]
don't[vbcol=seagreen]
> read
>|||Easy: simply add the statement With HoldLock to your Select query that
you want to be Serializable.
S. L.
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
> the
> previous level when it's done even if there is a fatal error, would
> someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
> read
> it and restore it 2) If my update fails,this will never get executed
>|||Zeng,
There's nothing you can do about the fatal errors that cancel an entire
batch. For fatal errors that simply abort the procedure, you can set up
wrapper code around the call to the proc that will restore the former
isolation level.
Here's one possible way:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @.SetValue varchar(100)
CREATE TABLE #useroptions (SetOption varchar(100), SetValue varchar(100))
INSERT #useroptions
EXEC ('DBCC USEROPTIONS')
SET @.SetValue = (SELECT SetValue FROM #useroptions WHERE SetOption =
'isolation level')
DROP TABLE #useroptions
SELECT @.SetValue
-- Simulates a call to a procedure, that changes the isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Now back in the calling code:
IF @.SetValue IS NULL OR @.SetValue = 'read committed'
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
IF @.SetValue = 'read uncommitted'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @.SetValue = 'serializable'
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
IF @.SetValue = 'repeatable read'
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> only
to[vbcol=seagreen]
to[vbcol=seagreen]
> should
PROCEDURE --[vbcol=seagreen]
> begins,
> don't
to[vbcol=seagreen]
>|||SET commands doesn't stick after the proc execution, so you only need to res
tore the isolation level
if you have further statements *inside* the procedure that you want to resto
re isolation level for.
Evidence:
USE tempdb
GO
CREATE PROC p
AS
DBCC USEROPTIONS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DBCC USEROPTIONS
GO
EXEC p
DBCC USEROPTIONS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zeng" <zzy@.nonospam.com> wrote in message news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...[v
bcol=seagreen]
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> only
> should
> begins,
> don't
>[/vbcol]