BUG: INSERTs into Temp Tables w/Identity May Cause Deadlock

 Last reviewed: January 30, 1998
Article ID: Q180101
The information in this article applies to:
  • Microsoft SQL Server, version 6.5
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. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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 : kbbug6.50 SSrvLock SSrvTran_SQL
Version : WINNT:6.5
Platform : winnt
Issue type : kbbug
Solution Type : kbworkaround

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: January 30, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.