是否可以在 SQL Server 中在同一事务中的同一行上应用 s lock 和 x lock

Question

在可重复读取隔离中，共享锁和独占锁将一直应用到事务结束。现在考虑下面提到的查询，该查询在id列上具有聚集索引，在名称和col1上具有非聚集索引

Set transaction isolation level repeatable read
begin transaction
update tableA
set name = 'abc'
where id = 1
select name, col
from tableA
where id = 1
commit transaction

在这种情况下，SQL Server 如何处理锁？将在ID = 1的行上放置两个锁 - X锁和S锁，或者X锁将转换为S锁。

我在这里很困惑。有人可以更多地了解这种锁定行为吗？

Answer 1

您可以通过启用以下跟踪标志或查看sys.dm_tran_locks视图来检查已获取/持有的锁。

不要在生产环境中运行这些跟踪标志。

跟踪标志将输出消息中获取/释放的锁。

在此测试中，它似乎持有 X 锁并在同一个 RID 上获取 S 锁。sys.dm_tran_locks的输出表明实际上只持有 X 锁。

您的结果可能会有所不同。

DBCC TRACEON(1200,-1,3604);
SET transaction isolation level repeatable read
BEGIN TRAN
UPDATE dbo.Customer SET name = 'peter';
SELECT * FROM dbo.Customer WHERE id = 1
DBCC TRACEOFF(1200,-1,3604);
SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
COMMIT

跟踪标志的输出：

进程 56 获取数据库上的 S 锁： 13 [计划指南] （类位0 参考 1） 结果：正常

进程 56 释放数据库上的锁定： 13 [计划指南]

DBCC 执行已完成。如果 DBCC 打印了错误消息，请联系您的 系統管理員。

进程 56 获取对象上的 IX 锁： 13：1920061926：0（类 bit2000000 ref1） 结果：正常

进程 56 获取 IU 锁 页上： 13：1：2150 （类位2000000 ref0） 结果：正常

进程 56 在 RID 上获取 U 锁：13：1：2150：0（类位 2000000 ref0） 结果：正常

进程 56 获取 IX 锁 在 PAGE： 13：1：2150 （类 bit2000000 ref0） 结果：正常

进程 56 获取 RID 上的 X 锁：13：1：2150：0（类位 2000000 ref0） 结果：正常

（1 行受影响）

进程 56 获取 IS 锁定在页面上：13：1：2150 （类位 2000000 ref0） 结果：正常

进程 56 获取 RID 上的 S 锁：13：1：2150：0（类位 2000000 ref0） 结果：正常

（1 行受影响）

进程 56 释放所有锁@00000002E54015A8

进程 56 已完成释放所有锁@00000002E54015A8

进程 56 释放所有锁@00000002E54015A8

进程 56 已完成释放所有锁@00000002E54015A8

进程 56 释放所有锁@00000002E54015A8

进程 56 已完成释放所有锁@00000002E54015A8

进程 56 释放所有锁@00000002E54015A8

进程 56 已完成释放所有锁@00000002E54015A8

进程 56 释放所有锁@00000002E54015A8

进程 56 已完成释放所有锁@00000002E54015A8

进程 56 释放所有锁@00000002E54015A8

进程 56 已完成释放所有锁@00000002E54015A8

进程 56 释放所有锁@00000002E54015A8

进程 56 已完成释放所有锁@00000002E54015A8

（4 行受影响） DBCC 执行已完成。如果 DBCC 打印错误 消息，请与系统管理员联系。

Answer 2

好吧

，由于它位于同一事务中，SQL Server当然知道您更改/更新了什么，并且可以将其返回到您的SELECT。

X独占锁仅阻止其他事务在更新数据时读取该数据。X锁将一直保留在原位，直到执行COMMIT。

如果在UPDATE后暂停执行，然后切换到另一个 SSMS 窗口并在那里执行SELECT，则会阻止它读取正在更新的行