理解SQL Server中的锁定行为

Question

我尝试复现问题[1]的情况。

在表格上，我使用维基百科“隔离（数据库系统）”[2]中的数据填充了数据，并在SQL Server 2008 R2 SSMS中执行了以下操作：

1）首先在SSMS的第一个选项卡（窗口）中。

-- transaction isolation level in first window does not influence results (?)
-- initially I thought that second transaction in 2) runs at the level set in first window

begin transaction 
INSERT INTO users VALUES ( 3, 'Bob', 27 )
waitfor delay '00:00:22'
rollback

2) 紧接着，在第二个窗口中

-- this is what I commented/uncommented

-- set transaction isolation level SERIALIZABLE
-- set transaction isolation level READ REPEATABLE
-- set transaction isolation level READ COMMITTED
-- set transaction isolation level READ UNCOMMITTED

SELECT * FROM users --WITH(NOLOCK)

更新：
抱歉，结果已经更正。

根据2）中设置的隔离级别，我的结果是SELECT返回：

- 立即返回（读取未提交的插入行）

- 对于所有使用NOLOCK的SELECT情况

- 对于使用READ UNCOMMITTED的SELECT（无论是否使用NOLOCK）

- 在READ COMMITTED及更高的事务隔离级别（REPEATABLE READ、SERIALIZABLE）下等待事务1）完成（仅当SELECT没有使用NOLOCK时）

这些结果与问题描述（以及答案中解释的内容）[1]相矛盾

例如，SELECT with NOCHECK正在等待1）的完成等等。

如何解释我的结果和[1]呢？

更新2：
这个问题实际上是我问题[3]的子问题（或者说是它们没有得到回答的结果）。

引用：
[1]
解释SQL Server中的锁定行为
Explain locking behavior in SQL Server
[2]
“隔离（数据库系统）”
请在链接后面添加)。我无法在此处保留它！ http://en.wikipedia.org/wiki/Isolation_(database_systems)
[3]
SELECT语句在SQL Server 2005中是否默认使用NOLOCK？
Is NOLOCK the default for SELECT statements in SQL Server 2005?

- Gennady Vanin Геннадий Ванин

---

插入事务的隔离级别不会对第二个事务所看到的产生影响。您确定第二个事务没有在“读未提交”级别下运行吗？ - Martin Smith

Answer 1

这段文字的大意是：有一个有用的 MSDN 链接，讨论了 SQL 2008 中锁定提示的使用。也许在你的例子中，SQL Server 2008 不喜欢你的表锁定？下面链接中的代码片段讨论了 SQL Server 2008 可能会忽略锁定的情况。例如，如果事务隔离级别设置为SERIALIZABLE，并且在SELECT语句中使用了表级锁定提示NOLOCK，则通常用于维护可串行化事务的键范围锁将不会被使用。

CopyUSE AdventureWorks2008R2;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT Title
    FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by 
-- the transaction.
SELECT  
        resource_type, 
        resource_subtype, 
        request_mode
    FROM sys.dm_tran_locks
    WHERE request_session_id = @@spid;

-- End the transaction.
ROLLBACK;
GO

唯一引用 HumanResources.Employee 的锁是模式稳定性 (Sch-S) 锁。在这种情况下，串行化不再得到保证。

在SQL Server 2008中，ALTER TABLE 的 LOCK_ESCALATION 选项可以不利于表锁，并在分区表上启用 HoBT 锁。该选项不是锁提示，但可用于减少锁升级。有关更多信息，请参见 ALTER TABLE (Transact-SQL)。

- kevchadders

---

只有在原始交易执行 SELECT 操作时，这才具有相关性。 - Martin Smith

@Martin - 谢谢，我自己也不确定，但仍然觉得值得发布。 - kevchadders

非常感谢！我在这个领域被矛盾的讨论淹没了，只发现其他RDBMS（如Oracle，PostGreSQL等）中锁定描述符的参考。现在，通过关键词“(Sch-S) lock”，我在搜索中走上了正确的轨道。真是鬼扯，同样的锁在不同的DBMS中被称为不同的名称，描述相同的概念... - Gennady Vanin Геннадий Ванин

@vgv8 - 没问题，很高兴这篇文章能帮到你。 - kevchadders

Answer 2

第二个查询中的提示覆盖了事务隔离级别。
SELECT ... WITH (NOLOCK) 基本上等同于 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT ...。

在任何其他隔离级别下，锁都会被尊重，因此第二个事务会等待第一个事务释放锁。

- VladV