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 <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: kbbug kbusage KB171322
