SQL Server中的事务与锁

一、了解事务与锁

事务：保持逻辑数据一致性与可恢复性，必不可少的利器。
锁：多用户访问同一数据库资源时，对访问的先后次序权限管理的一种机制，没有锁，事务或许将会一塌糊涂，不能保证数据的安全正确读写。
死锁：是数据库性能的重量级杀手之一，而死锁却是不同事务之间抢占数据资源造成的。

二、事务概念和分类

事务具有原子性，一致性，隔离性，持久性。

• 原子性：事务必须是一个自动工作的单元，要么全部执行，要么全部不执行。
• 一致性：事务结束的时候，所有的内部数据都是正确的。
• 隔离性：并发多个事务时，各个事务不干涉内部数据，处理的都是另外一个事务处理之前或之后的数据。
• 持久性：事务提交之后，数据是永久性的，不可再回滚。

在SQL Server中事务被分为3类常见的事务：

• 自动提交事务：是SQL Server默认的一种事务模式，每条Sql语句都被看成一个事务进行处理，你应该没有见过，一条Update 修改2个字段的语句，只修该了1个字段而另外一个字段没有修改。。
• 显式事务：T-sql标明，由Begin Transaction开启事务开始，由Commit Transaction 提交事务、Rollback Transaction 回滚事务结束。
• 隐式事务：使用Set IMPLICIT_TRANSACTIONS ON 将将隐式事务模式打开，不用Begin Transaction开启事务，当一个事务结束，这个模式会自动启用下一个事务，只用Commit Transaction 提交事务、Rollback Transaction 回滚事务即可。

三、显示事务的应用

常用语句就四个：

• Begin Transaction：标记事务开始。
• Commit Transaction：事务已经成功执行，数据已经处理妥当。
• Rollback Transaction：数据处理过程中出错，回滚到没有处理之前的数据状态，或回滚到事务内部的保存点。
• Save Transaction：事务内部设置的保存点（还原点），就是事务可以不全部回滚，只回滚到这里，保证事务内部不出错的前提下。

---开启事务begin tran--错误扑捉机制，看好啦，这里也有的。并且可以嵌套。begin try     --语句正确   insert into lives (Eat,Play,Numb) values ('猪肉','足球',1)   --Numb为int类型，出错   insert into lives (Eat,Play,Numb) values ('猪肉','足球','abc')   --语句正确   insert into lives (Eat,Play,Numb) values ('狗肉','篮球',2)end trybegin catch   select Error_number() as ErrorNumber,  --错误代码          Error_severity() as ErrorSeverity,  --错误严重级别，级别小于10 try catch 捕获不到          Error_state() as ErrorState ,  --错误状态码          Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。          Error_line() as ErrorLine,  --发生错误的行号          Error_message() as ErrorMessage  --错误的具体信息   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。end catchif(@@trancount>0)commit tran  --如果成功Lives表中，将会有3条数据。--表本身为空表，ID ,Numb为int 类型，其它为nvarchar类型select * from lives

出现错误，此时全部回滚到原始状态。

---开启事务begin tran--错误扑捉机制，看好啦，这里也有的。并且可以嵌套。begin try       --语句正确   insert into lives (Eat,Play,Numb) values ('猪肉','足球',1)       --加入保存点   save tran pigOneIn   insert into lives (Eat,Play,Numb) values ('猪肉','足球',2)   --语句正确   insert into lives (Eat,Play,Numb) values ('狗肉','篮球',3)end trybegin catch   select Error_number() as ErrorNumber,  --错误代码          Error_severity() as ErrorSeverity,  --错误严重级别，级别小于10 try catch 捕获不到          Error_state() as ErrorState ,  --错误状态码          Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。          Error_line() as ErrorLine,  --发生错误的行号          Error_message() as ErrorMessage  --错误的具体信息   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务      rollback tran   ---由于出错，这里回滚事务到原点，第一条语句也没有插入成功。end catchif(@@trancount>0)rollback tran pigOneIn --直接回滚到保存点，则第一条语句插入成功，后两条语句被回滚了--表本身为空表，ID ,Numb为int 类型，其它为nvarchar类型select * from lives

四、使用set xact_abort on/off 来进行回滚

xact_abort on/off , 指定是否回滚当前事务，为on时，如果当前sql出错，则回滚整个事务，为off时，如果当前sql出从，则只回滚当前sql语句，其他语句正常执行。

需要注意的是：xact_abort只对运行时出现的错误具有回滚作用，如果sql语句存在编译时错误，则xact_abort回滚功能失效。

delete lives  --清空数据set xact_abort offbegin tran     --语句正确   insert into lives (Eat,Play,Numb) values ('猪肉','足球',1)      --Numb为int类型，出错,如果1234..那个大数据换成'132dsaf' xact_abort将失效   insert into lives (Eat,Play,Numb) values ('猪肉','足球',12345646879783213)   --语句正确   insert into lives (Eat,Play,Numb) values ('狗肉','篮球',3)commit transelect * from lives

为on时，结果集为空，因为运行是数据过大溢出出错，回滚整个事务。

五、并发事务产生的问题

在多个用户使用事务同时访问同一个数据资源的时候，会造成一下的事务并发问题：

• 脏读：第一个事务读取第二个事务正在更新的数据表，如果第二个事务还没有更新完成，那么第一个事务读取的数据将是一半为更新过的，一半还没更新过的数据，这样的数据毫无意义
• 丢失更新：多个用户同时对一个数据资源进行更新，必定会产生被覆盖的数据，造成数据读写异常。
• 不可重复读：如果一个用户在一个事务中多次读取一条数据，而另外一个用户则同时更新啦这条数据，造成第一个用户多次读取数据不一致。（update）
• 幻读：第一个事务读取一个结果集后，第二个事务，对这个结果集经行增删操作，然而第一个事务中再次对这个结果集进行查询时，数据发现丢失或新增。(insert or delete)

六、锁---并发事务成败皆归于锁

锁定，就是为解决事务并发问题所生的，它的存在使得一个事务对他自己的数据块进行操作的时候，而另外一个事务则不能插足这些数据块。这就是所谓的锁定。

分类：

从数据库系统的角度分为6种：

• 共享锁（S）：也叫他读锁。使用共享锁使多个用户可以并发地读取数据，但不能修改数据。也就是说当数据资源上存在共享锁时，所有的事物都不能对这个资源进行修改，直到数据读取完成，共享锁自动释放。在SELECT命了执行时，Sql server通常会自动使用共享锁。共享锁的数据页读取完毕后，共享锁会立即释放。
• 排他锁(X):还可以叫他独占锁、写锁。就是如果你对数据资源进行增删改操作时，不允许其它任何事务操作这块资源，直到排它锁被释放，防止同时对同一资源进行多重操作。执行INSERT、UPDATE和DELETE命令时，SQLSERVER会自动使用独占锁。但当对象上有其它锁时，无法加独占锁。独占锁一直到事务结束才能被释放。
• 更新锁（U）：防止出现死锁的锁模式，两个事务对一个数据资源进行先读取再修改的情况下，使用共享锁和排它锁有时会出现死锁现象，而使用更新锁则可以避免死锁的出现。资源的更新锁一次只能分配给一个事务，如果需要对资源进行修改，更新锁会变成排他锁，否则变为共享锁。
• 意向锁：意向锁包括三种类型：意向共享锁（IS），意向排他锁（IX），意向排他共享锁（SIX）。SQL Server需要在层次结构中的底层资源上（如行，列）获取共享锁，排它锁，更新锁。例如表级放置了意向共享锁，就表示事务要对表的页或行上使用共享锁。在表的某一行上放置意向锁，可以防止其它事务获取其它不兼容的的锁。意向锁可以提高性能，因为数据引擎不需要检测资源的每一列每一行，就能判断是否可以获取到该资源的兼容锁。意向锁包括三种类型：意向共享锁（IS），意向排他锁（IX），意向排他共享锁（SIX）。
  
• 架构锁：防止修改表结构时，并发访问的锁。在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)
  
• 大容量更新锁：允许多个线程将大容量数据并发的插入到同一个表中，在加载的同时，不允许其它进程访问该表
  

些锁之间的相互兼容性，也就是，是否可以同时存在。

 

现有的授权模式

 

 

 

 

 

请求的模式

IS

S

U

IX

SIX

X

意向共享 (IS)

是

是

是

是

是

否

共享 (S)

是

是

是

否

否

否

更新 (U)

是

是

否

否

否

否

意向排他 (IX)

是

否

否

是

否

否

意向排他共享 (SIX)

是

否

否

否

否

否

排他 (X)

否

否

否

否

否

否

从程序员角度分为2种锁：

1. 悲观锁（Pessimistic Lock）

      悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。 

2. 乐观锁（Optimistic Lock）

      相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。
而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

七、死锁

这样相互等待对方释放资源，造成资源读写拥挤堵塞的情况，就被称为死锁现象，也叫做阻塞。

例子：

begin tran   update lives set play='羽毛球'  waitfor delay '0:0:5'    update dbo.Earth set Animal='老虎' commit tran

begin tran   update Earth set Animal='老虎'   waitfor  delay '0:0:5' --等待5秒执行下面的语句  update lives set play='羽毛球'commit tran

select * from livesselect * from Earth

第一个事务（称为A）：先更新lives表 --->>停顿5秒---->>更新earth表
第二个事务（称为B）：先更新earth表--->>停顿5秒---->>更新lives表
先执行事务A----5秒之内---执行事务B，出现死锁现象。

过程是这样子的：

1. A更新lives表，请求lives的排他锁，成功。
2. B更新earth表，请求earth的排他锁，成功。
3. 5秒过后
4. A更新earth，请求earth的排它锁，由于B占用着earth的排它锁，等待。
5. B更新lives，请求lives的排它锁，由于A占用着lives的排它锁，等待。

    然而数据库并没有出现无限等待的情况，是因为数据库搜索引擎会定期检测这种状况，一旦发现有情况，立马选择一个事务作为牺牲品。牺牲的事务，将会回滚数据。有点像两个人在过独木桥，两个无脑的人都走在啦独木桥中间，如果不落水，必定要有一个人给退回来。这种相互等待的过程，是一种耗时耗资源的现象，所以能避则避。

    哪个人会被退回来，作为牺牲品，这个我们是可以控制的。控制语法： 

set deadlock_priority  <级别>

死锁处理的优先级别为 low<normal<high，不指定的情况下默认为normal，牺牲品为随机。如果指定，牺牲品为级别低的。
还可以使用数字来处理标识级别：-10到-5为low，-5为normal，-5到10为high。

八、减少死锁的发生，提高数据库性能

死锁耗时耗资源，然而在大型数据库中，高并发带来的死锁是不可避免的，所以我们只能让其变的更少。

1. 按照同一顺序访问数据库资源，上述例子就不会发生死锁啦
2. 保持事务的简短，尽量不要让一个事务处理过于复杂的读写操作。事务过于复杂，占用资源会增多，处理时间增长，容易与其它事务冲突，提升死锁概率。
3. 尽量不要在事务中要求用户响应，比如修改新增数据之后再完成整个事务的提交，这样延长事务占用资源的时间，也会提升死锁概率。
4. 尽量减少数据库的并发量。
5. 尽可能使用分区表，分区视图，把数据放置在不同的磁盘和文件组中，分散访问保存在不同分区的数据，减少因为表中放置锁而造成的其它事务长时间等待。
6. 避免占用时间很长并且关系表复杂的数据操作。
7. 使用较低的隔离级别，使用较低的隔离级别比使用较高的隔离级别持有共享锁的时间更短。这样就减少了锁争用。

可参考：http://msdn.microsoft.com/zh-cn/library/ms191242(v=sql.105).aspx

查看锁活动情况：

--查看锁活动情况select * from sys.dm_tran_locks--查看事务活动情况dbcc opentran

九、事务的隔离级别-解决事务并发问题

所谓事物隔离级别，就是并发事务对同一资源的读取深度层次。分为5种。

1. read uncommitted（读未提交）：这个隔离级别最低啦，可以读取到一个事务未提交的数据，会出现脏读、不可重复读、和幻读（隔离级别最低，并发性最高）。
2. read committed（读已提交）：这个级别是默认选项，保证一个事务修改的数据提交后才能被另一个事务读取，另一个事务不能读取该事务未提交的数据，但可修改。可避免脏读，但会出现不可重复读和幻读。
3. repeatable read（可重复读）：不能读取事务正在处理的数据，也不能修改事务处理数据前的数据。可避免脏读和不可重复读，但会出现幻读。
4. snapshot（快照）：指定事务在开始的时候，就获得了已经提交数据的快照，因此当前事务只能看到事务开始之前对数据所做的修改。
5. serializable（可序列化）：最高事务隔离级别，只能看到事务处理之前的数据。 事务被处理为顺序执行，保证所有的情况不会发生(锁表)。

--语法set tran isolation level <级别>

read uncommitted隔离级别的例子：

begin tran   set deadlock_priority low  update Earth set Animal='老虎'   waitfor  delay '0:0:5' --等待5秒执行下面的语句rollback tran

开另外一个查询窗口执行下面语句

set tran isolation level read uncommittedselect * from Earth  --读取的数据为正在修改的数据 ，脏读waitfor  delay '0:0:5'  --5秒之后数据已经回滚select * from Earth  --回滚之后的数据

read committed隔离级别的例子：

begin tran   update Earth set Animal='老虎'   waitfor  delay '0:0:10' --等待5秒执行下面的语句rollback tran

set tran isolation level read committedselect * from Earth ---获取不到老虎，不能脏读update Earth set Animal='猴子1'   --可以修改waitfor  delay '0:0:10'  --10秒之后上一个事务已经回滚select * from Earth  --修改之后的数据，而不是猴子

十、设置锁超时时间

发生死锁的时候，数据库引擎会自动检测死锁，解决问题，然而这样子是很被动，只能在发生死锁后，等待处理。

--查看超时时间,默认为-1select @@lock_timeout--设置超时时间set lock_timeout 0 --为0时，即为一旦发现资源锁定，立即报错，不在等待，当前事务不回滚，设置时间需谨慎处理后事啊，你hold不住的。