事务全攻略

一   事务的属性  
   
  事务具有ACID属性  
  即   Atomic原子性,   Consistent一致性,   Isolated隔离性,   Durable永久性  
   
  原子性  
       
        就是事务应作为一个工作单元,事务处理完成，所有的工作要么都在数据库中保存下来，要么完全  
  回滚，全部不保留  
   
   
  一致性  
        事务完成或者撤销后，都应该处于一致的状态  
   
  隔离性  
   
        多个事务同时进行，它们之间应该互不干扰.应该防止一个事务处理其他事务也要修改的数据时，  
  不合理的存取和不完整的读取数据  
   
   
  永久性  
        事务提交以后，所做的工作就被永久的保存下来  
   
   
  二   事务并发处理会产生的问题    
   
  丢失更新  
   
          当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题、  
  每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。  
   
  脏读  
            当第二个事务选择其它事务正在更新的行时，会发生未确认的相关性问题。  
            第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。  
   
  不可重复读  
   
            当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。  
            不一致的分析与未确认的相关性类似，因为其它事务也是正在更改第二个事务正在读取的数据。  
            然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。而且，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都由其它事务更改；因而该行被非重复读取。  
   
  幻像读  
   
              当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题。  
              事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其它事务删除。同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。  
   
   
  三   事务处理类型  
   
   
  自动处理事务  
   
        系统默认每个T－SQL命令都是事务处理     由系统自动开始并提交  
   
   
  隐式事务  
   
          当有大量的DDL   和DML命令执行时会自动开始，并一直保持到用户明确提交为止，切换隐式事务可以用SET   IMPLICIT_TRANSACTIONS  
          为连接设置隐性事务模式.当设置为   ON   时，SET   IMPLICIT_TRANSACTIONS   将连接设置为隐性事务模式。当设置为   OFF   时，则使连接返回到自动提交事务模式  
   
   
  用户定义事务  
   
            由用户来控制事务的开始和结束     命令有:   begin   tran   commit   tran     rollback   tran   命令  
   
   
  分布式事务  
            跨越多个服务器的事务称为分布式事务,sql   server   可以由DTc   microsoft   distributed   transaction   coordinator  
            来支持处理分布式事务,可以使用   BEgin   distributed   transaction   命令启动一个分布式事务处理  
   
   
   
  四     事务处理的隔离级别  
   
  使用SET   TRANSACTION   ISOLATION   LEVEL来控制由连接发出的所有语句的默认事务锁定行为  
   
  从低到高依次是  
   
   
  READ   UNCOMMITTED  
   
  执行脏读或   0   级隔离锁定，这表示不发出共享锁，也不接受排它锁。当设置该选项时，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有语句中的所有表上设置   NOLOCK   相同。这是四个隔离级别中限制最小的级别。  
   
  举例  
   
  设table1(A,B,C)  
  A         B         C  
  a1       b1       c1  
  a2       b2       c2  
  a3       b3       c3  
   
  新建两个连接  
  在第一个连接中执行以下语句  
  select   *   from   table1  
  begin   tran  
  update   table1   set   c='c'  
  select   *   from   table1  
  waitfor   delay   '00:00:10'     --等待10秒  
  rollback   tran  
  select   *   from   table1  
   
  在第二个连接中执行以下语句  
  SET   TRANSACTION   ISOLATION   LEVEL   READ   UNCOMMITTED  
  print   '脏读'  
  select   *   from   table1  
  if   @@rowcount>0  
  begin  
    waitfor   delay   '00:00:10'    
    print   '不重复读'  
    select   *   from   table1  
  end  
   
  第二个连接的结果  
   
  脏读  
  A         B         C  
  a1       b1       c  
  a2       b2       c  
  a3       b3       c  
   
  '不重复读'  
  A         B         C  
  a1       b1       c1  
  a2       b2       c2  
  a3       b3       c3  
   
   
   
  READ   COMMITTED  
   
  指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数据。该选项是   SQL   Server   的默认值。  
   
   
  在第一个连接中执行以下语句  
  SET   TRANSACTION   ISOLATION   LEVEL   READ   COMMITTED  
  begin   tran  
  print   '初始'  
  select   *   from   table1  
  waitfor   delay   '00:00:10'     --等待10秒  
  print   '不重复读'  
  select   *   from   table1  
  rollback   tran  
   
   
  在第二个连接中执行以下语句  
  SET   TRANSACTION   ISOLATION   LEVEL   READ   COMMITTED  
   
  update   table1   set   c='c'  
       
   
  第一个连接的结果  
   
  初始  
  A         B         C  
  a1       b1       c1  
  a2       b2       c2  
  a3       b3       c3  
   
  不重复读  
  A         B         C  
  a1       b1       c  
  a2       b2       c  
  a3       b3       c  
   
   
   
  REPEATABLE   READ  
   
  锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。  
   
   
  在第一个连接中执行以下语句  
  SET   TRANSACTION   ISOLATION   LEVEL   REPEATABLE   READ  
  begin   tran  
  print   '初始'  
  select   *   from   table1  
  waitfor   delay   '00:00:10'     --等待10秒  
  print   '幻像读'  
  select   *   from   table1  
  rollback   tran  
   
   
  在第二个连接中执行以下语句  
  SET   TRANSACTION   ISOLATION   LEVEL   REPEATABLE   READ  
  insert     table1   select   'a4','b4','c4'  
   
   
  第一个连接的结果  
   
  初始  
  A         B         C  
  a1       b1       c1  
  a2       b2       c2  
  a3       b3       c3  
   
  幻像读  
  A         B         C  
  a1       b1       c1  
  a2       b2       c2  
  a3       b3       c3  
  a4       b4       c4  
   
   
  SERIALIZABLE  
   
  在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有   SELECT   语句中的所有表上设置   HOLDLOCK   相同。  
   
   
  在第一个连接中执行以下语句  
  SET   TRANSACTION   ISOLATION   LEVEL   SERIALIZABLE  
  begin   tran  
  print   '初始'  
  select   *   from   table1  
  waitfor   delay   '00:00:10'     --等待10秒  
  print   '没有变化'  
  select   *   from   table1  
  rollback   tran  
   
   
  在第二个连接中执行以下语句  
  SET   TRANSACTION   ISOLATION   LEVEL   SERIALIZABLE  
  insert     table1   select   'a4','b4','c4'  
   
   
  第一个连接的结果  
   
  初始  
  A         B         C  
  a1       b1       c1  
  a2       b2       c2  
  a3       b3       c3  
   
  没有变化  
  A         B         C  
  a1       b1       c1  
  a2       b2       c2  
  a3       b3       c3  
   
   
  五   事务处理嵌套的语法和对@@TRANCOUNT的影响  
   
  BEGIN   TRAN     @@TRANCOUNT＋1  
  COMMIT   TRAN   @@TRANCOUNT－1  
  ROLLBACK   TRAN   使@@TRANCOUNT回归0  
  SAVE   TRAN   不影响@@TRANCOUNT  
   
  举例  
  1)  
   
  SELECT   '事务处理前',   @@TRANCOUNT             --值为   0  
  BEGIN   TRAN  
      SELECT   '第一个事务',   @@TRANCOUNT         --值为   1  
          SELECT   *   FROM   table1  
          BEGIN   TRAN    
                SELECT   '第二个事务',   @@TRANCOUNT     --值为   2  
                    DELETE   table1  
          COMMIT   TRAN  
          SELECT   '递交第二个事务',   @@TRANCOUNT   --值为   1  
  ROLLBACK   TRAN  
  SELECT   '回滚第一个事务',   @@TRANCOUNT   --值为   0  
   
   
  2)  
  SELECT   '事务处理前',   @@TRANCOUNT             --值为   0  
  BEGIN   TRAN    
      SELECT   '第一个事务',   @@TRANCOUNT         --值为   1  
          SELECT   *   FROM   table1  
      SAVE   TRAN   t1  
      SELECT   '保存第一个事务后',   @@TRANCOUNT   --值为   1    
          BEGIN   TRAN    
                SELECT   '第二个事务',   @@TRANCOUNT     --值为   2  
                    DELETE   table1  
        ROLLBACK   TRAN   t1  
          SELECT   '回滚到保存点t1',   @@TRANCOUNT   --注意这里的值为   2    
  IF   @@TRANCOUNT>0  
  ROLLBACK   TRAN  
  SELECT   '处理结束',   @@TRANCOUNT   --为   0  
   
   
  SET   XACT_ABORT  
  控制语句产生运行时错误时，是否自动回滚当前事务  
   
  比如  
   
  SET   XACT_ABORT   ON  
  BEGIN   TRAN  
      SELECT   *   FROM   一个不存在的表  
  ROLL   BACKTRAN  
  PRINT   '处理完毕'     --执行结果没有到这一步  
  go  
  SELECT   @@TRANCOUNT     --值为1   产生孤立事务  
   
   
  六   事务调试语句  
  DBCC   OPENTRAN  
   
  如果在指定数据库内存在最旧的活动事务和最旧的分布和非分布式复制事务，则显示与之相关的信息  
  示例  
  下例获得当前数据库和   pubs   数据库的事务信息。  
   
  --   Display   transaction   information   only   for   the   current   database.  
  DBCC   OPENTRAN  
  GO  
  --   Display   transaction   information   for   the   pubs   database.  
  DBCC   OPENTRAN('pubs')  
  GO  

--------------------------------------------------------------------------------------------------------------------------------------------------------

 

全接触SQL异常与孤立事务！

一、首先从SQLServer中Error讲起，SQL中错误处理有些怪辟   错误级别同是16但结果都不同。  
   
      select   *   from   一个不在的表  
      if   @@error<>0  
          print   '这个没有输出'  
      go  
   
      raiserror('',16,3)  
      if   @@error<>0  
          print   '这个输出了'  
      go  
   
      exec('select   *   from   一个不在的表')  
      if   @@error<>0  
          print   '这个输出了'  
      go  
   
      exec   sp_executesql   N'select   *   from   一个不在的表'  
      if   @@error<>0  
          print   '这个输出了'  
   
  这样你可以发现通过exec或sp_executesql执行可疑的sql，这样就可以在后面捕捉到被异常终止的错误。  
   
   
  二、引出孤立事务：  
      1、孤立事务的产生  
   
          select   @@trancount   当前连接的活动事务数   --当前连接的活动事务数为0  
   
          begin   tran  
   
          select   *   from   一个不在的表  
          if   @@error<>0  
          begin  
              print   '没有执行到这里来!'  
              if   @@trancount<>0   rollback   tran  
          end  
   
          commit   tran  
   
          select   @@trancount   当前连接的活动事务数     --执行后你看看   当前连接的活动事务数为1,且重复执行会每次累加，这是很耗资源的。  
   
      应为rollback根本就没有被回滚。  
   
      2、使用现有手段解决孤立事务  
   
   
          print   @@trancount   print   '当前连接的活动事务数'   --当前连接的活动事务数为0  
   
          if   @@trancount<>0   rollback   tran   --在这里写可以让孤立事务只保持到下次你的过程被调用  
          begin   tran  
   
          select   *   from   一个不在的表  
          if   @@error<>0  
          begin  
              print   '没有执行到这里来!'  
              if   @@trancount<>0   rollback   tran  
          end  
   
          commit   tran  
   
          ---执行后你看看   当前连接的活动事务数为1，但重复执行不会累加  
          print   @@trancount   print   '当前连接的活动事务数'  
   
  三、使用   set   xact_abort   来控制部分违反约束的错误的执行过程  
   
      create   table   Table1   (a   int   check(a>100))  
      go  
   
      set   xact_abort   on  
      begin   tran  
          insert   table1   values(10)  
          print   '这里没有被执行'  
      commit   tran  
      go  
       
      print   ''   print   '=============================================='   print   ''  
       
      set   xact_abort   off  
      begin   tran  
          insert   table1   values(10)  
          print   '这里被执行'  
      commit   tran  
   
      go  
      drop   table   table1  
   
  但   set   xact_abort   对于编译产生的错误确没有起作用，且同样会产生孤立事务  
   
      set   xact_abort   on  
      begin   tran  
          insert     一个不在的表   values(10)  
          print   '这里没有被执行'  
      commit   tran  
      go  
   
      print   ''   print   '=============================================='   print   ''  
   
      set   xact_abort   off  
      begin   tran  
          insert     一个不在的表   values(10)  
          print   '这里没有被执行'  
      commit   tran  
      go  
   
      select   @@trancount   当前连接的活动事务数   ---有两个孤立事务  
      if   @@trancount<>0   rollback   tran  
   
   
  对于sql中怪辟的各种错误，和孤立事务在t-sql编程中一定要注意，小心孤立事务的陷阱，尽量避免浪费或孤立资源，Microsoft公开宣布过SQLServe下一版本Yukon将有内置异常处理语法。那时可以通过代码对无法预料的错误有更好的控制。