Global temporary table visibility

  • sergey.gigoyan

    Ten Centuries

    Points: 1195

    Comments posted to this topic are about the item Global temporary table visibility

  • bantrim

    Default port

    Points: 1484

    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.

  • This was removed by the editor as SPAM

  • steve.childs

    Valued Member

    Points: 57

    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 ...

  • Carlo Romagnano

    SSC-Insane

    Points: 21834

    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

  • RLilj33

    SSCrazy

    Points: 2146

    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!

  • bantrim

    Default port

    Points: 1484

    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.

  • Jacob Wilkins

    One Orange Chip

    Points: 27856

    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!

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    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

  • AlwaysLoadingData

    SSC Enthusiast

    Points: 143

    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

  • Jacob Wilkins

    One Orange Chip

    Points: 27856

    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!

  • AlwaysLoadingData

    SSC Enthusiast

    Points: 143

    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

  • Jacob Wilkins

    One Orange Chip

    Points: 27856

    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.

  • Revenant

    SSC-Forever

    Points: 42467

    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.

  • Hany Helmy

    SSChampion

    Points: 13435

    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.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply