The information in this article applies to:
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.
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").
WORKAROUND
To work around this problem, do one of the following:
STATUSMicrosoft 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.
On a separate connection, execute the following statements:
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:
Additional query words: Transact-SQL trans-sql t-sql sp proc
Keywords : kbusage SSrvStProc SSrvTran_SQL kbbug6.50 |
Last Reviewed: April 16, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |