SQL Server中的事物

示例：

set xact_abort off

begin tran

   insert into A(id,name,typeid) values (1,‘小王‘,1)     --语句正确

   insert into A(id,name,typeid) values (2,‘小李‘,12313212313212313)    --算术溢出错误，将插入其他两条。如果这里是‘学生‘， xact_abort将失效，不插入任何数据

   insert into A(id,name,typeid) values (1,‘小张‘,2)     --语句正确

commit tran

select * from A

6.事物并发

在多用户都用事务同时访问同一个数据资源的情况下，就会造成以下几种数据错误。

更新丢失：多个用户同时对一个数据资源进行更新，必定会产生被覆盖的数据，造成数据读写异常。

不可重复读：如果一个用户在一个事务中多次读取一条数据，而另外一个用户则同时更新啦这条数据，造成第一个用户多次读取数据不一致。

脏读：第一个事务读取第二个事务正在更新的数据表，如果第二个事务还没有更新完成，那么第一个事务读取的数据将是一半为更新过的，一半还没更新过的数据，这样的数据毫无意义。

幻读：第一个事务读取一个结果集后，第二个事务，对这个结果集经行增删操作，然而第一个事务中再次对这个结果集进行查询时，数据发现丢失或新增

我们用锁定正在操作的数据，来解决这些问题，当一个事务对一些数据块进行操作的时候，另外一个事务则不能插足这些数据块。

锁定从数据库角度看大致可以分为6种：

共享锁（S）：用于读操作（SELECT），还可以叫它读锁。多个事务可以并发读取数据，但任何事务都不能修改数据，直到数据读取完成，共享锁释放。S锁通常数据被读取完毕，立即被释放。

排它锁（X）：用于写操作（ INSERT、DELETE），还可以叫他独占锁、写锁。仅允许一个事务处理数据，也就是说如果你对数据资源进行增删改的操作时，其它任何事务不允许操作这块资源，直到排它锁被释放，防止同时对同一资源进行多重操作。Ｘ锁一直到事务结束才能被释放。

更新锁（U）：用来预定要对此页施加Ｘ锁，它允许其它事务读，但不允许再施加Ｕ。U锁是为了防止出现死锁模式，当两个事务对一个数据资源进行先读取在修改的情况下，使用共享锁和排它锁有时会出现死锁现象，而使用更新锁则可以避免死锁的出现。资源的更新锁一次只能分配给一个事务，如果需要对资源进行修改，更新锁会变成排他锁，否则变为共享锁。Ｕ锁一直到事务结束时才能被释放。

意向锁：SQL Server需要在层次结构中的底层资源上（如行，列）获取共享锁，排它锁，更新锁。例如表级放置了意 向共享锁，就表示事务要对表的页或行上使用共享锁。在表的某一行上上放置意向锁，可以防止其它事务获取其它不兼容的的锁。意向锁可以提高性能，因为数据引 擎不需要检测资源的每一列每一行，就能判断是否可以获取到该资源的兼容锁。意向锁包括三种类型：意向共享锁（IS），意向排他锁（IX），意向排他共享锁 （SIX）。

架构锁：防止修改表结构时，并发访问的锁。

大容量更新锁：允许多个线程将大容量数据并发的插入到同一个表中，在加载的同时，不允许其它进程访问该表。

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

7.死锁

死锁是指在一组进程中的各个进程均占有不会释放的资源，但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状态。

减少死锁的方法大致有一下几种：

按同一顺序访问对象：并发事务按同一顺序访问对象，则发生死锁的可能性会降低。

保持事务简短：尽量不要让一个事务处理过于复杂的读写操作，事务过于复杂，占用资源会增多，处理时间增长，并发执行事物通常会发生死锁。

避免事务中的用户交互：尽量不要在事务中要求用户响应，因为事务持有的任何锁只有在事务提交或回滚后才会释放，等待用户响应的时间，容易导致阻塞或死锁。

减少并发量及占用时间长的数据操作：尽量减少数据库的并发量，减少事务长时间等待。

使用较低的隔离级别：使用较低的隔离级别比使用较高的隔离级别持有共享锁的时间更短。这样就减少了锁争用。注意：先确定事务是否能在较低的隔离级别上运行。

使用基于行版本控制的隔离级别：如果将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON，则在已提交读隔离级别下运行的事务在读操作期间将使用行版本控制而不是共享锁。

8.为事务设置隔离级别

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

read uncommitted：这个隔离级别最低啦，可以读取到一个事务正在处理的数据，但事务还未提交，这种级别的读取叫做脏读。

read committed：这个级别是默认选项，不能脏读，不能读取事务正在处理没有提交的数据，但能修改。

repeatable read：不能读取事务正在处理的数据，也不能修改事务处理数据前的数据。

snapshot：指定事务在开始的时候，就获得了已经提交数据的快照，因此当前事务只能看到事务开始之前对数据所做的修改。

serializable：最高事务隔离级别，只能看到事务处理之前的数据。

语法

set tran isolation level <级别>

示例1:read uncommitted

begin tran
  set deadlock_priority low
  update A set name=‘小王1‘ where id=1 --原数据小王
  waitfor  delay ‘0:0:5‘ --等待5秒执行下面的语句
rollback tran

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

得到 name=‘小王‘。

示例2:read committed
begin tran
  update A set name=‘小王‘
  waitfor  delay ‘0:0:10‘ --等待10秒执行下面的语句
rollback tran

set tran isolation level read committed
select * from A --获取不到A，不能脏读
update A set name=‘小王2‘  where id=1 --可以修改
waitfor  delay ‘0:0:10‘  --10秒之后上一个事务已经回滚
select * from A--修改之后的数据，而不是A

得到 name=‘小王2‘。

SQL Server中的事物

标签：