Microsoft KB Archive/176491

= FIX: ORDER BY DESC Query After Update Brings Error 1203 =

Article ID: 176491

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Service Pack 2
 * Microsoft SQL Server 6.5 Service Pack 3
 * Microsoft SQL Server 6.5 Service Pack 4

-



This article was previously published under Q176491



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 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.



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: kbbug kbfix kbusage KB176491

-

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

© Microsoft Corporation. All rights reserved.