消失的共享锁
原文地址:
译者:
在SQL SERVER中,不同事务隔离级别下,获取共享锁的情况亦不同:
1) Read Uncommitted下不申请共享锁。
2) Read Committed下需申请共享锁,语句执行过程中,共享锁使用完立即释放。
3) Repeatable Read下需申请共享锁,共享锁需等到事务结束才释放。
4) Serialization下申请键范围锁。
SQL SERVER的缺省事务隔离级别是Read Committed,在事务执行的过程中会不停地申请和释放共享锁,代价非常大。因此SQL SERVER对共享锁进行了某种优化:在某些情况下,会跳过共享锁的申请和释放。注意:SQL SERVER至今没有在任何文档(至少我没有看见)中描述过这种共享锁优化,一切都是推测和总结。
以前翻译的文章《疯狂的“独占”行锁》其实就应该属于这种共享锁优化。本文的作者试图更加全面的描述这种共享锁优化行为。
译文:
这本文中,我将描述一种目前知之甚少的锁优化行为,它会给下面的问题一个令人意外的答案:
“如果我在一个数据行中拥有一个排他锁,其他运行在缺省的readcommitted隔离级别的事务还能读该行吗?”
基于以往的知识:读操作在试图申请一个共享锁时会受阻,所以大多数人的答案是:“不能”。还有一些人会说:这得依赖于READ_COMMITTED_SNAPSHOT数据库选项是否打开,本文中我们假设不用考虑这种情况,我们就是简单的缺省read committed(锁)隔离级别下的情况。
一个令人意外的答案
为了进行调查,我们创建一个测试表,并添加一行数据。
CREATE TABLE
dbo.Demo
(
some_key INTEGER PRIMARY KEY,
some_value INTEGER NOT NULL,
);
GO
INSERT INTO
dbo.Demo (some_key, some_value)
VALUES (1, 100);
GO
IF DB_NAME() = N'tempdb'
CHECKPOINT;
现在,启动一个事务,获取一个排他锁,并列出该事务拥有的锁的情况。
BEGIN TRANSACTION;
SELECT D.some_key,
D.some_value
FROM dbo.Demo D WITH (XLOCK);
SELECT L.resource_type,
L.request_mode,
L.request_status,
L.resource_description,
L.resource_associated_entity_id
FROM sys.dm_tran_current_transaction T
JOIN sys.dm_tran_locks L
ON L.request_owner_id = T.transaction_id;
就像所期望的一样:行上拥有一个排他锁,表和页上各有一个意向排他锁。
在另一个独立的连接中,我们试图来读取这个表的内容:
SELECT D.some_key,
D.some_value
FROM dbo.Demo D WITH (ROWLOCK, READCOMMITTEDLOCK);
可能和你期望的不同,这个查询并没有受阻——尽管已经有排他锁,但还是立即就返回了。
(注:READCOMMITTEDLOCK表提示是确保不管READ_COMMITTED_SNAPSHOT选项的情况,读操作都使用READCOMMITTED(锁)隔离级别的规则)
锁优化
SQL SERVER包含一种优化:允许在某些情况下避免申请行级别上的共享锁。特别是若没有这些锁也不会有读到非提交事务数据的风险时,它就会跳过共享锁的申请。
上面的SELECT查询没有受阻的原因非常简单:没有申请共享锁。我们可以通过profiler来检查锁的申请情况:
在表和页级别上分别有一个意向共享锁(IS),这和第一个查询的意向排他锁是兼容的。因为并没有和已存在的排他锁相冲突的行级别的共享锁,所以并没有受阻。
仅仅是行级别共享锁
这种优化仅仅是针对行级别的共享锁的,所以如果我们特别申请一个其他粒度的锁,查询就要受阻了:
SELECT D.some_key,
D.some_value
FROM dbo.Demo D WITH (PAGLOCK, READCOMMITTEDLOCK);
看下面的锁,我们看到一个表级别的意向共享锁(IS)已被成功申请。企图获取页级别的共享锁时受阻了,因为共享锁和已经存在的页级别的意向排他锁是相冲突的。
没有未提交的变化
如果页中含有未提交的变化,SQL SERVER也不会使用这种锁优化。这是有道理的:如果页中有一个未提交的变化,此时再不使用共享锁,就可能会发生脏读。在read committed隔离级别上是不允许发生脏读的。
SQL SERVER是基于页来使用这种锁优化的,所以一个读操作可能在一些页中会申请行级别共享锁(比如,这些页中有未提交的变化)而另外的页中却不需要申请(因为足够安全所以跳过了这些锁)。
为了演示,我们使用上面还开着的事务连接,并在表中插入第二条数据:
INSERT INTO
dbo.Demo (some_key, some_value)
VALUES (2, 200);
现在两行都是排他锁,而且在页中有一个未提交的变化:
如果再次运行SELECT查询,我们会发现:正如我们所期望的,它在试图获取一个排他锁是被阻塞了。如果我们加上一个READPAST提示(跳过已锁定行),我们发现会立即返回查询结果——没有任何行。
SELECT D.some_key,
D.some_value
FROM dbo.Demo D WITH (ROWLOCK, READCOMMITTEDLOCK, READPAST);
页上的两行都被跳过了:一条是因为前面使用了XLOCK表提示;另一条是为了保护新插入行(但是还没有提交)的排他锁。Profiler跟踪显示:这两个行级别的共享锁申请都超时了:
提交变化就好了
如果提交数据,我们发现又可以跳过行级别锁了:
COMMIT TRANSACTION;
BEGIN TRANSACTION;
SELECT D.some_key,
D.some_value
FROM dbo.Demo D WITH (XLOCK);
在第二个连接中,运行SELECT查询并不会获取任何共享锁,而且两行数据都返回了。
SELECT D.some_key,
D.some_value
FROM dbo.Demo D WITH (ROWLOCK, READCOMMITTEDLOCK);
事务回滚非常大不同
如果我们在页上修改了数据,然后回滚了这些修改,此时SQLSERVER也不能在该页上使用锁优化,直到该页中进行一次提交或者将该页写到磁盘中(比如,执行checkpoint)。
使用上面连接打开的事务,我们插入第三行,并立即回滚修改:
INSERT INTO
dbo.Demo (some_key, some_value)
VALUES (3, 300);
我们会发现SELECT在受影响的页上回申请共享锁,即使该页上并没有排他锁。
SELECT D.some_key,
D.some_value
FROM dbo.Demo D WITH (ROWLOCK, READCOMMITTEDLOCK);
Profiler跟踪显示行级别的共享锁:
现在再次插入第三行数据,并提交变化:
BEGIN TRANSACTION;
INSERT INTO
dbo.Demo (some_key, some_value)
VALUES (3, 300);
COMMIT TRANSACTION;
我们的SELECT查询返回了所有的三行数据,并且只是在表和页级别上申请了意向共享锁:
我们也可以通过手工执行CHECKPOINT重新使能(re-enabled)锁优化。
总结
SQLSERVER在读取数据时,会决定是否在行、页或者表级别上申请锁。本文描述的锁优化有一个有趣的副作用:一个查询获取页锁可能会被阻塞,而同样的查询使用ROWLOCK却可能不会被阻塞(我说“可能不会”是因为ROWLOCK是一个提示,而不是指令,所以引擎可能根本就不睬他)。
比如,一个需要申请页级共享锁的查询会因为遇到意向排他锁(IX)而被阻塞,但是该查询若指定ROWLOCK则可能仅申请页级意向共享锁(IS,它和IX锁兼容),并且不要获取任何行级别锁。这不是说你应该立即在所有的SELECT查询中加上ROWLOCK提示——这可能是一个非常坏的主意——但是你应当知道存在这样的锁优化方法。
Readcommitted隔离级别保证两件事:不会有脏写和脏读。但是没有说这种保证是如何实现的。SQL SERVER碰巧使用共享锁作为这种缺省隔离级别的部分实现——但是这种锁并不总是存在的。