Global temp tables can be accessed by one or more sessions. This important feature of SQL Server becomes more important than it was in my programming live since more applications I worked with and am currently working on have parallel processing involved heavily. Very often, amount of data generated from one sessioin are shared to many concurrent sessions. Global temp tables come and play. Creating a global temp table is simple.
create table ##temp(i int)
It will be released when
- It’s explicitly removed by any sessions
- The creator session closed and there is no other sessions referencing this session.
Nothing is hard to understand for the first point. A global temp table acts exactly as a permenant table in tempdb. You can retrieve object_id function to retrieve object id of the temp table created above
select OBJECT_ID('tempdb..##temp')
Now you open a second window and run
select * from ##temp
/*
i
-----------
(0 row(s) affected)
*/
Now you close the session that creates the temp table and then run the same query above.
Msg 208, Level 16, State 0, Line 1
Invalid object name '##temp'.
That’s what you get. Whenever the creator session closes, the temp table gets released event though it’s been referenced once from other sessions. Now let’s re-create the global temp table again and then insert a record from another session and keep the transaction open.
--Session1
create table ##temp(i int)
--Session 2
begin transaction
insert into ##temp values(1)
---Now let's close session 1 and then run this query from session 2
select * from ##temp
-- then you will see the ##temp table is not release
-- this is because the table is locked by session 2
-- SQL Server will not release the temp table event
-- the creator session closed.
-- not let's commit the transaction and then check the temp table
commit
select * from ##temp
/*
Msg 208, Level 16, State 0, Line 2
Invalid object name '##temp'.
*/
(Some blog content is in the comment of the script above.) So you can see once you have the transaction committed, the global temp table released immediately. This means that at the point when the transaction committed in Session 2, there is no more reference to ##temp anymore. Now let’s do another test. (the same, I will have my blog content in the code)
--Session ID = 51
create table ##temp(i int)
--Open another session, the session id = 57
begin transaction
insert into ##temp values(2)
--- now close session 51. go back to session 57 and run
select request_session_id, request_mode, request_status, resource_associated_entity_id from sys.dm_tran_locks
request_session_id request_mode request_status resource_associated_entity_id
------------------ ------------ -------------- -----------------------------
57 IX GRANT 2161727822326530048
57 IX GRANT 485576768
51 Sch-M WAIT 485576768
57 X GRANT 2161727822326530048
(4 row(s) affected)
Now we see the trick. Session 51 creates the temp table which can be accessed by other sessions. When Session 51 is closed from the application, the session will try to release the table. Line 15 indicates that a Sch-M lock is waiting to be granted for table removal. Because the table is locked by other session, that prevents the creator session removes the table. This is sort of conclusive that the creater session of global temp table release the table when session is closed. However, in order to release the table, an Schema Modification lock on the table is required. If any session locks the table schema, that will prevent the table to be removed. What would happen if I have another session trying to lock the table? Please see the code below
---open a new session, session 56
begin transaction
insert into ##temp values(1)
rollback
--- session 56 waits
--- go back to session 57
select request_session_id, request_mode, request_status, resource_associated_entity_id from sys.dm_tran_locks
/*
request_session_id request_mode request_status resource_associated_entity_id
------------------ ------------ -------------- -----------------------------
57 IX GRANT 2161727822326530048
57 IX GRANT 485576768
51 Sch-M WAIT 485576768
56 IX WAIT 485576768
57 X GRANT 2161727822326530048
(5 row(s) affected)
--- session 56 is waiting on object 485576768
-- now let's commit the transaction in session 57
---session 57
commit
-- now, will the global temp table released not not?
--go back to session 56
/*
Msg 208, Level 16, State 0, Line 2
Invalid object name '##temp'.
*/
This concluses that any sessions intend to acquire locks on a global temp table after the creator session of the temp table closed will FAIL. This is important, you must inform all sessions who needs the temp table to acquire a lock of it before you can close the creator session. Otherwise, you will lose the global temp table.
What options you may have to lock the table?
Option 1. Turn the access to this table to serializable mode. This will place a Shared lock on the temp table until transaction complete. Other sessioins can place Shared lock (holdlock) to the table as well, but they cannot modify the data
begin transaction
select * from ##temp with (holdlock)
Option 2. Insert zero record to the table. This will place IX lock on the table. IX lock is compatible with IX locks, other sessions can place IX lock by this way to the same global temp table. This approach also allows table to be modified from all sessions ( that has IX lock acquired before the creator session close).
begin transaction
insert into ##temp
select 1 where 1=0
Option 3. Use sp_getschemalock. This’s equivalent placing Shared lock on the table.
begin transaction
declare @1 int, @2 bigint
exec sp_getschemalock @1 output, @2 output, N'tempdb..##temp'
In this option, I used an undocumented procedure sp_getschemalock. This is not very useful for the scenario described above. However, if you are creating a global temp procedure and willing to share it to other sessions, how would you lock that procedure?
Although, this is not always– but always exceptions out there. If the creator session creates more than one global temp tables, when creator sessions is closed, these global temp tables are release one by one. As long as you lock the first created global temp table in an session, then rest of the global temp tables will be available anytime until the lock of the first table released. This is another trick.
The best of the best is to keep the creator session of temp tables alive until the temp tables finish serving all “client” sessions.