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

Article ID: 180101

Article Last Modified on 3/14/2006

Microsoft SQL Server 6.5 Standard Edition

This article was previously published under Q180101

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 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

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: kbbug kbfix KB180101