FIX: Descending Index Scans May Block with High Transaction Isolation

• Microsoft SQL Server version 7.0

SYMPTOMS

A descending index scan with a SERIALIZABLE transaction isolation level or a HOLDLOCK hint can hold a lock that prevents inserts greater than the highest value current in the table for the index the scan is performed on.

WORKAROUND

To work around this problem, do not use descending index scans when using a SERIALIZABLE transaction isolation level or a HOLDLOCK hint.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 7.0. 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

The following example queries clarify this problem:

--query 1:

   create table t(x int primary key)
   insert into t values(0)
   insert into t values(2)
   insert into t values(3)
   insert into t values(4)
   insert into t values(6)
   go
   set transaction isolation level serializable
   begin transaction
   go
   select * from t where x < 3 order by x desc
   go 

--query 2: Run from another connection

   insert into t values(1) -- blocked as expected
   go 

--query 3: Run from another connection

   insert into t values(5) -- works
   go
   insert into t values(7) -- blocked until query 1 client releases locks
   go 

   spid   dbid   ObjId       IndId  Type Resource         Mode     Status
   ------ ------ ----------- ------ ---- ---------------- -------- ------

   7      5      1365579903  1      KEY  (ffffffffffff)   IS-S     GRANT 

Additional query words: sp_lock block serializable holdlock

Keywords          : kbbug7.00 kbSQLServ700bug kbSQLServ700sp1fix 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: June 7, 1999