locked
Would #tmp table cause deadlock? RRS feed

  • Question

  • Hi,

    I would like to know if #tmp table will cause deadlock?

    My case is like this, I have a very complex query. It can be split into multiple 5 parts of logic. Each part of logic could be used by difference query; or all 5 parts would be used in same query. In order to reuse them without copy and paste, I have created them into 5 stored proc. Each of them will INSERT INTO the result into #tmp table (connection oriented temp table) and the calling SP will "use" this #tmp table for further action.

    I was thinking to use variable table, but

    1. my result could be large

    2. We have nested query

    Recently, I hit deadlock problem randomly. I would like to know if this approach would cause problem

    Thank you

    Wednesday, August 10, 2011 6:36 AM

Answers

All replies

  • Yes you can still experience deadlocks when using this type of temporary table


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Thursday, August 11, 2011 12:39 AM
  • Hi

    Thank you reply. How should I solve this problem?

    Thursday, August 11, 2011 12:50 AM
  • Hi chanmy8,

    Based on your description, you can define multi CTEs (Common Table Expression) to avoid using stored procedures and temporary table if you are using SQL Server 2005 or later version.

    >> How should I solve this problem?
    Deadlock can happen sometimes in SQL Server. However, we can detect and end a deadlock in some manner. There are many ways to troubleshooting deadlock such as using trace flag, SQL Server Profiler. Please take a look at following articles on this topic:
    http://msdn.microsoft.com/en-us/library/ms178104.aspx
    http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx

    Also, please refer to this article for best practices in SQL Server which may helpful to you: http://www.codeproject.com/KB/database/sqldodont.aspx


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    • Proposed as answer by Papy Normand Thursday, August 11, 2011 2:44 PM
    • Marked as answer by Alex Feng (SQL) Wednesday, August 17, 2011 1:20 PM
    Thursday, August 11, 2011 7:33 AM
  • #tmp tables are not shared between connections (each connection has its own copy of the #tmp table), so you shouldn't have any deadlock by using them.

    However, you can still have some deadlocks when accessing the other tables.dddd

    Saturday, August 13, 2011 10:36 AM
  • #tmp tables are not shared between connections (each connection has its own copy of the #tmp table), so you shouldn't have any deadlock by using them.

    However, you can still have some deadlocks when accessing the other tables.dddd


    Just because this type of temporary table is not shared between connections doesn't mean that they won't experience deadlocks.  It is very easy to execute two SQL statements on one connection which could cause a deadlock.  It's also easy to cause a deadlock by using a cursor or CTE that reads and updates common tables.
    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Saturday, August 13, 2011 10:45 AM
  • Just because this type of temporary table is not shared between connections doesn't mean that they won't experience deadlocks.  It is very easy to execute two SQL statements on one connection which could cause a deadlock.  It's also easy to cause a deadlock by using a cursor or CTE that reads and updates common tables.

    I'm surprised to read that as I was under the impression that a connection cannot block itself.  I will try to find some examples of code where a connection is blocking itself but at this point, I don't remember ever seeing such a thing.  If you have some references about that, I'll be grateful.

    Saturday, August 13, 2011 6:45 PM