Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have found a deadlock that appears to show something I thought was impossible. There are two processes involved in the deadlock:

1. process8cf948 SPID 63

  • Performing an ALTER TABLE on temporary table #PB_Cost_Excp_Process_Invoices_Work.

  • Owns IX lock on table #PB_Cost_Excp_Process_Invoices_Work with object ID 455743580

2. process4cb3708 SPID 72

  • Performing in UPDATE on temporary table #PB_Cost_Excp_Process_Invoices_Work which is supposed to be its own unique copy of the table.

  • Owns Sch-M lock on #PB_Cost_Excp_Process_Invoices_Work with the same object ID 455743580!

This is supposed to be impossible. Am I missing something? Did a #Temporary table really get reused between these two SPIDs?

This is on SQL Server 2008 R2 Service Pack 2 with Cumulative Update 1 (version 10.50.4260).

The full unaltered deadlock trace is below. Note how the two processes are both operating on the same object ID with the same table name #PB_Cost_Excp_Process_Invoices_Work_SNIP_0000000D8519:

12/14/2012 13:46:03,spid23s,Unknown,waiter id=process8cf948 mode=X requestType=wait
12/14/2012 13:46:03,spid23s,Unknown,waiter-list
12/14/2012 13:46:03,spid23s,Unknown,owner id=process4cb3708 mode=Sch-M
12/14/2012 13:46:03,spid23s,Unknown,owner-list
12/14/2012 13:46:03,spid23s,Unknown,objectlock lockPartition=0 objid=455743580 subresource=FULL dbid=2 objectname=tempdb.dbo.#PB_Cost_Excp_Process_Invoices_Work_________________________________________________________________________________0000000D8519 id=lock371705d00 mode=Sch-M associatedObjectId=455743580
12/14/2012 13:46:03,spid23s,Unknown,waiter id=process4cb3708 mode=Sch-M requestType=wait
12/14/2012 13:46:03,spid23s,Unknown,waiter-list
12/14/2012 13:46:03,spid23s,Unknown,owner id=process8cf948 mode=IX
12/14/2012 13:46:03,spid23s,Unknown,owner-list
12/14/2012 13:46:03,spid23s,Unknown,objectlock lockPartition=3 objid=455743580 subresource=FULL dbid=2 objectname=tempdb.dbo.#PB_Cost_Excp_Process_Invoices_Work_________________________________________________________________________________0000000D8519 id=lock3139b4780 mode=IX associatedObjectId=455743580
12/14/2012 13:46:03,spid23s,Unknown,resource-list
12/14/2012 13:46:03,spid23s,Unknown,Proc [Database Id = 8 Object Id = 1857974987]
12/14/2012 13:46:03,spid23s,Unknown,inputbuf
12/14/2012 13:46:03,spid23s,Unknown,EXEC PB_ProcessExc_Costs_Submit_SP @SiteKey, @PWDate
12/14/2012 13:46:03,spid23s,Unknown,frame procname=PDICompany_218_01.dbo.DR_SubmitPaperwork_SP line=174 stmtstart=12912 stmtend=13018 sqlhandle=0x03000800cb72be6e500434018da000000100000000000000
12/14/2012 13:46:03,spid23s,Unknown,EXEC PB_ProcessExc_Costs_Create_SP

    -- Clean up work table
12/14/2012 13:46:03,spid23s,Unknown,frame procname=PDICompany_218_01.dbo.PB_ProcessExc_Costs_Submit_SP line=138 stmtstart=11890 stmtend=12012 sqlhandle=0x03000800428c1f1950f833018da000000100000000000000
12/14/2012 13:46:03,spid23s,Unknown,UPDATE #PB_Cost_Excp_Process_Invoices_Work
    SET PBCEPrcInv_RtlPkg_Item_Quantity = RtlPkg_Item_Quantity
    FROM #PB_Cost_Excp_Process_Invoices_Work
        INNER JOIN Item_Packages (NOLOCK)
            ON PBCEPrcInv_ItemPkg_Key = ItemPkg_Key
        INNER JOIN Retail_Packages (NOLOCK)
            ON ItemPkg_RtlPkg_Key = RtlPkg_Key

    -- Lookup pricebook cost
12/14/2012 13:46:03,spid23s,Unknown,frame procname=PDICompany_218_01.dbo.PB_ProcessExc_Costs_Create_SP line=25 stmtstart=2394 stmtend=3050 sqlhandle=0x030008003a082846321f46018da000000100000000000000
12/14/2012 13:46:03,spid23s,Unknown,executionStack
12/14/2012 13:46:03,spid23s,Unknown,process id=process8cf948 taskpriority=0 logused=0 waitresource=OBJECT: 2:455743580:0  waittime=3739 ownerId=707053534 transactionname=UPDATE lasttranstarted=2012-12-14T13:45:59.327 XDES=0x3c4502930 lockMode=X schedulerid=4 kpid=7276 status=suspended spid=72 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-12-14T13:45:58.337 lastbatchcompleted=2012-12-14T13:45:58.337 clientapp=PDI WCF Services - pdidb01-PDIMaster.cfg hostname=PDIWEB01 hostpid=2084 loginname=pdiuser isolationlevel=read committed (2) xactid=707053534 currentdb=8 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128568
12/14/2012 13:46:03,spid23s,Unknown,Proc [Database Id = 8 Object Id = 1857974987]
12/14/2012 13:46:03,spid23s,Unknown,inputbuf
12/14/2012 13:46:03,spid23s,Unknown,EXEC PB_ProcessExc_Costs_Submit_SP @SiteKey, @PWDate
12/14/2012 13:46:03,spid23s,Unknown,frame procname=PDICompany_218_01.dbo.DR_SubmitPaperwork_SP line=174 stmtstart=12912 stmtend=13018 sqlhandle=0x03000800cb72be6e500434018da000000100000000000000
12/14/2012 13:46:03,spid23s,Unknown,EXEC dbo.PB_ProcessExc_Costs_CreateInvoiceWorkTable_SP
12/14/2012 13:46:03,spid23s,Unknown,frame procname=PDICompany_218_01.dbo.PB_ProcessExc_Costs_Submit_SP line=58 stmtstart=5782 stmtend=5894 sqlhandle=0x03000800428c1f1950f833018da000000100000000000000
12/14/2012 13:46:03,spid23s,Unknown,ALTER TABLE #PB_Cost_Excp_Process_Invoices_Work DROP COLUMN PBCEPrcInv_Filler
12/14/2012 13:46:03,spid23s,Unknown,frame procname=PDICompany_218_01.dbo.PB_ProcessExc_Costs_CreateInvoiceWorkTable_SP line=50 stmtstart=5382 stmtend=5538 sqlhandle=0x0300080025d75a14ffff4701969f00000100000000000000
12/14/2012 13:46:03,spid23s,Unknown,executionStack
12/14/2012 13:46:03,spid23s,Unknown,process id=process4cb3708 taskpriority=0 logused=0 waitresource=OBJECT: 2:455743580:3  waittime=3739 ownerId=707052778 transactionname=ALTER TABLE lasttranstarted=2012-12-14T13:45:58.517 XDES=0x5f48bce80 lockMode=Sch-M schedulerid=6 kpid=7212 status=suspended spid=63 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2012-12-14T13:45:58.513 lastbatchcompleted=2012-12-14T13:45:58.513 clientapp=PDI WCF Services - pdidb01-PDIMaster.cfg hostname=PDIWEB01 hostpid=2084 loginname=pdiuser isolationlevel=read committed (2) xactid=707052778 currentdb=2 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128568
12/14/2012 13:46:03,spid23s,Unknown,process-list
12/14/2012 13:46:03,spid23s,Unknown,deadlock victim=process4cb3708
12/14/2012 13:46:03,spid23s,Unknown,deadlock-list

UPDATE

The machine in question shows 16 processors in Task Manager and Device Manager, so lock partitioning is enabled, and the two locks are on different lock partitions. I don't know if lock partitioning is a contributing cause here or not.

I also found this intriguing post on the CSS SQL Server Engineers blog.

UPDATE 2

The temporary tables are dropped at the end of every stored procedure. They are created with the pattern create #table, modify schema, insert, update, select, and then drop. There are multiple entry points to a common procedure that uses this temp #table, so we have a central proc that sets up the columns needed to call the common proc. Otherwise, we'd have to replicate the same #table definition in all the entry point procs.

The process is invoked frequently from multiple client applications. Some of the client applications call this process from multiple threads. Others run it one at a time. Think inventory/accounting software where the home office is processing data for thousands of stores in parallel while the stores also run the same process themselves. So if this is a rare issue when lock partitioning is enabled, it is not going to be so rare on our larger customer databases.

UPDATE 3 - 2012-12-19

Another customer is having the same issue on SQL Server 2012 build 11.0.2100. I did not see any mention of a fix for this issue in the cumulative update descriptions. Researching.

UPDATE 4 - 2013-02-13

Microsoft has released the fix for this bug in the following updates:

share|improve this question

migrated from stackoverflow.com Dec 14 '12 at 22:08

This question came from our site for professional and enthusiast programmers.

    
@AaronBertrand: No, we don't want #temp table caching. That would be bad enough on the same connection-- much less being reused between processes. I do not have an .xdl file, just the trace flag 1222 info. –  Paul Williams Dec 14 '12 at 21:37
    
We are explicitly dropping these #temp tables when done with them. –  Paul Williams Dec 14 '12 at 22:18
2  
I would still suggest capturing and posting the .xdl file somewhere so others can take a closer look - it will have much better detail. –  Aaron Bertrand Dec 14 '12 at 22:40
2  
I can confirm that lock partitioning is involved here. These posts has some detail about analyzing deadlocks involving and due to lock partitions. bit.ly/Ruzmym bit.ly/W7yuRK But I don't know why both the sessions posted the same ObjectID. –  Roji P Thomas Dec 15 '12 at 2:29
    
@SQLKiwi Thank you for looking at the issue! I didn't think about lock resource hashing. Given that it is on an object ID, I suspect that isn't the case, but I'm just guessing. The customer has been reporting deadlocks to us for several days. I have only 1 day worth of deadlock tracing, but I bet this is the deadlock they have been experiencing. We are opening a support ticket with Microsoft to help us figure it out. I'll update this question as I learn more. –  Paul Williams Dec 17 '12 at 14:13
show 1 more comment

2 Answers

up vote 11 down vote accepted

This exact issue was just announced on Deadlocks occur when you execute a stored procedure to alter a temporary table if lock partitioning is enabled in SQL Server 2008 R2. It is linked from Cumulative update package 4 for SQL Server 2008 R2 SP2.

It finally pays off to read SQL Server fix descriptions.

share|improve this answer
4  
Nice find! I will let our customer know. Thank you very much. –  Paul Williams Dec 18 '12 at 19:34
add comment

We had opened a case with Microsoft regarding this issue. Microsoft confirmed that this bug also affects SQL Server 2012. They are planning to release the fix in SQL Server 2012 Service Pack 2 (not released at the time I was writing this answer).

Until Microsoft releases this service pack, SQL Server 2012 users can bypass the issue by disabling lock partitioning via trace flag 1229.

Note that this issue applies only to machines that have 16 or more processors.

More information about lock partitioning

My thanks to Microsoft support! They were very prompt and helpful.

UPDATE

The bug is fixed in SQL Server 2012 Cumulative Update 2 For SQL Server 2012 SP 1.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.