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

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

2、d) 否 否 是 查询期间，不允许其他爭务update 可串行读(serializable) 否 否 否 查询期间，不允许其他爭务insert或delete 提交读 假设存在表A,如下所示 Al A2 A3 11 21 31 12 22 32 打开查询分析器并打开两个连接，分别输入如下两个事务： 一事务I SET TRANSACTION ISOLATION LEVEL READ Committed begin tran update A set A2 = 20 where Al = 11 waitfor delay 00:00:10 rollback tran 一事务II SET TRANSA

3、CTION ISOLATION LEVEL READ Committed select * from A where Al = 11 如果先运行事务I,然后紧接着运行事务II，则事务II要等待10秒钟（一个连接在修改数据 块时别的连接也不能査询这个数据块.直到解锁反之亦然：读的时候不能写和修改）。 如果把事务II改为如下 SET TRANSACTION ISOLATION LEVEL READ UNCommitted select * from A where Al = 11 那么事务II不需等待，立即执行（可以看出READ UNCommitted事务select不对数据发出共亨锁） 锁：（这

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

5、锁定的数据不同。 根据以上分析，我们总结为六个字为“共享读，排他写”。 了解了锁的情况之后，又涉及到一个问题。事务究竟要保持锁多久呢？ 一般来说，共享锁的锁龙时间与事务的隔离级别有关，如果隔离级别为Read Committed 的默认级别，只在读取(select)的期间保持锁定，即在查询出数据以后就释放了锁;如果隔 离级别为更髙的Repeatable read或Serializable,直到事务结束才释放锁 另说明，如 果select语句中指上了 HoldLock提示，则也要等到事务结束才释放锁 排他锁直到事务结朿才释放。 做岀了以上分析，现在我们可能会存在这样的疑问，到底在执行SQL语句的时

6、候发出什 么样的锁呢，这就由事务的隔离级别决左了。一般情况，读语句(select)发出共享锁，写语 句(update, insert, delete)发出排他锁。但是，如果这样不能满足我们的要求怎么办呢，有 没有更多选择呢，别急，SQLServer为我们提供了锁宦提示的概念。 锁立提示对SQL语句进行特别指定，这个指定将覆盖事务的隔离级别。下而 对各个锁定提示分别予以介绍(更多资料请査看SQLServer的联机帮助)，笔者做岀了以下 分类。 类型1 READUNCOMMITTED：不发出锁 READCOMMITTED：发出共享锁.保持到读取结束 REPEATABLEREAD：发出共享锁.保持到

7、事务结束 SERIALIZABLE：发岀共享锁,保持到事务结束 类型2 N0L0CK：不发岀锁 等同于READUNCOMMITTED HOLDLOCK：发岀共享锁.保持到事务结束。等同于SERIALIZABLE XL0CK：发岀排他锁，保持到事务结束。 UPDL0CK：发出更新锁保持到事务事务结束。(更新锁：不阻塞别的M物，允许别的 事物读数据(即更新锁町与共享锁兼容)，但他确保11上次读取数据后数据没有被更新) READPAST：发出共享锁，但跳过锁左行，它不会被阻塞。适用条件：提交读的隔离级别, 行级锁,select语句中。 类型3 ROWLOCK：行级锁 PAGLOCK：页级锁 TABL

8、OCK：表锁 TABLOCKX：表排他锁 讲解完锁后，下而结合一个具体实例，具体看一 1、锁的使用。 在很多系统中，经常会遇到这种情况，要保持一个编号的唯一，如会计软件 中的凭证的编号。一种编号的处理是这样的，把表中的最大编号保存到表中，然后在这个编 号上累加，形成新的编号。这个过程对并发处理要求非常高，下而我们就来模拟这个过程, 看如何保持编号的唯一性。 新建一张表code来保存凭证的最大编号。字段如下：编 号:bh (numeric (18, 0),凭证表名 pinzheng (varchar (50) 假设表中有这样的一条记录： Bh Pinzheng 18000 会计凭证 新建一个存储

9、过程来生成新的凭证编号，如下： 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个

10、连接，模拟有8个人同时并发，读 者可以开更多的连接进行试验)，把类似以下这样的语句复制到每个连接窗口中， declare i numeric(18 0) set i = 1 while i = 1 Begin if getdateO 2004-07-22 14:23设定一个时间,到此时间同时 执行upgetbh存储过程 set i = 0 end exec up_getbh 然后，接连运行各个连接，到2004-7-22 14： 23这一刻，各个连接同时运行up_getbho从 运行结果可以看岀连接顺序出现18001开始个数字，并没有重号或丢号的现象。 分析：由于up_getbh中的select语句使用了更新锁，因更新锁之间不能兼容，所以各个连 接要等到所有其他的连接释放抻锁才能执行，而