FIX: ORDER BY DESC Query After Update Brings Error 1203

• Microsoft SQL Server versions 6.5 Service Pack 2 and Service Pack 3

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

• 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.

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 version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

MORE INFORMATION

Steps to Reproduce 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 : kbusage SSrvGen kbbug6.50 kbbug6.50.sp2 kbbug6.50.sp3 kbfix6.50.SP5
Version :
Platform : WINDOWS
Issue type : kbbug