在SQL Server中使用事务隔离级别锁定数据

Question

我正在开发一个应用程序，它需要处理数据访问并发问题，但我不太清楚如何正确使用事务隔离级别。

我有一个名为“Folders”的表，其中包含类似树形结构的文件夹：

+-----------------------------------------------------------------+
| Id (int) | Name (varchar) | FullPath (varchar) | ParentId (int) |
|----------+----------------+--------------------+----------------|
| 1        | 'root1'        | '/root1/'          | NULL           |
| 2        | 'c1'           | '/root1/c1/'       | 1              |
| 3        | 'c2'           | '/root1/c1/c2/'    | 2              |
| 4        | 'root2'        | '/root2/'          | NULL           |
+----------+----------------+--------------------+----------------+

我正在尝试实现“移动文件夹”工作流程，如下所示（例如，我想将ID为2的文件夹移动到新父文件夹ID为4）：

1. 开始事务
2. 读取ID为2的文件夹（称其为folder2）：SELECT * FROM Folders WHERE Id=2
3. 读取ID为4的文件夹（称其为folder4）：SELECT * FROM Folders WHERE Id=4
4. 更新folder2的ParentId和FullPath：UPDATE Folders SET ParentId=folder4.Id, FullPath=folder4.FullPath+folder2.Name+'/' WHERE Id = folder2.Id
5. 读取folder2的所有子文件夹（称之为subfoldersOfFolder2）：SELECT * FROM Folders WHERE FullPath LIKE folder2.FullPath + '%'
6. 对于subfoldersOfFolder2中的每个子文件夹，更新FullPath列（省略查询）
7. 提交事务

显然，我不希望在我的事务完成之前有任何其他交易写入（甚至读取）folder2和subfoldersOfFolder2。阅读这篇关于SQL Server事务的文章后，我认为在步骤#1将隔离级别设置为Serializable会有所帮助。但出于某种原因，似乎并没有发生这种情况。我尝试保持事务打开状态（在第7步之前停止），打开SSMS的另一个实例，并执行SELECT * FROM Folders，查询成功完成，我仍然可以看到第1个事务读取的数据。

为什么会发生这种情况？如何防止其他人读取/写入folder2和subfoldersOfFolder2？我觉得我错过了关于事务实际上如何锁定数据的重要内容。

- Nazz

---

“Serializable” 具体意味着没有并发，全局唯一。这是否是您想要的？如果不是，您应该先使用 updlock, holdlock 选择所有相关的父节点来修改代码。然而，这只会让修改代码正确，但不能防止人们读取您将要修改但尚未修改的子文件夹的路径。考虑到在锁定和读取父文件夹之后才能知道这些子文件夹的 ID（竞争条件），似乎您希望每次编辑路径时对整个表格进行 tablock, xlock, holdlock。 - GSerg

Answer 1

当您使用 Serializable 时，它会保持共享锁（来自 SELECT）在事务完成之前读取的行中。但是，对行的共享锁并不能阻止另一个事务读取同一行......它只是阻止另一个事务获取独占锁在该行上进行更新或删除（这个共享锁）。

如果您想要防止任何其他事务甚至读取（SELECT）这些行，您需要在 SELECT 时强制执行独占锁：

SELECT *
FROM dbo.Folders WITH (XLOCK)
WHERE ....

如果这个事务 "保持打开"，那么没有其他事务可以读取由该WHERE条件选择的任何行 - 直到SELECT .. FROM dbo.Folders WITH (XLOCK)事务已提交或回滚。

- marc_s

Answer 2

更改序列化是我只在最后一步才会考虑的事情。 在这种情况下，我会坚持使用单个事务来更新父ID，但将更新文件夹路径的代码放在更新触发器中。

- cloudsafe

Answer 3

如果您按照以下步骤操作，它会完全按照您的要求执行（阻止读取器和编写器查看行：

在第1个会话中：

create table dbo.test(i1 int, a1 varchar(25))
insert into dbo.test values (1,'NY'),(1,'NY'),(1,'NJ'),(2,'NY'),(2,'NY'),(2,'NJ') 
set transaction isolation level serializable
begin transaction
select * from test where i1=1
update dbo.test set i1=3 where a1='NJ'

在第二个会话中尝试

select * from dbo.test where i1=1 

卡住了....

加上 try 和 catch 可以改善情况，但即使没有它，可序列化仍然有效。你没有展示给我们所有的代码。

- benjamin moskovits

---

如果我从你的代码中删除“update”语句，会话2就不会挂起。为什么会这样？我喜欢的文章说明了“数据库引擎保持对所选数据获取的读取和写入锁定”，因此在会话1中读取的数据应该被锁定，是吗？ - Nazz

没有任何方法（没有设置）可以让SQL Server在仅进行选择而没有更新/插入/删除的事务中锁定行。由于更改，SQL Server保证各种级别的数据一致性。没有更改就没有什么保证。您可能需要探索快照级别隔离，它可以在不锁定的情况下保证数据一致性。每个人都可以获得自己版本的数据。 - benjamin moskovits

@benjaminmoskovits：真的吗？！？！ WITH (XLOCK) - marc_s

我错了。即使没有更新，带有（xlock）的选择对行进行选择也可以防止选择。marc_s的解决方案是正确的。 - benjamin moskovits