SQL Server学习笔记之事务、锁定、阻塞、死锁用法详解

/*==================================================================当以create,drop, fetch,open, revoke,grand, alter table,select,insert,delete,update,truncate table语句首先执行的时候，SQL Server会话自动打开一个新的事务，如果在会话中激活了隐式事务模式，那么这个事务会一直保持打开状态，直到rollback或commit语句这个事务才结束，如果忘记提交事务,那么在相应的隔离级别下，事务占用的锁可能不会释放，因此尽量不要用隐式事务。====================================================================*/--会话1set implicit_transactions onupdate tset v = 'ext12'set implicit_transactions offselect @@TRANCOUNT --输出：1，说明事务没有释放     --占用的X独占锁不会释放，会阻塞其他会话

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

--会话1set implicit_transactions on --打开了隐式事务select *from tset implicit_transactions offselect @@TRANCOUNT --输入：1，说明这个会话中的事务也没有提交

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

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

--由于上面未提交事务中的select语句在默认的隔离级别下执行后自动释放了共享锁，--所以dbcc opentran命令并没有返回这个活动事务，--不过下面的视图解决了这个问题，可以找到所有活动事务。--找到活动事务select session_id,      --session_id与transaction_id的对应关系  transaction_id,  is_user_transaction,  is_localfrom 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.textfrom sys.dm_exec_connections c  --执行连接，最近执行的查询信息cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) swhere 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

DB(数据库) Metadata(系统元数据) Object(数据库对象:视图,函数,存储过程,触发器) Table(表)  Hobt(堆或B树)   Allocation Unit(按照数据的类型(数据,行溢出、大对象)分组的相关页面)    Extent(8个8KB的页面)     Page(8KB数据页面)      Rid(行标示符对应一个堆表的行)      Key(键范围上的锁、B树中的键)FileApplication

select resource_type,     --资源类型  resource_database_id,   --资源所在的数据库id  resource_associated_entity_id, --数据库中与资源相关联的实体的 ID。          --该值可以是对象ID、Hobt ID 或分配单元 ID，          --具体视资源类型而定  object_name(resource_associated_entity_id,resource_database_id),  resource_lock_partition, --已分区锁资源的锁分区ID。对于未分区锁资源值为 0  resource_description, --资源的说明，其中只包含从其他资源列中无法获取的信息  request_session_id, --请求资源的会话  request_type,   --请求类型，该值为 LOCK  request_mode,  --请求的模式,对于已授予的请求，为已授予模式,       --对于等待请求，为正在请求的模式(锁定模式)  request_status   --请求的当前状态，        --可能值为 GRANTED、CONVERT 或 WAITfrom sys.dm_tran_locksWHERE request_session_id = 361

--默认值，不管是不是分区表，会在表级别启用锁升级ALTER TABLE tSET (lock_escalation = TABLE)--当表升级时，如果表已经分区，会在分区级别启用锁升级ALTER TABLE tSET (lock_escalation = auto)--在表级别禁用锁升级，如果用了TabLock提示或在Serializable隔离级别下查询，还是会有表锁ALTER TABLE tSET (lock_escalation = disable)

--会话1，修改数据，但没有提交事务BEGIN TRANselect @@SPID --输出：287UPDATE tSET v = '88888'WHERE idd = 1--会话2，由于会话一事务没有提交，导致阻塞BEGIN TRANselect @@SPID --输出：105UPDATE tSET v = '888'WHERE idd = 1--查询会话1的等待信息select session_id,   --查询的会话，也就是被阻塞的会话  wait_duration_ms,  --等待毫秒数  wait_type,   --等待类型，如：LCK_M_X表示正在等待获取排他锁  blocking_session_id --阻塞session_id会话的会话from sys.dm_os_waiting_taskswhere session_id = 105--查询这个被阻塞的会话请求的资源情况select resource_type,  request_status,  request_mode,  request_session_idfrom sys.dm_tran_lockswhere request_session_id = 105--说明会话2在update时一共获取了4个锁，共享数据库锁、2个意向独占锁(锁定表、数据页)，--一个键锁锁住那条要更新的记录，只有这个键锁的请求状态时wait，--其他3个锁状态为grant表示已经会话2已经获得了锁。--另一种查看阻塞会话的方法：--查看当前会话的执行请求select session_id,  status,  blocking_session_id,  wait_type,  wait_timefrom sys.dm_exec_requestswhere session_id = 105--配置语句等待锁释放的时间--设置语句的锁请求超时时段--超时时段是以毫秒为单位，超时后会返回锁定错误返回错误：(1 行受影响)消息 1222，级别 16，状态 51，第 7 行已超过了锁请求超时时段。语句已终止。

set lock_timeout 1000--跟踪死锁--会话1set transaction isolation level serializablebegin tranupdate tset v ='563'where idd =2waitfor delay '00:00:10'update tset v = '963'where idd =1commit--会话2set transaction isolation level serializablebegin tranupdate tset v ='234'where idd =1waitfor delay '00:00:10'update tset v = '987'where idd=2commit

/*===================================================================开启跟踪标志位：    DBCC TRACEON(trace#[,...n],-1) [With No_InfoMsgs]检查某种或某些标志位是开启，还是关闭：    DBCC TRACESTATUS(trace#[,...n],-1) [With No_InfoMsgs]1.trace#:指定一个或多个需要开启或需要检查状态的跟踪标志位数字2. -1:如果指定了-1，则以全局方式打开某种或某些跟踪标志位3.with No_InfoMsgs:当命令中包含此参数时，则禁止DBCC输出信息性消息=====================================================================*/--跟踪1222能把详细的死锁信息返回到SQL Server的日志中--标志位-1表示跟踪标志位1222应该对所有SQL Server连接全局启用DBCC TraceOn(1222,-1)go--验证标志位是否启动DBCC TraceStatusgo--关闭标志位DBCC TraceOff(1222,-1)go设置死锁优先级--设置死锁的优先级,调整一个查询会话由于死锁而被终止运行的可能性SET DeadLock_Priority Low | Normal | High | numeric-priority--是当前连接很有可能被终止运行set deadlock_priority Low--SQL Server终止回滚代价较小的连接set deadlock_priority Normal--减少连接被终止的可能性，除非另一个连接也是High或数值优先级大于5set deadlock_priority High--数值优先级：-10到10的值，-10最有可能被终止运行，10最不可能被终止运行，--两个数字谁大，谁就越不可能在死锁中被终止set deadlock_priority 10