环境: SQL Server 2005 or 2008
最近在处理一个锁的问题时,发现一个比较郁闷的事,使用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 <= 2;
SELECT
spid = @@SPID,
tran_count = @@TRANCOUNT,
database_name = DB_NAME(),
object_id = OBJECT_ID(N'dbo.tb', N'Table');
-- 显示锁
EXEC sp_lock @@SPID;
通过执行结果,可以看到对象被加锁的情况:表级和页级上是IX锁,记录上是X锁。
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 <= 2;
上述查询会很快返回结果,并不会被查询1阻塞住。
按照我们的了解(联机帮助上也有说明),在READ COMMITTED事务隔离级别下,查询使用共享锁(S),而根据锁的兼容级别,S锁是与X锁冲突的,所以正常情况下,连接2的查询需要等待连接1执行完成。可是测试的结果去违反了这一原则。
为了了解为什么连接2不会被阻塞,对连接2做了一个Trace,发现一个更郁闷的问题,Trace的结果如下:
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 锁)放置在锁层次结构的底层资源上。 意向锁之所以命名为意向锁,是因为在较低级别锁前可获取它们,因此会通知意向将锁放置在较低级别上。
分享到:
相关推荐
锁不住的安全 攻防实训与靶场 安全测试 web安全 红蓝对抗 工控安全
屏幕锁 锁住屏幕 让别人动不了你电脑 灰常实用的小软件
「安全体系」锁不住的安全 - 安全研究 威胁情报 NGFW 网络安全 金融安全 WEB应用防火墙
四年级语文下册 第三单元 11锁不住的心教案设计 冀教版-冀教版小学四年级下册语文教案.doc
S7-200SMART PLC中使用临时变量TEMP无法实现自锁功能的解决办法
支持密码解锁,指纹解锁,刷卡解锁,蓝牙解锁
2015春冀教版语文四下《锁不住的心》ppt课件
抓不住的方块 java游戏 课程设计 游戏
大家的服务器如果抗不住的话可以来我这里查询,呵呵! 广告ad1.asp和ad2.asp换成你的广告代码赚钱就可以了。测试地址:http://www.xamq.com/tj/index 下载地址:http://www.xamq.com/tj/index.rar
Java练习碰撞检测:抓不住的小球,在本练习中,利用Java的图形界面技术和消息响应机制完成了抓不住的方块的制作。通过图形界面的技术,完成了界面布局和方块的制作。通过运用Windows下鼠标动作的响应技术,实现“抓...
实现了安卓语音解锁,代码详细简洁。本人自己写的吧。请多指教。
KidsZone,世界一流的安卓手机锁,绝对能把手机锁住。国内的儿童桌面,全部使用过,都有这样或那样的问题,锁不住。
春色满园关不住,一枝红杏出墙来,满院子的桃花,唯美樱花,春天里的南方小镇,水墨画风古宅院与桃花,春梦主题春天ppt模板。
一个Java小游戏抓不住的方块,用户可以通过鼠标抓取方块,但是无论用户怎样努力,方块总是朝着与鼠标相反的方向运动,总是抓不住方块。主要是利用Java的图形界面技术和消息响应机制完成了抓不住的方块的制作。通过...
简述了生活中的数字欺骗游戏,对提高程序员的基本素质,还是有用的。
《自控力》读后感-----“我控制不住自己,怎么办-”.doc
通过对2012年春晚音响问题的分析,有助于提升演播厅音响系统应用操作技能。
MySQL查询语句及MySQL8.0新特性窗口函数,自己总结了一些,供大家参考,记不住可以随时来看看