patternsqlModerate
Deadlocks From Locks on Same Temporary Tables In Different Processes
Viewed 0 times
sametablestemporarydifferentdeadlocksprocessesfromlocks
Problem
I have found a deadlock that appears to show something I thought was impossible. There are two processes involved in the deadlock:
-
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
-
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__________________
- 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
- 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__________________
Solution
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.
It finally pays off to read SQL Server fix descriptions.
Context
StackExchange Database Administrators Q#30477, answer score: 15
Revisions (0)
No revisions yet.