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

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.

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

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:For more information, contact your primary support provider.

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: 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.