`
javayestome
  • 浏览: 1046018 次
  • 性别: Icon_minigender_2
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

锁不住的查询

GoSQLSQL Server脚本 
阅读更多

环境： 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

 width
分享到：
| jxta shell 一些命令
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics