July 30, 2015 at 8:23 pm
Comments posted to this topic are about the item Global temporary table visibility
July 31, 2015 at 12:22 am
I got this one right, but I don't think the correct answer is listed. I could be wrong, but I think, in these circumstances, Query 3 would succeed both times. In the explanation after I answered, it mentioned how a global temp table is dropped after the user who created it disconnects and no other users have any active sessions against it. However, no where in the question did it say you disconnected...only that you closed Query 1.
July 31, 2015 at 12:34 am
This was removed by the editor as SPAM
July 31, 2015 at 1:55 am
I think the question is unclear. Q2 returns all rows created when Q1 is closed, not all rows as in the number specified in the while loop. I understand the logic behind the question, but answers 2 and 3 are ambiguos and it's not clear if Q1 has completed when we close it.
Though I enjoyed checking ...
July 31, 2015 at 2:04 am
I tested it. The qotd is right and also the answers are correct.
I learned something new.
I also tested placing a WITH(NOLOCK) on query 2 and 3, same results.
Query 1:
create table ##p(i char)
declare @i int = (select count(*) from ##p)
set nocount on
while @i < 3000000
begin
insert into ##p select 1
select @i +=1
end
Query 2
SELECT * FROM ##p
Query 3
SELECT * FROM ##p
July 31, 2015 at 7:43 am
Stewart "Arturius" Campbell (7/31/2015)
Closing Query 1 effectively disconnects the session.Query 2, being a SELECT *, while running, takes an exclusive table lock on the table, thus query 3 will be placed in a wait state until it is complete.
However, given that Query 1 has been closed, as soon as the session currently locking the table (viz. Query 2) is complete, the lock is released and the table is dropped. When Query 3 attempts to read, the table no longer exists, thus the failure
Good explanation!
July 31, 2015 at 8:17 am
Wow! I stand corrected. I should have tested this out before commenting. I could have sworn that I came across issues where a global temp table was still sitting in memory, long after the query had been closed and it had gone unused for a while. However, testing this out in my environment shows the QOTD was 100% correct.
Thanks for the lesson, Sergey.
July 31, 2015 at 8:58 am
A little more detail on what happens here:
1) Creating and populating the table. Nothing special to see in this bit.
2) Running the SELECT * from the Query2 window. Also nothing special to see. The SELECT * just takes out the appropriate locks (IS on the object, and S locks on the pages).
3) While Query2 is running, closing Query1, where the global temp table was created. The session associated with Query1 requests a Sch-M lock on the temp table so it can drop it. Since Sch-M will be blocked by any other locks on the object at all, it is blocked by the IS lock from the SELECT *. The SELECT * continues running normally.
4) While Query2 is still running, running the SELECT TOP 1 * in Query3. To compile, this attempts to take out a Sch-S lock on the global temp table. Since the lock manager is FIFO (unless the requested lock is compatible with all granted and and waiting requests, in which case that request can be immediately granted), Query3's request for a Sch-S lock has to wait for Query1's Sch-M lock request to be granted, which in turn has to wait for the SELECT * to release its IS lock.
5) Query2 finishes. It releases its IS lock, which allows Query1's Sch-M lock to be granted. When that lock is granted, Query1 drops the global temp table. After the table is dropped, the compile of the SELECT TOP 1 * in Query3 is no longer blocked, so it runs and finds that the table does not exist.
Cheers!
July 31, 2015 at 11:35 am
Jacob Wilkins (7/31/2015)
A little more detail on what happens here:1) Creating and populating the table. Nothing special to see in this bit.
2) Running the SELECT * from the Query2 window. Also nothing special to see. The SELECT * just takes out the appropriate locks (IS on the object, and S locks on the pages).
3) While Query2 is running, closing Query1, where the global temp table was created. The session associated with Query1 requests a Sch-M lock on the temp table so it can drop it. Since Sch-M will be blocked by any other locks on the object at all, it is blocked by the IS lock from the SELECT *. The SELECT * continues running normally.
4) While Query2 is still running, running the SELECT TOP 1 * in Query3. To compile, this attempts to take out a Sch-S lock on the global temp table. Since the lock manager is FIFO (unless the requested lock is compatible with all granted and and waiting requests, in which case that request can be immediately granted), Query3's request for a Sch-S lock has to wait for Query1's Sch-M lock request to be granted, which in turn has to wait for the SELECT * to release its IS lock.
5) Query2 finishes. It releases its IS lock, which allows Query1's Sch-M lock to be granted. When that lock is granted, Query1 drops the global temp table. After the table is dropped, the compile of the SELECT TOP 1 * in Query3 is no longer blocked, so it runs and finds that the table does not exist.
Cheers!
Good explanation. Thanks.
Igor Micev,My blog: www.igormicev.com
July 31, 2015 at 5:18 pm
Jacob Wilkins (7/31/2015)
3) While Query2 is running, closing Query1, where the global temp table was created. The session associated with Query1 requests a Sch-M lock on the temp table so it can drop it. Since Sch-M will be blocked by any other locks on the object at all, it is blocked by the IS lock from the SELECT *. The SELECT * continues running normally.4) While Query2 is still running, running the SELECT TOP 1 * in Query3. To compile, this attempts to take out a Sch-S lock on the global temp table. Since the lock manager is FIFO (unless the requested lock is compatible with all granted and and waiting requests, in which case that request can be immediately granted), Query3's request for a Sch-S lock has to wait for Query1's Sch-M lock request to be granted, which in turn has to wait for the SELECT * to release its IS lock.
5) Query2 finishes. It releases its IS lock, which allows Query1's Sch-M lock to be granted. When that lock is granted, Query1 drops the global temp table. After the table is dropped, the compile of the SELECT TOP 1 * in Query3 is no longer blocked, so it runs and finds that the table does not exist.
Jacob, I really appreciate the detail.
Perhaps you or someone else can help with why I'm having trouble duplicating the same results.
When I run it, the query 3 is granted the Sch-S lock and executes immediately. Once query 2 is finished, then the disconnect from query 1 is granted the Sch-M lock and proceeds to clean up the table.
I am using SQL 2008R2. I don't know if there have been changes in the lock scheduler that might make a difference.
Thanks,
Erik
July 31, 2015 at 5:32 pm
Interesting. I've run it on a 2008 R2 instance, and I get the expected behavior.
Are you definitely taking the actions in this order?
1)Create/populate ##tmptest in Query1
2)Run SELECT * FROM ##tmptest in Query2
3) While Query2 is running, close Query1
4) While Query2 is running, and after closing Query1, run SELECT TOP 1 * FROM ##tmptest in Query3
I can't think of any reason you would not see the expected behavior if you ran them in this order. Is there a chance you're running Query3 before disconnecting Query1?
Cheers!
July 31, 2015 at 5:51 pm
AlwaysLoadingData (7/31/2015)
I am using SQL 2008R2. I don't know if there have been changes in the lock scheduler that might make a difference.
There was indeed a behavior change between SQL 2008R2 and SQL 2012. 2008R2 does not block Sch-S, even if there is a Sch-M waiting.
I found a more complete explanation from Paul White here: http://dba.stackexchange.com/a/73848/34699.
It does however, make the answer dependent on which version of SQL you're on. 😉
Erik
July 31, 2015 at 6:06 pm
Even more interesting! I'll have to do a sanity check when I get home; what's your exact build of 2008 R2?
EDIT: I did my sanity check. I get the expected behavior on 2008 R2 SP3. Perhaps SP3 has the same correction. I'll have to see if I have an older 2008 R2 lying around somewhere.
August 2, 2015 at 9:29 am
This is definitely controversial: at the time of my post only 16 percent got it 'right.' I did not.
Nonetheless, it forced me to look into minutiae of the locking mechanism.
August 2, 2015 at 11:32 pm
That is 1 good tricky question, thanx!
Stewart "Arturius" Campbell (7/31/2015)
Closing Query 1 effectively disconnects the session.
Query 2, being a SELECT *, while running, takes an exclusive table lock on the table, thus query 3 will be placed in a wait state until it is complete.
However, given that Query 1 has been closed, as soon as the session currently locking the table (viz. Query 2) is complete, the lock is released and the table is dropped. When Query 3 attempts to read, the table no longer exists, thus the failure
Nice explanation Stewart, thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply