FIX: Temp Table Stranded If Deadlock in Stored Proc. w/ Cursor (157570)

SYMPTOMS

If a stored procedure is selected as the victim in a deadlock situation, a temporary table created within the procedure may become stranded in tempdb. This situation will only occur if, in addition to the temporary table, a cursor is declared within the stored procedure, and the stored procedure then acts upon the temporary table.

Because the standard method for handling a deadlock is to resubmit the command that was terminated, problems may arise if that command attempts to re-create the temporary table upon resubmission.

Specifically, error message 2714 may be reported when the query is resubmitted, as follows:

SQL Server message 2714, state 1, severity 16:
There is already an object named '#temp' in the database.

Attempts to drop the temporary table prior to re-creating it will fail, and you will receive error message 3701:

SQL Server message 3701, state 1, severity 11:
Cannot drop the table '#temp', because it doesn't exist in the system
catalogs.

The existence of the temporary table can be confirmed by selecting from tempdb.sysobjects for that table.

This problem does not occur if a cursor is not used within the stored procedure. Thus, if a cursor is absent, the temporary table is correctly cleaned up from tempdb after a deadlock.

WORKAROUND

To work around this problem, try to close out the connection on which the deadlock occurred, prior to resubmitting the command. It is not necessary to cycle SQL Server in order to clean up an object left stranded in this manner, because that process (and any associated resources) appear to be released once the creating process is closed.

MORE INFORMATION

On SQL Server 6.0, the temporary table was not left stranded after a deadlock. However, an attempt to declare and open the cursor fails, with the following error message:

SQL Server message 16915, state 1, severity 16:
A cursor with the name 'cur1' already exists.

This error message is then followed by the following error: