锁不住的查询

原创 2009年07月27日 10:27:00

环境: 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的前面两行是连接2Trace结果,从结果看,连接2仅使用了意向共享锁(IS),而且只是表级和页级,按照锁的兼容性原则,ISIX(连接1在表级和页级仅使用了IX锁)是不冲突的,所以连接2的查询不会被阻塞。在增加了查询的数据量后,Trace结果表明查还是只在表级和页级使用了IS锁(Trace结果的最后4行)。

         对于这个问题,解决的办法当然就是提升连接1锁的粒度,使用PAGLOCK表提示将锁的粒度提升到页级,这样ISX是冲突的,就可以成功阻塞连接2

         但疑问就是,为什么查询只在表级和页级下意向共享锁(IS),而不在行级下共享锁(X),这个似乎与联机帮助上的说明不一样(还是一直以来理解上的偏差呢)。

附:联机帮助上关于锁模式的说明

共享锁

共享锁(S 锁)允许并发事务在封闭式并发控制下读取 (SELECT) 资源

更新锁

更新锁(U 锁)可以防止常见的死锁。 在可重复读或可序列化事务中,此事务读取数据 [获取资源(页或行)的共享锁(S 锁)],然后修改数据 [此操作要求锁转换为排他锁(X 锁)] 如果两个事务获得了资源上的共享模式锁,然后试图同时更新数据,则一个事务尝试将锁转换为排他锁(X 锁)。 共享模式到排他锁的转换必须等待一段时间,因为一个事务的排他锁与其他事务的共享模式锁不兼容;发生锁等待。 第二个事务试图获取排他锁(X 锁)以进行更新。 由于两个事务都要转换为排他锁(X 锁),并且每个事务都等待另一个事务释放共享模式锁,因此发生死锁。

若要避免这种潜在的死锁问题,请使用更新锁(U 锁)。 一次只有一个事务可以获得资源的更新锁(U 锁)。 如果事务修改资源,则更新锁(U 锁)转换为排他锁(X 锁)。

排他锁

排他锁(X 锁)可以防止并发事务对资源进行访问。 使用排他锁(X 锁)时,任何其他事务都无法修改数据;仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。

数据修改语句(如 INSERTUPDATE DELETE)合并了修改和读取操作。 语句在执行所需的修改操作之前首先执行读取操作以获取数据。 因此,数据修改语句通常请求共享锁和排他锁。 例如,UPDATE 语句可能根据与一个表的联接修改另一个表中的行。 在此情况下,除了请求更新行上的排他锁之外,UPDATE 语句还将请求在联接表中读取的行上的共享锁。

意向锁

数据库引擎使用意向锁来保护共享锁(S 锁)或排他锁(X 锁)放置在锁层次结构的底层资源上。 意向锁之所以命名为意向锁,是因为在较低级别锁前可获取它们,因此会通知意向将锁放置在较低级别上。

C# lock 锁的操作

private  static  readonly  object  SequenceLock = new  object();         public  void  test()     ...
  • changhong009
  • changhong009
  • 2012年04月23日 17:09
  • 487

刨根问底Java多线程系列:线程不安全的最根本的原因是什么

一、引言 在多线程环境中,线程安全毫无疑问是最主要面对的问题。 找到线程不安全的根源,就好像找到了一把万能钥匙,解开程序中的任何线程不安全隐患。 12 二、分析 对于线程安全的定义...
  • u013970971
  • u013970971
  • 2017年11月23日 16:53
  • 90

synchronized锁不住?

当synchronized遇上基本类型的封装类型对象时,锁不住?不废话,上代码public class Test { static Integer lock = new Integer(1);...
  • weixin_39471786
  • weixin_39471786
  • 2017年07月10日 23:45
  • 123

线程、同步与锁——Mutex想说爱你不容易

除了Lock()、Monitor之外,我们最长用的就是Mutex了,但是玩不好Mutex就总会造成死锁或者AbandonedMutexException(我就玩的不怎么好,在并发性访问测试的时候总是遇...
  • king16304
  • king16304
  • 2016年08月10日 16:48
  • 683

Redis锁防止并发

Redis锁防止并发
  • liushunqiu
  • liushunqiu
  • 2016年08月22日 15:07
  • 1001

基于Redis的分布式锁到底安全吗?

网上有关Redis分布式锁的文章可谓多如牛毛了,不信的话你可以拿关键词“Redis 分布式锁”随便到哪个搜索引擎上去搜索一下就知道了。这些文章的思路大体相近,给出的实现算法也看似合乎逻辑,但当我们着手...
  • paincupid
  • paincupid
  • 2017年07月14日 00:08
  • 1517

synchronized锁住了什么

先看一个简单示例,下面这段代码能够正常通过编译。 public class SyncTest {     public SyncTest syncVar;    ...
  • yanlinwang
  • yanlinwang
  • 2015年03月17日 11:12
  • 10085

左查询和右查询区别

左查询和右查询区别 Left Join / RightJoin /inner join相关 关于左连接和右连接总结性的一句话: 左连接where只影向右表,右连接where只影响左表。 Left...
  • yanwen_dong
  • yanwen_dong
  • 2013年10月31日 14:28
  • 2407

hibernate查询竟然有6种方法

hibernate查询竟然有6种方法,用了hibernate好久了,偶然才发现hibernate有那么多种查询方式 以前 也系统化的学过hibernate 但是仅仅 只是记得hibernate的sql...
  • java_best
  • java_best
  • 2016年09月19日 16:24
  • 4788

hibernate的三种查询方式

Hibernate的查询方式 hibernate的查询方式常见的主要分为三种: HQL, QBC(命名查询), 以及使用原生SQL查询(SqlQuery) 一、HQL查询 • HQL(Hibe...
  • u010963948
  • u010963948
  • 2013年11月19日 09:20
  • 14075
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:锁不住的查询
举报原因:
原因补充:

(最多只允许输入30个字)