了解SQL Server中的锁定行为

I tried to reproduce the situation of question [1].

我试图重现问题[1]的情况。

On table, taken and filled with data from wiki's "Isolation (database systems)" [2],
in SQL Server 2008 R2 SSMS, I executed:

在桌面上,采取并填充了来自wiki的“隔离(数据库系统)”[2]的数据,在SQL Server 2008 R2 SSMS中,我执行了:

1) first in first tab (window) of 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) immediately after, in second window

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)

Update:
Sorry, results were corrected.

更新:对不起,结果已更正。

My results, depending on isolation level set in 2), are that SELECT returns:

我的结果取决于2)中设置的隔离级别,SELECT返回:

immediately (reading uncommitted inserted row)

立即(读取未提交的插入行)
- for all cases of SELECT with NOLOCK
- for READ UNCOMMITTED (SELECT either with or without NOLOCK)
is waiting the completion of transaction 1) (ONLY IF SELECT is without NOLOCK) and

等待事务1)的完成(仅在SELECT没有NOLOCK时)和
- in READ COMMITTED and higher (REPEATABLE READ, SERIALIZABLE) transaction isolation level

These results contradict to situation described in question (and explained in answers?) [1]
(for example, that SELECT with NOCHECK is waiting completion of 1)), etc.

这些结果与所讨论的情况相矛盾(并在答案中解释?)[1](例如,SELECT with NOCHECK等待完成1))等。

How can my results and [1] be explained?

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

Update2:
This question is really subquestion of my questions [3] (or the result of them not being answered).

更新2:这个问题确实是我的问题[3]的问题(或者他们没有得到回答的结果)。

Cited:
[1]
Explain locking behavior in SQL Server
Explain locking behavior in SQL Server
[2]
"Isolation (database systems)"
Plz add trailing ) to link. I cannot manage to preserve it here in the link! http://en.wikipedia.org/wiki/Isolation_(database_systems)
[3]
Is NOLOCK the default for SELECT statements in SQL Server 2005?
Is NOLOCK the default for SELECT statements in SQL Server 2005?

引用:[1]解释SQL Server中的锁定行为SQL Server中的解释锁定行为[2]“隔离(数据库系统)”Plz添加尾随)链接。我无法在链接中设置保存它! http://en.wikipedia.org/wiki/Isolation_(database_systems)[3] NOLOCK是SQL Server 2005中SELECT语句的默认值吗? NOLOCK是SQL Server 2005中SELECT语句的默认值吗?

2 个解决方案

#1

There is a useful MSDN link her talk about locking hints in SQL 2008. Maybe in your example its a case of SQL Server 2008 disfavoring your tables locks?

有一个有用的MSDN链接,她谈到了SQL 2008中的锁定提示。也许在你的例子中,SQL Server 2008的案例不利于你的表锁?

(The following snippet from the link below talks about locks potentially being ingored by SQL Server 2008)

(以下链接中的以下片段讨论了可能被SQL Server 2008占用的锁定)

As shown in the following example, if the transaction isolation level is set to SERIALIZABLE, and the table-level locking hint NOLOCK is used with the SELECT statement, key-range locks typically used to maintain serializable transactions are not taken.

如下例所示,如果事务隔离级别设置为SERIALIZABLE,并且表级锁定提示NOLOCK与SELECT语句一起使用,则不会采用通常用于维护可序列化事务的键范围锁。

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

The only lock taken that references HumanResources.Employee is a schema stability (Sch-S) lock. In this case, serializability is no longer guaranteed.

引用HumanResources.Employee的唯一锁定是模式稳定性(Sch-S)锁定。在这种情况下,不再保证可序列化。

In SQL Server 2008, the LOCK_ESCALATION option of A LTER TABLE can disfavor table locks, and enable HoBT locks on partitioned tables. This option is not a locking hint, but can but used to reduce lock escalation. For more information, see ALTER TABLE (Transact-SQL).

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

#2

The hint in the second query overrides transaction isolation level.
SELECT ... WITH (NOLOCK) is basically identical to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT ....

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

With any other isolation level the locks are honored, so the second transaction waits until the locks are released by the first one.

对于任何其他隔离级别,锁被授予,因此第二个事务等待直到第一个锁释放锁。

#1