BUG: Insert/Update May Cause Client to Stop Responding

• Microsoft SQL Server, version 6.5

SYMPTOMS

An insert or an update operation to a table with clustered index may cause the computer to stop responding with waittype 0x0013 (exclusive resource lock) on SMP machine. This scenario usually involves at least two processes. Process A is being blocked (with waittype 0x8006 or 0x0006) by process B. Process B is not being blocked by anything, but has waittype 0x0013 in master..sysprocesses. Neither process can complete until one of them is killed.

CAUSE

On a multi-processor computer (SMPStat>1), when an insert or update is performed on a table with clustered index, it acquires SH_PAGE locks on some of the data pages involved as it scans through and checks on duplicated rows. If any page it needs was being EX_PAGE locked by other processes, this insert or update process would go to sleep until the SH_PAGE lock is available. If another process comes in and performs an insert, update, or delete (which in turn causes the index to split or shrink) that needs to acquire an exclusive resource lock on the same clustered index page, it is blocked by the first process because it is still holding the shared resource lock. This causes the computer to stop responding with waittype 0x0013 and other blocking symptoms.

WORKAROUND

To work around this problem, change the clustered index to a nonclustered index.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. A supported fix is now available, but has not been fully regression- tested and should be applied only to systems experiencing this specific problem. Unless you are severely impacted by this specific problem, Microsoft recommends that you wait for the next Service Pack that contains this fix. Contact Microsoft Technical Support for more information.