FIX: Undetected Deadlock with Checkpoint and SELECT INTO (163908)

SYMPTOMS

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

WORKAROUND

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

Run SELECT INTO temporary tables outside of manual transactions.
Create the temporary table and then use INSERT SELECT instead.
Remove the identity property.
Use trace flag 5302 to disable holding locks on system tables.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. 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 what the sp_who output looks like:

   Spid  Status      Command          Blocked
---------------------------------------------
      4  Sleeping    Dump Transaction 10
      0  Sleeping    Insert           0

Note that the spid 4 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.

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