BUG: Err 1203 w/ SELECT ORDER BY DESC After an UPDATE in a Tran

Last reviewed: November 11, 1997
Article ID: Q176491
The information in this article applies to:
  • Microsoft SQL Server, versions 6.5 Service Pack 2 and Service Pack 3
BUG #: 17495 (6.5)

SYMPTOMS

If you run an ORDER BY DESC query after an UPDATE on the same table in a transaction, you may receive the following error message:

   Caller of lock manager is incorrectly trying to unlock
   an unlocked object. spid=%d locktype=%d dbid=%d lockid=%Id

This problem occurs if all of the following conditions are true:
  • The table has a clustered index.
  • The table has a nonclustered index on the WHERE clause condition of the UPDATE statement.
  • The UPDATE and SELECT statements are issued in a transaction.
  • There is an ORDER BY DESC clause in the SELECT statement.

This is a regression starting with SQL Server 6.5 Service Pack 2.

WORKAROUND

To work around this problem, do any one of the following:

  • Change the clustered index to a nonclustered index.
  • Remove the DESC keyword from the ORDER BY clause.
  • Remove the nonclustered index from the column that is referenced in the WHERE clause of the update.
  • Remove the transaction surrounding the UPDATE and SELECT statements.

STATUS

Microsoft has confirmed this to be a problem in SQL Server versions 6.5 Service Pack 2 and Service Pack 3. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce the Problem

The following script will demonstrate the problem:

   drop table test
   go
   create table test (c1 smallint,c2 varchar(200),c3 char(250),c4 int)
   go

   /* Create sample data */
   declare @wert int
   select @wert = 1

   while @wert < 12
   begin
    insert test values
   (1,convert(varchar(200),@wert),convert(char(250),@wert),@wert)
    insert test values
   (0,convert(varchar(200),@wert+1),convert(char(250),@wert+1),@wert+1)
    select @wert = @wert + 2
   end
   go
   create unique clustered index CL_IND ON test (c1, c2)
   go
   create nonclustered index NCL_IND on test (c4)
   go


   BEGIN TRANSACTION
   update test set c3 = "X" where c4 = 9
   select * from test where c1 = 1 order by c1 DESC
   COMMIT TRANSACTION


Additional query words: sp sp2 sp3
Keywords : kbbug6.50.sp2 kbbug6.50.sp3 SSrvGen kbusage
Version : WINNT:6.5
Platform : WINDOWS
Issue type : kbbug
Solution Type : kbworkaround


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 11, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.