SqlServer——事务—锁与隔离级别

　　隔离实际上是通过锁来实现的，作用于整个事务，它通常在事务开始前指定，如 SET TRANSACTION ISOLATION LEVEL READ Committed，指定后面的事务为 已提交读；而锁是在我们执行某一具体的SQL语句时在from中指定锁模式来实现的，它可以覆盖掉已指定隔离级别下应用的锁类型。隔离级别牺牲并发性来实现一致性。

　　并发:是指在相同的时间，多个用户访问相同的数据。它通常引起以下问题：脏读；丢失更新；不可重复度；幻读；

• 脏读：一个进程读取了另一个进程尚未提交的数据。
• 不可重复读：一个进程先后两次读取的数据不相同，若另一个进程在一个进程两次读取的中间修改了数据。
• 幻读：事务在执行过程中进行了两次相同的查询，第二次查询的结果包含了第一次查询未出现的数据或没有第一次查询出现的数据。这是因为在两次查询的执行过程之间，这是因为事务没有对读取的范围进行锁定造成的，在两次查询之间，另外一个事务执行了插入insert或删除delete。
• 更新丢失（Lost Update）。两个进程读取相同的数据，并进行的修改，一个进程会覆盖另一个进程的修改。或两个事务都同时修改同一数据，但第二个事务因发生错误而回滚，导致两次事务的数据修改都丢失了。这是因为系统没有执行任何锁操作，并发的事务没有进行隔离。

隔离级别

提交读

假设存在表A，如下所示

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

--事务Ⅰ

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 read或Serializable，直到事务结束才释放锁。另说明，如果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))

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

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

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 14：23 这一刻，各个连接同时运行up_getbh。从运行结果可以看出连接顺序出现18001开始个数字，并没有重号或丢号的现象。

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

附：锁的兼容性表