April 15, 2012 at 1:36 pm
Hi,
We have a lot of procedures which internally uses the temp tables. But the procedures are not ended with dropping the temp tables.
Can this create deadlocks in tempdb?
Any suggestions? Per the definition of deadlock...if two processes are awaiting for the other resource it can create Deadlock.
However am looking for a bit further suggestion?
Also....per the architecture looks like table variables would be better since this works in memory. Can this too create deadlocks?
Thanks.
April 15, 2012 at 1:54 pm
Sourav-657741 (4/15/2012)
Hi,We have a lot of procedures which internally uses the temp tables. But the procedures are not ended with dropping the temp tables.
Can this create deadlocks in tempdb?
No. The tables will be dropped automatically as soon as the procedure they were created in ends
Also....per the architecture looks like table variables would be better since this works in memory.
Oh not that tired old myth again....
Table variables are not memory only. They are handled just like temp table are. I'm not going to find a link, there are many, many articles debunking that myth, including one on my blog.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2012 at 2:00 pm
Bu Let's say if the same procedure is under call by different reports in that case can that not have chances of deadlock? I think every process will call the same routine in a different session...so point in deadlock?
Also, will it be suggestible to use table variable in comparison to temp tables to have a better performance point of view? I think the existing temp tables too do not have any index associated to it....so will table variables be better here in this scenario?
Thanks.
April 15, 2012 at 2:04 pm
Sourav-657741 (4/15/2012)
Bu Let's say if the same procedure is under call by different reports in that case can that not have chances of deadlock? I think every process will call the same routine in a different session...so point in deadlock?
No. There is no chance of deadlock on temporary tables. Each person running the procedure has their own temp table. It's dropped when that procedure ends
Also, will it be suggestible to use table variable in comparison to temp tables to have a better performance point of view? I think the existing temp tables too do not have any index associated to it....so will table variables be better here in this scenario?
Highly unlikely. Table variables should not be used in any case where correct costing of the query operators is important for performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2012 at 5:08 pm
Assuming that your procedure creates a temp table using # (a local temp table) and NOT ## (a global temp table)
Run your procedure simultaneously by different users. Then execute the following:
SELECT name FROM tempdb.sys.tables AS t
WHERE t.name LIKE N'#%';
Notice that each local temp table in TEMPDB has a unique name, and is a unique table used by the procedure which created it.
For example:
name
#Temp__________________________________________000000000004
#Temp__________________________________________000000000008
August 2, 2016 at 6:25 am
Hello;
I know this thread is a little old, but I've seen a deadlock on temporary tables - this was due to Lock Partitioning. I think this issue was "introduced" since the original question - so I think all answers to date are correct at the time of posting.
I don't suppose one of the gurus could explain (as I don't know the answer). So; if the deadlock occurs on the temporary object is this purely a failure of the locking mechanism? Which I believe there is a SP fix for?
It still holds true that the actual data held within the temp. objects are still only available to the connection that creates the object? It's simply the locking mechanism that is getting "confused" and causing the deadlock?
Many thanks.
August 2, 2016 at 6:35 am
A deadlock can occur only on resources that multiple sessions can work with. Local temporary table can be used by one session only (the one that was created them), so there is no way that local temporary table can cause a deadlock.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 2, 2016 at 6:46 am
Adi Cohn-120898 (8/2/2016)
A deadlock can occur only on resources that multiple sessions can work with. Local temporary table can be used by one session only (the one that was created them), so there is no way that local temporary table can cause a deadlock.Adi
That's what I thought too Adi, then I found this article.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 2, 2016 at 6:57 am
ChrisM@Work (8/2/2016)
Adi Cohn-120898 (8/2/2016)
A deadlock can occur only on resources that multiple sessions can work with. Local temporary table can be used by one session only (the one that was created them), so there is no way that local temporary table can cause a deadlock.Adi
That's what I thought too Adi, then I found this article.
Very surprising. Obviously I was wrong in previous response.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 2, 2016 at 7:03 am
Yes - that was always my impression too. However, I have seen a deadlock occur on a temp resource:
<objectlock lockPartition="6" objid="-1602335561" subresource="FULL" dbid="6" objectname="tempdb.dbo.#LogTab______..."
Microsoft confirm this behaviour: https://support.microsoft.com/en-gb/kb/2776344
I've no doubt that the deadlock occurred on the temp. object; so my question still stands.
What I'm hoping someone can confirm and explain is (as I'm not clear even after reading the above KB), that this is an issue with the Lock Partitioning and not that actual use of the temp. resource.
Example: I have a stored procedure that Creates a temp table, populates it with data, adds an index, and the uses the temp table (via a join) in a SELECT statement. If I execute two different instances of this procedure at exactly the same time the data held within the two instances of the temp tables are still segregated, but I get a deadlock due to the issue with Lock Partitioning. The reason I get the deadlock is because one query is (lets say) doing the create and the other is applying the index (KB says the issue is due to an ALTER TABLE) and the Lock Partitioning manager is reacting as if this is the same resource. The data within the tables is still perfectly segregated.
i.e. It's an issue with Lock Partioning (the locking mechanism) rather than an issue with the management of temp. resources (tempDB)
Thanks
August 3, 2016 at 7:57 am
Hello;
Can anyone explain this issue in any more detail? Is it just an issue with Lock Manager or is it an issue with tempDB.
I cannot believe that there is an issue with tempDB, but then I never thought I'd see a deadlock on a temp table.... 🙂
August 3, 2016 at 8:46 am
Luminary1975 (8/3/2016)
Hello;Can anyone explain this issue in any more detail? Is it just an issue with Lock Manager or is it an issue with tempDB.
I cannot believe that there is an issue with tempDB, but then I never thought I'd see a deadlock on a temp table.... 🙂
The "CAUSE" section in the KB that you have the link for is pretty explicit. It was a bug that allowed two sessions for the same query to exist and conflict.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2016 at 3:24 pm
Hello;
Thanks for your reply, but I don't believe the cause in the article is clear.
This issue occurs because the two sessions that are used to execute the stored procedure try to obtain locks on the same temporary table object when lock partitioning is enabled.
The article also states that the issue is when an Alter Table operation occurs.
So when the KB article says the "stored procedure try to obtain locks" I assume this means Schema locks due to the reference to Alter Table. This would suggest that any data held within the tables within the two sessions is still separated, as there is no mention of issues with page and row locks. However, the article also says "the same temporary table object" which suggests that the data between the two sessions could also be "mixed" due to it being the same object; I thought there was never any risk of two temp. tables ever mixing data.
This is why I'm seeking clarification from someone. I think the article is not clear. I think what it's saying is there is an issue with taking out Schema locks due to a bug, but that the actual table data is still very much separated and there is no chance that one session running a proc. can affect the data, in a temp. table, in another session running the same proc.. I was just asking if anyone could answer that.
August 3, 2016 at 3:31 pm
Luminary1975 (8/3/2016)
Hello;Thanks for your reply, but I don't believe the cause in the article is clear.
This issue occurs because the two sessions that are used to execute the stored procedure try to obtain locks on the same temporary table object when lock partitioning is enabled.
The article also states that the issue is when an Alter Table operation occurs.
So when the KB article says the "stored procedure try to obtain locks" I assume this means Schema locks due to the reference to Alter Table. This would suggest that any data held within the tables within the two sessions is still separated, as there is no mention of issues with page and row locks. However, the article also says "the same temporary table object" which suggests that the data between the two sessions could also be "mixed" due to it being the same object; I thought there was never any risk of two temp. tables ever mixing data.
This is why I'm seeking clarification from someone. I think the article is not clear. I think what it's saying is there is an issue with taking out Schema locks due to a bug, but that the actual table data is still very much separated and there is no chance that one session running a proc. can affect the data, in a temp. table, in another session running the same proc.. I was just asking if anyone could answer that.
I'm thinking that the only people that will know the level of detail that you'd like all work for Microsoft.
Me... I'm happy with the explanation that two sessions were spawned and that a bug allowed a self conflict on the same Temp Table. I'm also happy that they said they fixed it and that I don't actually need to know the details any deeper because I don't have to write a work around because of the fix. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2016 at 4:24 pm
The reason I'm asking is I appear to have the correct cumulative fix applied and I just got a deadlock on a temp. table... :crying:
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply