April 1, 2009 at 5:26 pm
When I call a stored procedure with autocommit on, SQL 2005 seems to ignore the SET TRANSACTION ISOLATION LEVEL statement within the stored procedure.
According to http://technet.microsoft.com/en-us/library/ms173763.aspx I should be able to set the transaction isolation level within the stored proc.
However, this does not work as documented if the stored procedure is executed outside an explicit transaction (i.e. with autocommit on).
For example, this does NOT work:
CREATE PROCEDURE spTest
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--We expect isolation level to be READ COMMITTED
...do something here...
END
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
EXEC spTest
--Broken: wrong isolation level is used. Level from
--connection is used, and level set by the stored proc
--is ignored.
When this is executed from SSMS, the isolation level for "do something here" is actually SNAPSHOT.
On the other hand, if the call to spText is in either an explicit or implicit transaction, things work as expected, and the isolation level for "do something here" is actually READ COMMITTED as intended.
For example:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
EXEC spTest
--works fine: correct isolation level is used as set by the
--stored proc
COMMIT TRAN
or
SET IMPLICIT_TRANSACTIONS ON
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
EXEC spTest
--works fine: correct isolation level is used as set by the
--stored proc
COMMIT TRAN
This is similar to a documented bug in SQL 6.5 here: http://support.microsoft.com/kb/171322
Has anyone else come across this? Anyone have any more information on this?
Sincerely,
David Rueter
April 2, 2009 at 2:19 am
David Rueter (4/1/2009)
For example, this does NOT work:CREATE PROCEDURE spTest
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--We expect isolation level to be READ COMMITTED
...do something here...
END
What do you mean by 'does not work'? Read committed is the default isolation level so, unless you have read committed snapshot enabled, that proc will run under read committed regardless of whether or not the statement's there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2009 at 12:08 pm
There's something strange going on. Maybe SSMS is the problem? Try this:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
CREATE TABLE tblTest (Test int)
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM [127.0.0.1].Northwind.dbo.tblTest
DROP TABLE tblTest
Execution fails with:
Msg 7420, Level 16, State 1, Line 3
Remote access is not supported for transaction isolation level "SNAPSHOT".
However, this works:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
CREATE TABLE tblTest (Test int)
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
SELECT * FROM [127.0.0.1,24849].Northwind.dbo.tblTest
DROP TABLE tblTest
{resultset is returned}
Note that you'll need a Linked Server, which you can create like this:
EXEC master.dbo.sp_addlinkedserver @server = N'127.0.0.1', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'127.0.0.1',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
I'm seeing this same kind of behavior if the statements are in a stored proc. I'm also seeing the same kind of behavor whether the stored proc is executed from SSMS or via ADO.
Any further clues are appreciated.
Sincerely,
David Rueter
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply