ITPub博客

首页 > 数据库 > SQL Server > SQLserver锁和事务隔离级别的比较与使用(转)

SQLserver锁和事务隔离级别的比较与使用(转)

原创 SQL Server 作者:RockJack 时间:2007-11-05 14:54:44 0 删除 编辑

对象

锁:每条SQL语句

隔离:事务

并发问题

丢失更新

未确认的读取(脏读)

不一致的分析(非重复读):多次读取相同的数据(行)不一致(其他用户更改update

幻像读:多次读取有不存在和新增的数据(其他用户插入insert或删除delete

隔离级别

隔离级别

脏读

不可重复读取

幻像

说明

未提交读(read uncommitted)

如果其他事务更新,不管是否提交,立即执行

提交读(read committed默认)

读取提交过的数据。如果其他事务更新没提交,则等待

可重复读(repeatable read)

查询期间,不允许其他事务update

可串行读(serializable)

查询期间,不允许其他事务insert或delete

提交读

假设存在表A,如下所示

A1

A2

A3

11

21

31

12

22

32

打开查询分析器并打开两个连接,分别输入如下两个事务:

--事务Ⅰ

SET TRANSACTION ISOLATION LEVEL READ Committed

begin tran

update A set A2 = 20 where A1 = 11

waitfor delay '00:00:10'

rollback tran

--事务Ⅱ

SET TRANSACTION ISOLATION LEVEL READ Committed

select * from A where A1 = 11

如果先运行事务Ⅰ,然后紧接着运行事务Ⅱ,则事务Ⅱ要等待10秒钟(一个连接在修改数据块时别的连接也不能查询这个数据块,直到解锁。反之亦然:读的时候不能写和修改)。

如果把事务Ⅱ改为如下

SET TRANSACTION ISOLATION LEVEL READ UNCommitted

select * from A where A1 = 11

那么事务Ⅱ不需等待,立即执行(可以看出READ UNCommitted事务select不对数据发出共享锁

锁:(这里主要讲解 共享锁 排他锁 两种经常用到的锁)

共享锁主要是为了共享读(select),如果存在事务(一个或多个)拥有对表中数据(关于锁数据的多少,视锁的粒度而定)的共享锁,不允许对锁定的数据进行更新(update)(从锁的角度讲,即不允许事务获取排他锁,要等到所有的共享锁都释放掉)。反之,如果事务对数据已经具有排他锁(只能有一个),其他的事务就不能对锁定的数据获取共享锁和排他锁(即排他锁与共享锁不能兼容,更多信息请查看锁兼容性),在此特别强调一下 锁定的数据 ,因为有的资料上讲解到“一个连接写的时候,另一个连接可以写”,实际上写的这种情况是各个连接的读写的数据不是相同的行,也就是说各个连接锁定的数据不同。

根据以上分析,我们总结为六个字为“共享读,排他写”。

了解了锁的情况之后,又涉及到一个问题。事务究竟要保持锁多久呢?

一般来说,共享锁的锁定时间与事务的隔离级别有关,如果隔离级别为Read Committed的默认级别,只在读取(select)的期间保持锁定,即在查询出数据以后就释放了锁;如果隔离级别为更高的Repeatable readSerializable,直到事务结束才释放锁。另说明,如果select语句中指定了HoldLock提示,则也要等到事务结束才释放锁。

排他锁直到事务结束才释放。

做出了以上分析,现在我们可能会存在这样的疑问,到底在执行SQL语句的时候发出什么样的锁呢,这就由事务的隔离级别决定了。一般情况,读语句(select)发出共享锁,写语句(update,insert,delete)发出排他锁。但是,如果这样不能满足我们的要求怎么办呢,有没有更多选择呢,别急,SQLserver为我们提供了锁定提示的概念。

锁定提示对SQL语句进行特别指定,这个指定将覆盖事务的隔离级别。下面对各个锁定提示分别予以介绍(更多资料请查看SQLserver的联机帮助),笔者做出了以下分类。

类型1

READUNCOMMITTED:不发出锁

READCOMMITTED:发出共享锁,保持到读取结束

REPEATABLEREAD:发出共享锁,保持到事务结束

SERIALIZABLE:发出共享锁,保持到事务结束

类型2

NOLOCK:不发出锁。等同于READUNCOMMITTED

HOLDLOCK:发出共享锁,保持到事务结束。等同于SERIALIZABLE

XLOCK:发出排他锁,保持到事务结束。

UPDLOCK:发出更新锁,保持到事务事务结束。(更新锁:不阻塞别的事物,允许别的事物读数据(即更新锁可与共享锁兼容),但他确保自上次读取数据后数据没有被更新

READPAST:发出共享锁,但跳过锁定行,它不会被阻塞。适用条件:提交读的隔离级别,行级锁,select语句中。

类型3

ROWLOCK:行级锁

PAGLOCK:页级锁

TABLOCK:表锁

TABLOCKX:表排他锁

讲解完锁后,下面结合一个具体实例,具体看一下锁的使用。

在很多系统中,经常会遇到这种情况,要保持一个编号的唯一,如会计软件中的凭证的编号。一种编号的处理是这样的,把表中的最大编号保存到表中,然后在这个编号上累加,形成新的编号。这个过程对并发处理要求非常高,下面我们就来模拟这个过程,看如何保持编号的唯一性。

新建一张表code来保存凭证的最大编号。字段如下:编号:bh(numeric(18,0)),凭证表名pinzheng(varchar(50))

假设表中有这样的一条记录:

Bh

Pinzheng

18000

会计凭证

新建一个存储过程来生成新的凭证编号,如下:

CREATE PROCEDURE up_getbh AS

Begin Tran

Declare @numnewbh numeric(18,0)

select @numnewbh = bh FROM code WITH (UPDLOCK,ROWLOCK) where pinzheng = '会计凭证'

set @numnewbh = @numnewbh + 1

update code set bh = @numnewbh where pinzheng = '会计凭证'

print @numnewbh

Commit tran

GO

然后,打开查询分析器,并多开几个连接(笔者开了8个连接,模拟有8个人同时并发,读者可以开更多的连接进行试验),把类似以下这样的语句复制到每个连接窗口中,

declare @i numeric(18,0)

set @i = 1

while @i = 1

Begin

if getdate() > '2004-07-22 14:23' --设定一个时间,到此时间同时执行upgetbh存储过程

set @i = 0

end

exec up_getbh

然后,接连运行各个连接,到2004-7-22 1423 这一刻,各个连接同时运行up_getbh。从运行结果可以看出连接顺序出现18001开始个数字,并没有重号或丢号的现象。

分析:由于up_getbh中的select语句使用了更新锁,因更新锁之间不能兼容,所以各个连接要等到所有其他的连接释放掉锁才能执行,而更新锁的释放要等到事务结束,这样就不会发生号出错的现象了。

附:锁的兼容性表

现有的授权模式

请求模式

IS

S

U

IX

SIX

X

意向共享 (IS)

共享 (S)

更新 (U)

意向排它 (IX)

与意向排它共享 (SIX)

排它 (X)



Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=48881

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7724693/viewspace-980861/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-08-14

  • 博文量
    30
  • 访问量
    61340