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

 

sql 优化和事务有关系？

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

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

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

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

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

 

1、隐式事务

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

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

/*==================================================================
当以create,drop,
    fetch,open,
    revoke,grand,
    alter table,select,insert,delete,update,truncate table
语句首先执行的时候，SQL Server会话自动打开一个新的事务，
如果在会话中激活了隐式事务模式，那么这个事务会一直保持打开状态，
直到rollback或commit语句这个事务才结束，如果忘记提交事务,
那么在相应的隔离级别下，事务占用的锁可能不会释放，因此尽量不要用隐式事务。
====================================================================*/

--会话1
set implicit_transactions on

update t
set v = 'ext12'

set implicit_transactions off

select @@TRANCOUNT --输出：1，说明事务没有释放
                   --占用的X独占锁不会释放，会阻塞其他会话
  

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

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

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

--会话1
set implicit_transactions on  --打开了隐式事务

select *
from t

set implicit_transactions off

select @@TRANCOUNT  --输入：1，说明这个会话中的事务也没有提交

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

 

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

/*==============================================================
如果事务在数据库中始终打开，有可能会阻塞其他进程的操作,
为什么是有可能而不是一定呢，
原因就是：在默认隔离级别下的select语句查询到数据后就会立即释放共享锁。
另外，日志备份也只会截断不活动事务的那部分日志，所以活动的事务
会导致日志数据越来越多。

为了找到没有提交的事务，可以用下面的命令显示某个数据库最早的活动事务.

不过有个例外，就是下面的命令不会返回：不占用锁资源的未提交事务
================================================================*/

begin tran  --开始显示事务

select *
from t      --运行后立即释放共享锁

select @@TRANCOUNT   --输入：1，说明没有提交事务

dbcc opentran('wc')  --显示数据库最早的活动事务，
                            --但是这儿显示"没有处于打开状态的活动事务"

 

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

--由于上面未提交事务中的select语句在默认的隔离级别下执行后自动释放了共享锁，
--所以dbcc opentran命令并没有返回这个活动事务，
--不过下面的视图解决了这个问题，可以找到所有活动事务。


--找到活动事务
select session_id,                     --session_id与transaction_id的对应关系
       transaction_id,
       is_user_transaction,
       is_local
from sys.dm_tran_session_transactions  --会话中的事务，识别所有打开的事务 
where is_user_transaction =1


--找到活动事务对应的执行语句
select c.session_id,                   --session_id与connection_id的对应关系
       c.connection_id,
       c.most_recent_sql_handle,
       s.text            
from sys.dm_exec_connections  c        --执行连接，最近执行的查询信息
cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) s
where c.session_id = 361   


--活动事务的具体信息
select t.transaction_id,
       t.name,                        --这里显示user_transaction
       t.transaction_begin_time,
       
       case t.transaction_type         --事务类型
		   when 1 then '读/写事务'
		   when 2 then '只读事务'
		   when 3 then '系统事务'
		   when 4 then '分布式事务'
	   end 'transaction type',
	   
	   case t.transaction_state
		   when 0 then '事务尚未完全初始化'
		   when 1 then '事务已初始化但尚未启动'
		   when 2 then '事务处于活动状态'
		   when 3 then '事务已结束。该状态用于只读事务'
		   when 4 then '已对分布式事务启动提交进程'
		   when 5 then '事务处于准备就绪状态且等待解析'
		   when 6 then '事务已提交'
		   when 7 then '事务正在被回滚'
		   when 8 then '事务已回滚'
	   end  'transaction state'
from sys.dm_tran_active_transactions t    --活动的事务
where transaction_id = 150764485