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

《SQLserver锁和事务隔离级别的比较与使用》由会员分享，可在线阅读，更多相关《SQLserver锁和事务隔离级别的比较与使用（3页珍藏版）》请在装配图网上搜索。

1、SQLServer锁和事务隔离级别的比较与使用对象 锁：每条SQL语句 隔离：事务锁并发问题丢失更新未确认的读取（脏读）不一致的分析（非重复读）：多次读取相同的数据（行）不一致（其他用户更改update） 幻像读：多次读取有不存在和新增的数据（其他用户插入insert或删除delete）隔离级别隔离级别脏读不可重幻像说明复读取未提交读(read uncommitted)是是是如果其他事务更新，不管是否提交，立即执行提交读（read committed默认）否是是读取提交过的数据。如果其他事务更新没提交，则等待可重复读(repeatable read)否否是查询期间，不允许其他事务update可

2、串行读(serializable)否否否查询期间，不允许其他事务insert或delete提交读假设存在表A,如下所示A1A2A3112131122232打开查询分析器并打开两个连接，分别输入如下两个事务:事务ISET TRANSACTION ISOLATION LEVEL READ Committed begin tran update A set A2 = 20 where A1 = 11 waitfor delay 00:00:10 rollback tran 事务IISET TRANSACTION ISOLATION LEVEL READ Committed select * from

3、 A where A1 = 11如果先运行事务I,然后紧接着运行事务II,则事务II要等待10秒钟（一个连接在修改数据 块时别的连接也不能查询这个数据块，直到解锁。反之亦然：读的时候不能写和修改）。如果把事务II改为如下SET TRANSACTION ISOLATION LEVEL READ UNCommitted select * from A where A1 = 11那么事务II不需等待，立即执彳丁（可以看出READ UNCommitted事务select不对数据发出共享锁 锁：（这里主要讲解共享锁和排他锁两种经常用到的锁）共享锁主要是为了共享读（select），如果存在事务（一个或多个

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

5、务究竟要保持锁多久呢？一般来说，共享锁的锁定时间与事务的隔离级别有关，如果隔离级别为Read Committed 的默认级别，只在读取(select)的期间保持锁定，即在查询出数据以后就释放了锁；如果隔 离级别为更高的Repeatable read或Serializable，直到事务结束才释放锁。另说明，如 果select语句中指定了 HoldLock提示，则也要等到事务结束才释放锁。排他锁直到事务结束才释放。做出了以上分析，现在我们可能会存在这样的疑问，到底在执行SQL语句的时候发出什 么样的锁呢，这就由事务的隔离级别决定了。一般情况，读语句(select)发出共享锁，写语 句(update

6、,insert,delete)发出排他锁。但是，如果这样不能满足我们的要求怎么办呢，有 没有更多选择呢，别急，SQLserver为我们提供了锁定提示的概念。锁定提示对SQL语句进行特别指定，这个指定将覆盖事务的隔离级别。下面对各个 锁定提示分别予以介绍(更多资料请查看SQLserver的联机帮助)，笔者做出了以下分类。 类型1 READUNCOMMITTED :不发出锁 READCOMMITTED:发出共享锁，保持到读取结束 REPEATABLEREAD:发出共享锁，保持到事务结束 SERIALIZABLE:发出共享锁，保持到事务结束 类型2 NOLOCK:不发出锁。等同于READUNCOMM

7、ITTED HOLDLOCK:发出共享锁，保持到事务结束。等同于SERIALIZABLE XLOCK：发出排他锁，保持到事务结束。 UPDLOCK:发出更新锁，保持到事务事务结束。(更新锁：不阻塞别的事物，允许别的 事物读数据(即更新锁可与共享锁兼容)，但他确保自上次读取数据后数据没有被更新 READPAST：发出共享锁，但跳过锁定行，它不会被阻塞。适用条件：提交读的隔离级别 行级锁，select语句中。类型3 ROWLOCK:行级锁 PAGLOCK:页级锁 TABLOCK :表锁 TABLOCKX：表排他锁讲解完锁后，下面结合一个具体实例，具体看一下锁的使用。在很多系统中，经常会遇到这种情况

8、，要保持一个编号的唯一，如会计软件中的凭证的编号。一种编号的处理是这样的，把表中的最大编号保存到表中，然后在这个编号上累 加，形成新的编号。这个过程对并发处理要求非常高，下面我们就来模拟这个过程，看如何 保持编号的唯一性。新建一张表code来保存凭证的最大编号。字段如下：编号:bh(numeric(18,0),凭证表名 pinzheng(varchar(50) 假设表中有这样的一条记录：BhPinzheng新建一个存储过程来生成新的凭证编号，如下：CREATE PROCEDURE up_getbh ASBegin TranDeclare numnewbh numeric(18,0)select

9、 numnewbh = bh FROM code WITH (UPDLOCK,ROWLOCK) where pinzheng =会计凭证set numnewbh = numnewbh + 1update code set bh = numnewbh where pinzheng =会计凭证 print numnewbhCommit tranGO然后，打开查询分析器，并多开几个连接(笔者开了8个连接，模拟有8个人同时并发，读 者可以开更多的连接进行试验)，把类似以下这样的语句复制到每个连接窗口中， declare i numeric(18,0) set i = 1while i = 1Begin

10、if getdate() 2004-07-22 14:23-设定一个时间，到此时间同时执行upgetbh 存储过程set i = 0endexec up_getbh然后，接连运行各个连接，到2004-7-22 14： 23这一刻，各个连接同时运行up_getbh。从 运行结果可以看出连接顺序出现18001开始个数字，并没有重号或丢号的现象。分析：由于up_getbh中的select语句使用了更新锁，因更新锁之间不能兼容，所以各个连 接要等到所有其他的连接释放掉锁才能执行，而更新锁的释放要等到事务结束，这样就不会 发生号出错的现象了。附：锁的兼容性表现有的授权模式请求模式ISS现有的授眼模式UIISII意向摆享as)是是是皆.是否共享cn是是是否否否更新an是是否否否否意向捧它ai)是否否是否否与意向拷它共享(SH)是否否否否否推它a)否否否否否否