BUG: Stored Proc Can Fail w/ Msg 1203 Using READ UNCOMMITTED

• Microsoft SQL Server, version 6.0

SYMPTOMS

If a stored procedure is executed after setting the TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED, the procedure can fail with the following error:

   Error : 1203, Severity: 20, State: 2
   Caller of lock manager is incorrectly trying to unlock an unlocked
   object.
   spid=<n> locktype=4 dbid=<n>  lockid=<n>

CAUSE

This problem occurs if a stored procedure plan is reused in cache that was compiled when the user was not using READ UNCOMMITTED. If a new plan is used to execute the procedure with READ UNCOMMITTED, the problem does not occur.

WORKAROUND

Use the locking hint keyword NOLOCK in the SELECT statement instead of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

- OR -

Recompile the stored procedure so it does not use the existing plan in cache that was not executed with READ UNCOMMITTED. This can be accomplished in several different ways, including EXEC WITH RECOMPILE, drop/recreate the stored procedure, or restarting SQL Server.

Just retrying the operation will not avoid the problem unless the existing plan is flushed from cache. This can be determined by using DBCC MEMUSAGE.

STATUS

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

This problem does not occur in Microsoft SQL Server 6.5.