最近在处理一个锁的问题时，发现一个比较郁闷的事，使用X锁居然无法锁住查询，模拟这个问题，可以使用如下T-SQL脚本来建立测试环境。
USE master; GO IF @@TRANCOUNT > 0 ROLLBACK TRAN; GO -- ======================================= -- 建立测试数据库 -- a. 删除测试库, 如果已经存在的话 IF DB_ID(N'db_xlock_test') IS NOT NULL BEGIN; ALTER DATABASE db_xlock_test SET SINGLE_USER WITH ROLLBACK AFTER 0; DROP DATABASE db_xlock_test; END; -- b. 建立测试数据库 CREATE DATABASE db_xlock_test; -- c. 关闭READ_COMMITTED_SNAPSHOT 以保持SELECT 的默认加锁模式 ALTER DATABASE db_xlock_test SET READ_COMMITTED_SNAPSHOT OFF; GO -- ======================================= -- 建立测试表 USE db_xlock_test; GO CREATE TABLE dbo.tb( id int IDENTITY PRIMARY KEY, name sysname ); INSERT dbo.tb SELECT TOP(50000) O1.name + N'.' + O2.name + N'.' + O3.name FROM sys.objects O1 WITH(NOLOCK), sys.objects O2 WITH(NOLOCK), sys.objects O3 WITH(NOLOCK); GO
然后，建立一个连接，执行下面的脚本来实现加锁。
-- ======================================= -- 测试连接1 - 加锁 BEGIN TRAN --测试的初衷是通过SELECT加锁，结果发现UPDATE也锁不住 UPDATE dbo.tb SET name = name --SELECT COUNT(*) FROM dbo.tb WITH(XLOCK) WHERE id <p style="margin: 0cm 0cm 0pt; text-align: left;"><span style="font-size: small;"><span style="font-family: 宋体; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">通过执行结果，可以看到对象被加锁的情况：表级和页级上是</span><span style="font-family: Calibri;">IX</span><span style="font-family: 宋体; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">锁，记录上是</span><span style="font-family: Calibri;">X</span><span style="font-family: 宋体; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">锁。</span></span></p>
spid |
tran_count |
database_name |
object_id |
|||||||
51 |
1 |
db_xlock_test |
21575115 |
|||||||
spid |
dbid |
ObjId |
IndId |
Type |
Resource |
Mode |
Status |
|||
51 |
7 |
0 |
0 |
DB |
S |
GRANT |
||||
51 |
7 |
21575115 |
1 |
PAG |
0.095138889 |
IX |
GRANT |
|||
51 |
7 |
21575115 |
0 |
TAB |
IX |
GRANT |
||||
51 |
1 |
1131151075 |
0 |
TAB |
IS |
GRANT |
||||
51 |
7 |
21575115 |
1 |
KEY |
(020068e8b274) |
X |
GRANT |
|||
51 |
7 |
21575115 |
1 |
KEY |
-10086470766 |
X |
GRANT |
|||
然后新建一个连接，执行下面的T-SQL查询，看看会否被连接1锁住
-- ======================================= -- 测试连接2 - 被阻塞(在测试连接1 执行后执行) SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT * FROM dbo.tb WHERE id <p style="margin: 0cm 0cm 0pt; text-align: left;"><span style="font-size: small;"><span style="font-family: 宋体; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">上述查询会很快返回结果，并不会被查询</span><span style="font-family: Calibri;">1</span><span style="font-family: 宋体; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">阻塞住。</span></span></p><p style="margin: 0cm 0cm 0pt; text-align: left;"><span style="font-size: small;"><span style="font-family: 宋体; color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">按照我们的了解（联机帮助上也有说明），在</span><span style="color: red;"><span style="font-family: Calibri;">READ COMMITTED</span></span><span style="font-family: 宋体; color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">事务隔离级别下，查询使用共享锁（</span><span style="color: red;"><span style="font-family: Calibri;">S</span></span><span style="font-family: 宋体; color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">），而根据锁的兼容级别，</span><span style="color: red;"><span style="font-family: Calibri;">S</span></span><span style="font-family: 宋体; color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">锁是与</span><span style="color: red;"><span style="font-family: Calibri;">X</span></span><span style="font-family: 宋体; color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">锁冲突的，所以正常情况下，连接</span><span style="color: red;"><span style="font-family: Calibri;">2</span></span><span style="font-family: 宋体; color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">的查询需要等待连接</span><span style="color: red;"><span style="font-family: Calibri;">1</span></span><span style="font-family: 宋体; color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">执行完成。可是测试的结果去违反了这一原则。</span></span></p><p style="margin: 0cm 0cm 0pt; text-align: left;"><span style="font-size: small;"><span style="font-family: 宋体; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">为了了解为什么连接</span><span style="font-family: Calibri;">2</span><span style="font-family: 宋体; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">不会被阻塞，对连接</span><span style="font-family: Calibri;">2</span><span style="font-family: 宋体; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">做了一个</span><span style="font-family: Calibri;">Trace</span><span style="font-family: 宋体; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">，发现一个更郁闷的问题，</span><span style="font-family: Calibri;">Trace</span><span style="font-family: 宋体; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">的结果如下：</span></span></p>
EventClass |
TextData |
ObjectID |
Type |
Mode |
Lock:Acquired |
21575115 |
5 - OBJECT |
6 - IS |
|
Lock:Acquired |
1:77 |
0 |
6 - PAGE |
6 - IS |
Lock:Acquired |
[PLANGUIDE] |
0 |
2 - DATABASE |
3 - S |
Lock:Acquired |
21575115 |
5 - OBJECT |
6 - IS |
|
Lock:Acquired |
1:77 |
0 |
6 - PAGE |
6 - IS |
Lock:Acquired |
1:80 |
0 |
6 - PAGE |
6 - IS |
Lock:Acquired |
1:89 |
0 |
6 - PAGE |
6 - IS |
Trace的前面两行是连接2的Trace结果，从结果看，连接2仅使用了意向共享锁（IS），而且只是表级和页级，按照锁的兼容性原则，IS和IX（连接1在表级和页级仅使用了IX锁）是不冲突的，所以连接2的查询不会被阻塞。在增加了查询的数据量后，Trace结果表明查还是只在表级和页级使用了IS锁（Trace结果的最后4行）。
对于这个问题，解决的办法当然就是提升连接1锁的粒度，使用PAGLOCK表提示将锁的粒度提升到页级，这样IS与X是冲突的，就可以成功阻塞连接2。
但疑问就是，为什么查询只在表级和页级下意向共享锁（IS），而不在行级下共享锁（X），这个似乎与联机帮助上的说明不一样（还是一直以来理解上的偏差呢）。
附：联机帮助上关于锁模式的说明
共享锁
共享锁（S 锁）允许并发事务在封闭式并发控制下读取 (SELECT) 资源。
更新锁
更新锁（U 锁）可以防止常见的死锁。在可重复读或可序列化事务中，此事务读取数据 [获取资源（页或行）的共享锁（S 锁）]，然后修改数据 [此操作要求锁转换为排他锁（X 锁）]。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排他锁（X 锁）。共享模式到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排他锁（X 锁）以进行更新。由于两个事务都要转换为排他锁（X 锁），并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。
若要避免这种潜在的死锁问题，请使用更新锁（U 锁）。一次只有一个事务可以获得资源的更新锁（U 锁）。如果事务修改资源，则更新锁（U 锁）转换为排他锁（X 锁）。
排他锁
排他锁（X 锁）可以防止并发事务对资源进行访问。使用排他锁（X 锁）时，任何其他事务都无法修改数据；仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。
数据修改语句（如 INSERT、UPDATE 和 DELETE）合并了修改和读取操作。语句在执行所需的修改操作之前首先执行读取操作以获取数据。因此，数据修改语句通常请求共享锁和排他锁。例如，UPDATE 语句可能根据与一个表的联接修改另一个表中的行。在此情况下，除了请求更新行上的排他锁之外，UPDATE 语句还将请求在联接表中读取的行上的共享锁。
意向锁
数据库引擎使用意向锁来保护共享锁（S 锁）或排他锁（X 锁）放置在锁层次结构的底层资源上。意向锁之所以命名为意向锁，是因为在较低级别锁前可获取它们，因此会通知意向将锁放置在较低级别上。
本文讲解了锁不住的查询，更多相关内容，请关注php中文网。
相关推荐：
