What types of locks are held on global temporary tables?
I was curious for this question and wanted to try myself and see whether tempdb is a special database or just like another user database wherein default isolation "Read Committed" is applied or not.

Below are queries that I executed:

--Created two tables in tempdb, one named table and second one as gobal tempdb table

select * into tempdb.dbo.msgs from sys.messages


select * into ##msgs from sys.messages


Executed Update query for few records of these tables in next window

begin tran
go
update dbo.msgs set language_id = 1055 where message_id = 11242
go
update ##msgs set language_id = 1055 where message_id = 11242
go


Both the above statement updated 22 rows as expected.

Later on I ran select statement on these two tables in two different window and so far both are stuck:





One thing I noted that in the first query no record is coming at all on screen whereas in the second case, 8830 records are on screen and stuck afterwards( Not sure why?) however in both the case, query is stuck and running forever as they are waiting for update to complete. This is what was expected in the default behavior of MSSQL database.

I validated same using sp_whoisactive and could see there is database blocking as below:



Lock information for each of them is provided for details.

1st:

&lt;Database name="tempdb"&gt;
  &lt;Objects&gt;
    &lt;Object name="msgs" schema_name="dbo"&gt;
      &lt;Locks&gt;
        &lt;Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="1" /&gt;
        &lt;Lock resource_type="PAGE" page_type="*" request_mode="S" request_status="WAIT" request_count="1" /&gt;
      &lt;/Locks&gt;
    &lt;/Object&gt;
  &lt;/Objects&gt;
&lt;/Database&gt;


2nd:

&lt;Database name="tempdb"&gt;
  &lt;Objects&gt;
    &lt;Object name="##msgs" schema_name="dbo"&gt;
      &lt;Locks&gt;
        &lt;Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" /&gt;
        &lt;Lock resource_type="PAGE" page_type="*" request_mode="UIX" request_status="GRANT" request_count="22" /&gt;
        &lt;Lock resource_type="RID" page_type="*" request_mode="X" request_status="GRANT" request_count="22" /&gt;
      &lt;/Locks&gt;
    &lt;/Object&gt;
    &lt;Object name="msgs" schema_name="dbo"&gt;
      &lt;Locks&gt;
        &lt;Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" /&gt;
        &lt;Lock resource_type="PAGE" page_type="*" request_mode="UIX" request_status="GRANT" request_count="22" /&gt;
        &lt;Lock resource_type="RID" page_type="*" request_mode="X" request_status="GRANT" request_count="22" /&gt;
      &lt;/Locks&gt;
    &lt;/Object&gt;
  &lt;/Objects&gt;
&lt;/Database&gt;


3rd:

&lt;Database name="tempdb"&gt;
  &lt;Objects&gt;
    &lt;Object name="##msgs" schema_name="dbo"&gt;
      &lt;Locks&gt;
        &lt;Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="1" /&gt;
        &lt;Lock resource_type="PAGE" page_type="*" request_mode="S" request_status="WAIT" request_count="1" /&gt;
      &lt;/Locks&gt;
    &lt;/Object&gt;
  &lt;/Objects&gt;
&lt;/Database&gt;


Also cross-checked isolation level for each of these transaction using below command:

SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id in(63,66,67)


Result:



With all these, it could be stated that for tempdb also, isolation level is set as "Read Committed" and rules apply similar to other databases.

I have run these queries on MSSQL 2019 version.