FIX: 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 a SMP computer. 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. 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.

Additional query words: undetected deadlock distributed sleep kill stop client

Keywords : kbnetwork kbusage SSrvGen kbbug6.50 kbfix6.50.SP5
Version : winnt:6.5
Platform : winnt
Issue type : kbbug