FIX: Deadlock Loop Causes Stack Overflow Unhandled Exception

ID: Q195414

The information in this article applies to:

Microsoft SQL Server version 6.5

BUG #: 18354 (SQLBUG_65)

SYMPTOMS

A stored procedure may enter a loop with deadlock handling routines, and may cause a stack overflow unhandled exception error if both of the following conditions are true:

The stored procedure creates a temporary table within the context of a user-defined transaction.

-and-

The stored procedure is involved in a deadlock between two of the system tables in tempdb.

WORKAROUND

To work around this problem, do either of the following:

Within the relevant stored procedure code, remove the creation of the temporary table from inside the user-defined transaction.

-or-

Recode the stored procedure to avoid using a temporary table. It may be possible to use a predefined permanent table with the same columns as the original temporary table. By adding a smallint column to hold the @@SPID value for the current connection, you can achieve the same functionality as with the original temporary table.

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

If you have not enabled trace flags 1204 and 3605, all you will see in the error log is an "EXCEPTION_STACK_OVERFLOW" message (an example of this error message is provided at the end of this article). There may be no indication of deadlocking unless the client application raises an error to the user.

The deadlock that causes this problem is always between two or more system tables in tempdb. Normally the two tables involved are sysindexes and syscolumns. However, sometimes sysobjects may be involved as well.

If you have trace flags 1204 and 3605 enabled when the deadlock handling routines enter this loop, the server will display the same *** DEADLOCK DETECTED *** error many times in the error log within the space of a few seconds. The number of times the message will be displayed before the stack overflow will vary; Microsoft Product Support Services has observed it occurring as few as 16 times and as many as several thousand times. The message contents will be identical, except that the date/time stamp and the cputime both may increment as many more messages are being printed.

If you are running SQL Server on a multiprocessor computer, the SQL Server process may not be shut down, but will produce a large stack dump and leave many orphan locks in tempdb. In this case, you will be able to observe the orphan locking by logging in to SQL Server and issuing a "select * from syslocks" statement.

If you encounter this bug you may also notice infrequent occurrences of error 617:

Descriptor for object '%ld' in database '%d' not found in the hash table during attempt to unhash it

If the checkpoint process starts, you may encounter error 602:

Could not find row in Sysindexes for dbid '%d', object '%ld',index '%d'. Run DBCC CHECKTABLE on Sysindexes

Another message encountered during testing the scenario for this bug was:

WARNING: Pss found with open sdes; psid 26, psuid 1, pcurdb 2, sp 0x27327f8, objid 1

The following is a sample of the errors in a customer's error log after experiencing this problem (with trace flags 1204 and 3605 switched on):

98/08/20 16:58:17.54 spid39 *** DEADLOCK DETECTED with spid 67 *** spid 39 requesting UP_PAGE (waittype 0x8007), blocked by: EX_PAGE: spid 67, dbid 2, page 0x13d, table 0x3, indid 0 pcurcmd DEALLOCATE CURSOR(0x2c), input buffer: EA_MOM_TotalPremie_SP spid 67 waiting for EX_PAGE (waittype 0x8005), blocked by: EX_PAGE: spid 39, dbid 2, page 0x1a, table sysindexes, indid 0 pcurcmd CREATE TABLE(0xc6), input buffer: EA_MOM_TotalPremie_SP VICTIM: spid 39, pstat 0x0080 , cputime 82014
98/08/20 16:58:17.54 spid39 *** DEADLOCK DETECTED with spid 67 *** spid 39 requesting UP_PAGE (waittype 0x8007), blocked by: EX_PAGE: spid 67, dbid 2, page 0x13d, table 0x3, indid 0 pcurcmd DEALLOCATE CURSOR(0x2c), input buffer: EA_MOM_TotalPremie_SP spid 67 waiting for EX_PAGE (waittype 0x8005), blocked by: EX_PAGE: spid 39, dbid 2, page 0x1a, table sysindexes, indid 0 pcurcmd CREATE TABLE(0xc6), input buffer: EA_MOM_TotalPremie_SP VICTIM: spid 39, pstat 0x0080 , cputime 82014
98/08/20 16:58:17.54 spid39 *** DEADLOCK DETECTED with spid 67 *** spid 39 requesting UP_PAGE (waittype 0x8007), blocked by: EX_PAGE: spid 67, dbid 2, page 0x13d, table 0x3, indid 0 pcurcmd DEALLOCATE CURSOR(0x2c), input buffer: EA_MOM_TotalPremie_SP spid 67 waiting for EX_PAGE (waittype 0x8005), blocked by: EX_PAGE: spid 39, dbid 2, page 0x1a, table sysindexes, indid 0 pcurcmd CREATE TABLE(0xc6), input buffer: EA_MOM_TotalPremie_SP VICTIM: spid 39, pstat 0x0080 , cputime 82014
98/08/20 16:58:17.54 spid39 *** DEADLOCK DETECTED with spid 67 *** spid 39 requesting UP_PAGE (waittype 0x8007), blocked by: EX_PAGE: spid 67, dbid 2, page 0x13d, table 0x3, indid 0 pcurcmd DEALLOCATE CURSOR(0x2c), input buffer: EA_MOM_TotalPremie_SP spid 67 waiting for EX_PAGE (waittype 0x8005), blocked by: EX_PAGE: spid 39, dbid 2, page 0x1a, table sysindexes, indid 0 pcurcmd CREATE TABLE(0xc6), input buffer: EA_MOM_TotalPremie_SP VICTIM: spid 39, pstat 0x0080 , cputime 82014
98/08/20 16:58:17.54 spid39 *** DEADLOCK DETECTED with spid 67 *** spid 39 requesting UP_PAGE (waittype 0x8007), blocked by: EX_PAGE: spid 67, dbid 2, page 0x13d, table 0x3, indid 0 pcurcmd DEALLOCATE CURSOR(0x2c), input buffer: EA_MOM_TotalPremie_SP spid 67 waiting for EX_PAGE (waittype 0x8005), blocked by: EX_PAGE: spid 39, dbid 2, page 0x1a, table sysindexes, indid 0 pcurcmd CREATE TABLE(0xc6), input buffer: EA_MOM_TotalPremie_SP VICTIM: spid 39, pstat 0x0080 , cputime 82014
98/08/20 16:58:17.54 spid39 *** DEADLOCK DETECTED with spid 67 *** spid 39 requesting UP_PAGE (waittype 0x8007), blocked by: EX_PAGE: spid 67, dbid 2, page 0x13d, table 0x3, indid 0 pcurcmd DEALLOCATE CURSOR(0x2c), input buffer: EA_MOM_TotalPremie_SP spid 67 waiting for EX_PAGE (waittype 0x8005), blocked by: EX_PAGE: spid 39, dbid 2, page 0x1a, table sysindexes, indid 0 pcurcmd CREATE TABLE(0xc6), input buffer: EA_MOM_TotalPremie_SP VICTIM: spid 39, pstat 0x0080 , cputime 82029
98/08/20 16:58:17.54 spid39 *** DEADLOCK DETECTED with spid 67 *** spid 39 requesting UP_PAGE (waittype 0x8007), blocked by: EX_PAGE: spid 67, dbid 2, page 0x13d, table 0x3, indid 0 pcurcmd DEALLOCATE CURSOR(0x2c), input buffer: EA_MOM_TotalPremie_SP spid 67 waiting for EX_PAGE (waittype 0x8005), blocked by: EX_PAGE: spid 39, dbid 2, page 0x1a, table sysindexes, indid 0 pcurcmd CREATE TABLE(0xc6), input buffer: EA_MOM_TotalPremie_SP VICTIM: spid 39, pstat 0x0080 , cputime 82029
98/08/20 16:58:17.54 spid39 *** DEADLOCK DETECTED with spid 67 *** spid 39 requesting UP_PAGE (waittype 0x8007), blocked by:
98/08/20 16:58:17.54 spid39 EXCEPTION_STACK_OVERFLOW raised, attempting to create symptom dump
98/08/20 16:58:17.54 spid39 Initializing symptom dump and stack dump facilities

Additional query words: errorlog err msg st proc stproc sproc


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

Last Reviewed: May 4, 1999