SQL优化中的重要概念：事务

时间：2022-04-17 07:52:22

原文: SQL优化中的重要概念：事务

 

sql 优化和事务有关系？

从表面上看，让sql跑的更快，似乎和事务这个概念没什么联系，但是关系数据库中最重要的2个概念就是 关系、事务。

关系，对应到sql中，是通过 主外键以及join 来实现的，当然，没有主外键，照样能关联表。

事务，是数据库提供的，特别是在高并发的情况下，保障数据一致的一种机制。

但实际上，当一个会话在修改数据，而另一个会话又要读取数据时，事务就自动发挥作用了。

通常情况下，似乎这个事务又离我们很远，似乎来无影，去无踪的概念，不过我们可以通过一些系统试图，查询到事务的具体信息，加深对事务的理解。

 

1、隐式事务

隐式事务就是如果不显式的进行 commit或rollback，事务一致存在。

由于默认情况下 sql server是关闭隐式事务的，所以下面在实验中要打开隐式事务。


   
  
    

  1.       /*==================================================================  
    2.  
   
  2.       当以create,drop,  
    3.  
   
  3.        fetch,open,  
    4.  
   
  4.        revoke,grand,  
    5.  
   
  5.        alter table,select,insert,delete,update,truncate table  
    6.  
   
  6.       语句首先执行的时候，SQL Server会话自动打开一个新的事务，  
    7.  
   
  7.       如果在会话中激活了隐式事务模式，那么这个事务会一直保持打开状态，  
    8.  
   
  8.       直到rollback或commit语句这个事务才结束，如果忘记提交事务,  
    9.  
   
  9.       那么在相应的隔离级别下，事务占用的锁可能不会释放，因此尽量不要用隐式事务。  
    10.  
   
  10.       ====================================================================*/  
    11.  
   
  11.        
    12.  
   
  12.       --会话1  
    13.  
   
  13.       set implicit_transactions on  
    14.  
   
  14.        
    15.  
   
  15.       update t  
    16.  
   
  16.       set v = ‘ext12‘  
    17.  
   
  17.        
    18.  
   
  18.       set implicit_transactions off  
    19.  
   
  19.        
    20.  
   
  20.       select @@TRANCOUNT --输出：1，说明事务没有释放  
    21.  
   
  21.       --占用的X独占锁不会释放，会阻塞其他会话  
    22.  
  

  

   
  
    

  1.       --会话2,被会话1阻塞住了，不会返回任何记录  
    2.  
   
  2.       select *  
    3.  
   
  3.       from t  
    4.

在会话1中执行commit来提交事务，那么会话2马上就会返回记录了。

现在把两个会话的执行顺序调换一下：


   
  
    

  1.       --会话1  
    2.  
   
  2.       set implicit_transactions on --打开了隐式事务  
    3.  
   
  3.        
    4.  
   
  4.       select *  
    5.  
   
  5.       from t  
    6.  
   
  6.        
    7.  
   
  7.       set implicit_transactions off  
    8.  
   
  8.        
    9.  
   
  9.       select @@TRANCOUNT --输入：1，说明这个会话中的事务也没有提交  
    10.  
  

  

   
  
    

  1.       --会话2，会话2没有被会话1阻塞，  
    2.  
   
  2.       --之所以这样是因为会话的默认隔离级别是read committed，  
    3.  
   
  3.       --会话1中的事务虽然没有提交，但是select语句在这种隔离级别下，  
    4.  
   
  4.       --运行完就会释放占用的S共享锁，所以不会阻塞写操作  
    5.  
   
  5.       update t  
    6.  
   
  6.       set v = ‘ext‘  
    7.

 

2、显示数据库最早的活动事务


   
  
    

  1.       /*==============================================================  
    2.  
   
  2.       如果事务在数据库中始终打开，有可能会阻塞其他进程的操作,  
    3.  
   
  3.       为什么是有可能而不是一定呢，  
    4.  
   
  4.       原因就是：在默认隔离级别下的select语句查询到数据后就会立即释放共享锁。  
    5.  
   
  5.       另外，日志备份也只会截断不活动事务的那部分日志，所以活动的事务  
    6.  
   
  6.       会导致日志数据越来越多。  
    7.  
   
  7.         
    8.  
   
  8.       为了找到没有提交的事务，可以用下面的命令显示某个数据库最早的活动事务.  
    9.  
   
  9.         
    10.  
   
  10.       不过有个例外，就是下面的命令不会返回：不占用锁资源的未提交事务  
    11.  
   
  11.       ================================================================*/  
    12.  
   
  12.        
    13.  
   
  13.       begin tran --开始显示事务  
    14.  
   
  14.        
    15.  
   
  15.       select *  
    16.  
   
  16.       from t --运行后立即释放共享锁  
    17.  
   
  17.        
    18.  
   
  18.       select @@TRANCOUNT --输入：1，说明没有提交事务  
    19.  
   
  19.        
    20.  
   
  20.       dbcc opentran( ‘wc‘) --显示数据库最早的活动事务，  
    21.  
   
  21.       --但是这儿显示"没有处于打开状态的活动事务"  
    22.

 

3、通过会话来查询事务信息


   
  
    

  1.       --由于上面未提交事务中的select语句在默认的隔离级别下执行后自动释放了共享锁，  
    2.  
   
  2.       --所以dbcc opentran命令并没有返回这个活动事务，  
    3.  
   
  3.       --不过下面的视图解决了这个问题，可以找到所有活动事务。  
    4.  
   
  4.        
    5.  
   
  5.        
    6.  
   
  6.       --找到活动事务  
    7.  
   
  7.       select session_id, --session_id与transaction_id的对应关系  
    8.  
   
  8.       transaction_id,  
    9.  
   
  9.       is_user_transaction,  
    10.  
   
  10.       is_local  
    11.  
   
  11.       from sys.dm_tran_session_transactions --会话中的事务，识别所有打开的事务   
    12.  
   
  12.       where is_user_transaction = 1  
    13.  
   
  13.        
    14.  
   
  14.        
    15.  
   
  15.       --找到活动事务对应的执行语句  
    16.  
   
  16.       select c.session_id, --session_id与connection_id的对应关系  
    17.  
   
  17.       c.connection_id,  
    18.  
   
  18.       c.most_recent_sql_handle,  
    19.  
   
  19.       s.text  
    20.  
   
  20.       from sys.dm_exec_connections c --执行连接，最近执行的查询信息  
    21.  
   
  21.       cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) s  
    22.  
   
  22.       where c.session_id = 361  
    23.  
   
  23.        
    24.  
   
  24.        
    25.  
   
  25.       --活动事务的具体信息  
    26.  
   
  26.       select t.transaction_id,  
    27.  
   
  27.       t.name, --这里显示user_transaction  
    28.  
   
  28.       t.transaction_begin_time,  
    29.  
   
  29.        
    30.  
   
  30.       case t.transaction_type --事务类型  
    31.  
   
  31.       when 1 then ‘读/写事务‘  
    32.  
   
  32.       when 2 then ‘只读事务‘  
    33.  
   
  33.       when 3 then ‘系统事务‘  
    34.  
   
  34.       when 4 then ‘分布式事务‘  
    35.  
   
  35.       end ‘transaction type‘,  
    36.  
   
  36.        
    37.  
   
  37.       case t.transaction_state  
    38.  
   
  38.       when 0 then ‘事务尚未完全初始化‘  
    39.  
   
  39.       when 1 then ‘事务已初始化但尚未启动‘  
    40.  
   
  40.       when 2 then ‘事务处于活动状态‘  
    41.  
   
  41.       when 3 then ‘事务已结束。该状态用于只读事务‘  
    42.  
   
  42.       when 4 then ‘已对分布式事务启动提交进程‘  
    43.  
   
  43.       when 5 then ‘事务处于准备就绪状态且等待解析‘  
    44.  
   
  44.       when 6 then ‘事务已提交‘  
    45.  
   
  45.       when 7 then ‘事务正在被回滚‘  
    46.  
   
  46.       when 8 then ‘事务已回滚‘  
    47.  
   
  47.       end ‘transaction state‘  
    48.  
   
  48.       from sys.dm_tran_active_transactions t --活动的事务  
    49.  
   
  49.       where transaction_id = 150764485  
    50.
SQL优化中的重要概念：事务SQL优化中的重要概念：事务 不想长大啊 发布了416 篇原创文章 · 获赞 135 · 访问量 94万 他的留言板 关注

相关文章