Microsoft KB Archive/171322

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

Article ID: 171322

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q171322



BUG #: 17076



SYMPTOMS
If you code "SET TRANSACTION ISOLATION LEVEL " 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 is coded for the "SET TRANSACTION ISOLATION LEVEL " statement within the procedure.



CAUSE
The SET TRANSACTION ISOLATION LEVEL 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 statement before calling the stored procedure.
 * Execute the SET TRANSACTION ISOLATION LEVEL 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: kbbug kbusage KB171322

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.