FIX: INSERTs into Temporary Tables with Identity May Cause an Undetected Deadlock

ID: Q180101

The information in this article applies to:
BUG #: 17762 (6.5)

SYMPTOMS

When you INSERT INTO temporary tables with identity columns while other clients are performing SELECT INTO on temporary tables, you may experience undetected deadlocks with the Checkpoint Handler in the tempdb database, and neither client is chosen as a deadlock victim.


WORKAROUND

To work around this problem, avoid creating temporary tables with identity columns.


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

You can detect this situation by using the sp_who stored procedure and looking at the spid, status, command, and blocked columns. The following is an example of how the sp_who output looks: 


   Spid  Status      Command          Blocked
      3  Sleeping    Dump Transaction 10
      0  Sleeping    Insert           0
Note that the spid 3 command changes from Checkpoint Handler to Dump Transaction when the database is set to Truncate Log on Checkpoint (which is always set for tempdb) and the Checkpoint Handler runs a checkpoint.

Additionally, if you run the sp_lock stored procedure, the blocking lock held by the client will be an exclusive page lock on syscolumns.

After this problem occurs, clients are unable to create temporary tables, and you can only stop the SQL Server with the SHUTDOWN WITH NOWAIT Transact- SQL statement.

Additional query words: block 


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

Last Reviewed: April 21, 1999