Microsoft KB Archive/171322

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 10:05, 21 July 2020 by X010 (talk | contribs) (Text replacement - """ to """)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base

Article ID: 171322

Article Last Modified on 10/3/2003


  • Microsoft SQL Server 6.5 Standard Edition

This article was previously published under Q171322

BUG #: 17076


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.


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.


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.


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.


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

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

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

   insert into testing values (123456, '123456')

On a separate connection, execute the following statements:

   use pubs

   set transaction isolation level read uncommitted

   exec testproc

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

   rollback tran

   drop table testing

   drop proc testproc

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

Keywords: kbbug kbusage KB171322