BUG: "SET TRANSACTION ISOLATION LEVEL" Ignored in Stored Proc

ID: Q171322

The information in this article applies to:

Microsoft SQL Server version 6.5

BUG #: 17076

SYMPTOMS

If you code "SET TRANSACTION ISOLATION LEVEL <option>" within a stored procedure, the statement is ignored during the execution of that stored procedure. The isolation level in effect for the connection that first executes the stored procedure will be used during all subsequent executions of that procedure plan, even if a subsequent connection is using a different isolation level when it calls the relevant stored procedure.

Also, the connection that issues the CREATE PROCEDURE statement will have its transaction isolation level set to whatever <option> is coded for the "SET TRANSACTION ISOLATION LEVEL <option>" statement within the procedure.

CAUSE

The SET TRANSACTION ISOLATION LEVEL <option> that is contained in the CREATE PROCEDURE statement is executed as if it were coded outside of the procedure. However, the CREATE PROCEDURE statement executes without indicating any problem, (unless the SET statement is the only statement in the procedure, in which case you get error 124: "CREATE PROCEDURE contains no statement").

Furthermore, the transaction isolation level associated with the connection that first executes the relevant stored procedure is set during all subsequent executions of that procedures execution plan.

WORKAROUND

To work around this problem, do one of the following:

If the requirement is to set the transaction isolation level to READ UNCOMMITTED within your stored procedure, use the optimizer hint NOLOCK in your SELECT statements within the stored procedure.

Do not set the transaction isolation level anywhere in your code. The default transaction isolation level, READ COMMITTED, will then be used consistently within all stored procedures.

Use the WITH RECOMPILE option on the CREATE PROCEDURE statement. Then execute the SET TRANSACTION ISOLATION LEVEL <option> statement before calling the stored procedure.

Execute the SET TRANSACTION ISOLATION LEVEL <option> statement before calling the stored procedure. Then use the WITH RECOMPILE option when invoking the stored procedure. Be aware that this causes multiple copies of the stored procedure plan to be kept in the procedure cache.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

As a result of this issue, users will experience unpredictable locking behavior when using stored procedures, unless the SET TRANSACTION ISOLATION LEVEL statement is never used on connections that call stored procedures. Calling the same stored procedure twice in a row may result in different locking behavior if you pick up different plans from the procedure cache.

To demonstrate this issue, execute the following code within ISQL/w:


   use pubs
   go

   CREATE TABLE testing (f1 int NOT NULL , f2 varchar (24) NOT NULL )
   GO

   create procedure testproc as
   set transaction isolation level serializable
   select * from testing
   go

   BEGIN TRANSACTION
   insert into testing values (123456, '123456')
   go

On a separate connection, execute the following statements:


   use pubs
   go

   set transaction isolation level read uncommitted
   go

   exec testproc
   go

Note that the stored procedure returns uncommitted rows, despite the fact that the isolation level was set to serializable within the stored procedure code.

To tidy up after this demonstration, issue the following code from the first connection:


   use pubs
   go

   rollback tran
   go

   drop table testing
   go

   drop proc testproc
   go

Additional query words: Transact-SQL trans-sql t-sql sp proc


Keywords          : kbusage SSrvStProc SSrvTran_SQL kbbug6.50 
Version           : 6.5
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: April 16, 1999