2

I am wondering if:

  • the isolation level is Read Committed
  • data is inserted or deleted or read

are the same locks (shared for reading and exclusive for insert/delete) used for global temporary table as the traditional row store table.

For temporary table I believe there will be no blocking as it "lives" during the current context, so I am not able to read and insert data at the same time.

But for global temporary table, let's say I have many operations - some are inserting new data, some are deleting data, and some wants to read data is going to be deleted.

Let's say the structure of the table is:

GroupID EntityID
1       101
1       102
1       103
2       101
2       104
3       100

Are the same rules applied here?

2

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:

First Select Query

Second Select Query

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:

Output of sp_whoisactive

Lock information for each of them is provided for details.

1st:

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

2nd:

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

3rd:

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

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:

Isolation Level

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.

  • Do you mean to say that in both local temp and global temp table, isolation level is set as "Read Committed" and rules apply similar to other databases. – KumarHarsh May 14 at 7:57
  • @KumarHarsh I have not used local temp tables, its all global tables in my examples and question is also on global temp tables. Local temp tables are confined to only that session as also mentioned here --> sqlines.com/articles/sql-server/… – Learning_DBAdmin May 14 at 8:38
  • So local temp table will not experience any locking ? – KumarHarsh May 14 at 10:46
  • I am not sure what your question is, please ask a new question on the forum and some expert will advise. As far as above answer is concerned, it is for global temp tables which can be accessed in any session or from any database on the same instance. – Learning_DBAdmin May 14 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.