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!