全局临时表上持有哪些类型的锁？

Question

我想知道：

• 已提交隔离级别。
• 数据被插入、删除或读取。

用于全局临时表的锁(用于读取的共享锁和用于插入/删除的独占锁)与传统的行存储表相同。

对于临时表，我相信在当前的情况下不会有阻塞，因为它“存在”，所以我不能同时读取和插入数据。

但是对于全局临时表，假设我有许多操作--一些正在插入新数据，一些正在删除数据，还有一些希望读取的数据将被删除。

假设表的结构是：

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

相同规则在这里应用了吗？

Answer 1

我对这个问题很好奇，我想试一试，看看tempdb是一个特殊的数据库，还是像另一个用户数据库一样，其中应用了默认的隔离“读提交”。

下面是我执行的查询：

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

对下一个窗口中这些表的少数记录执行更新查询

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

上述语句都按预期更新了22行。

稍后，我在两个不同的窗口中对这两个表运行select语句，到目前为止，这两个表都被卡住了：

我注意到，在第一次查询中，屏幕上根本没有记录，而在第二种情况下，8830条记录出现在屏幕上，然后卡在屏幕上(不知道为什么？)但是，在这两种情况下，查询都会被困住并永远运行，因为它们都在等待更新完成。这是MSSQL数据库默认行为中所期望的。

我使用sp_whoisactive验证了相同的结果，并且可以看到数据库被阻塞，如下所示：

其中每个锁信息都提供了详细信息。

第一次：

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

第二名：

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

第三：

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

另外，使用以下命令对这些事务进行交叉检查的隔离级别：

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)

结果：

有了所有这些，可以说，对于tempdb来说，隔离级别设置为"Read“，规则应用于其他数据库。

我已经在MSSQL 2019版本上运行了这些查询。