SQL Server学习笔记之事务、锁定、阻塞、死锁用法详解
本文实例讲述了SQLServer学习笔记之事务、锁定、阻塞、死锁用法。分享给大家供大家参考，具体如下：
1、事务
隐式事务
/*================================================================== 当以create,drop, fetch,open, revoke,grand, altertable,select,insert,delete,update,truncatetable 语句首先执行的时候，SQLServer会话自动打开一个新的事务， 如果在会话中激活了隐式事务模式，那么这个事务会一直保持打开状态， 直到rollback或commit语句这个事务才结束，如果忘记提交事务, 那么在相应的隔离级别下，事务占用的锁可能不会释放，因此尽量不要用隐式事务。 ====================================================================*/ --会话1 setimplicit_transactionson updatet setv='ext12' setimplicit_transactionsoff select@@TRANCOUNT--输出：1，说明事务没有释放 --占用的X独占锁不会释放，会阻塞其他会话
--会话2,被会话1阻塞住了，不会返回任何记录 select* fromt
在会话1中执行commit来提交事务，那么会话2马上就会返回记录了。
现在把两个会话的执行顺序调换一下：
--会话1 setimplicit_transactionson--打开了隐式事务 select* fromt setimplicit_transactionsoff select@@TRANCOUNT--输入：1，说明这个会话中的事务也没有提交
--会话2，会话2没有被会话1阻塞， --之所以这样是因为会话的默认隔离级别是readcommitted， --会话1中的事务虽然没有提交，但是select语句在这种隔离级别下， --运行完就会释放占用的S共享锁，所以不会阻塞写操作 updatet setv='ext'
显示数据库最早的活动事务
/*==============================================================
如果事务在数据库中始终打开，有可能会阻塞其他进程的操作,
为什么是有可能而不是一定呢，
原因就是：在默认隔离级别下的select语句查询到数据后就会立即释放共享锁。
另外，日志备份也只会截断不活动事务的那部分日志，所以活动的事务
会导致日志数据越来越多。
为了找到没有提交的事务，可以用下面的命令显示某个数据库最早的活动事务.
不过有个例外，就是下面的命令不会返回：不占用锁资源的未提交事务
================================================================*/
begintran--开始显示事务
select*
fromt--运行后立即释放共享锁
select@@TRANCOUNT--输入：1，说明没有提交事务
dbccopentran('wc')--显示数据库最早的活动事务，
--但是这儿显示"没有处于打开状态的活动事务"
通过会话来查询事务信息
--由于上面未提交事务中的select语句在默认的隔离级别下执行后自动释放了共享锁， --所以dbccopentran命令并没有返回这个活动事务， --不过下面的视图解决了这个问题，可以找到所有活动事务。 --找到活动事务 selectsession_id,--session_id与transaction_id的对应关系 transaction_id, is_user_transaction, is_local fromsys.dm_tran_session_transactions--会话中的事务，识别所有打开的事务 whereis_user_transaction=1 --找到活动事务对应的执行语句 selectc.session_id,--session_id与connection_id的对应关系 c.connection_id, c.most_recent_sql_handle, s.text fromsys.dm_exec_connectionsc--执行连接，最近执行的查询信息 crossapplysys.dm_exec_sql_text(c.most_recent_sql_handle)s wherec.session_id=361 --活动事务的具体信息 selectt.transaction_id, t.name,--这里显示user_transaction t.transaction_begin_time, caset.transaction_type--事务类型 when1then'读/写事务' when2then'只读事务' when3then'系统事务' when4then'分布式事务' end'transactiontype', caset.transaction_state when0then'事务尚未完全初始化' when1then'事务已初始化但尚未启动' when2then'事务处于活动状态' when3then'事务已结束。该状态用于只读事务' when4then'已对分布式事务启动提交进程' when5then'事务处于准备就绪状态且等待解析' when6then'事务已提交' when7then'事务正在被回滚' when8then'事务已回滚' end'transactionstate' fromsys.dm_tran_active_transactionst--活动的事务 wheretransaction_id=150764485
2、锁定
当一个用户要读取另一个用户正在修改的数据，或者一个用户正在修改另一个用户正在读取的数据，或者一个用户要修改另一个用户正在修改的数据，就会出现并发问题。锁定能防止并发问题。
资源的锁定方式称为锁定模式，SQLServer中的锁定模式：共享锁，意向锁，更新锁，排他锁，架构稳定锁，架构修改锁，大批量更新锁，键范围锁。不是所有锁模式都是兼容的，如：一个加了排他锁的资源不能再加其他锁，其他事务必须等待，直到释放排他锁。
可以锁定SQLServer中的各类对象，可以锁定的资源在粒度上差异很大，从细粒度(行、键)到粗粒度(数据库)。细粒度的锁允许用户能查询那些未被锁定的行，并发性更高，但是需要更多的锁资源(每个被锁定的行都需要一个锁资源)；粗粒度的锁降低了并发性，但需要的锁资源很少。
在SQLServer中可锁定的资源：
DB(数据库) Metadata(系统元数据) Object(数据库对象:视图,函数,存储过程,触发器) Table(表) Hobt(堆或B树) AllocationUnit(按照数据的类型(数据,行溢出、大对象)分组的相关页面) Extent(8个8KB的页面) Page(8KB数据页面) Rid(行标示符对应一个堆表的行) Key(键范围上的锁、B树中的键) File Application
查看锁的活动
selectresource_type,--资源类型 resource_database_id,--资源所在的数据库id resource_associated_entity_id,--数据库中与资源相关联的实体的ID。 --该值可以是对象ID、HobtID或分配单元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或WAIT fromsys.dm_tran_locks WHERErequest_session_id=361
控制表的锁升级
每个锁都会消耗内存资源，当锁的数量增加时，那么所需要的内存就会增加，而系统内可用的内存就会减少。如果锁占用的内存比率超过一个阀值，SQLServer会将细粒度锁（行锁）升级为粗粒度锁（表锁），这个过程就是锁升级。
锁升级的优点是可以减少锁的数量，相应的减少内存的使用量，而缺点是由于锁住了更大的资源，所以会导致阻塞，降低并发性。
--默认值，不管是不是分区表，会在表级别启用锁升级 ALTERTABLEt SET(lock_escalation=TABLE) --当表升级时，如果表已经分区，会在分区级别启用锁升级 ALTERTABLEt SET(lock_escalation=auto) --在表级别禁用锁升级，如果用了TabLock提示或在Serializable隔离级别下查询，还是会有表锁 ALTERTABLEt SET(lock_escalation=disable)
影响锁定的除了上面提到的锁定模式、锁的粒度，还有就是事务的隔离级别。
所谓隔离级别其实就是事务与事务之间相互影响的程度，比如，一个事务修改了数据，那么其他事务是否能看到这些修改的数据，无论事务是否提交。对于最高的隔离级别，这个事务所做的修改，其他任何事务都看不到；而最低的隔离级别，这个事务所做的修改，可以被其他任何事务看到。
SQLServer隔离级别：
1.readuncommitted能解决丢失更新的问题，但是会导致脏读。
2.readcommitted读取的是已提交的数据，所以解决了脏读的问题，但是会有不可重复读取的问题，也就是在一个事务中有两次读取，第一次读取的和第二次读取的同一条数据，可能值是不同的，因为在事务中的select语句在读取完之后就立即释放的共享锁，而此时有另一个事务把刚才第一个事务读取的那条数据修改了，这样第一次读和第二次读到的值就会不同。
3.repeatableread解决了不可重复读取的问题，也就是在一个事务中的前后两次读取，读取到的数据值是一样的，但是会有幻读的可能，也就是第一次读出的数据确实和第二次读取的数据一样，但是第二次读取的记录条数可能多于第一次读取的记录条数，因为在读取的时候确实是锁住了被读取的记录，但是这个表可能添加了新的记录。
4.serializable通过锁住查询范围内的键、键与键之间的范围来解决幻读的问题，比如whereid>=5andid<=10,加入表表中只有id为7,9的两条记录，那么5-6、7-8、9-10这3个范围都会被锁住。
5.在ALLOW_SNAPSHOT_ISOLATION下的snapshot这种隔离级别允许读取事务一致性版本的数据，但可能不是最新的版本，也就是说在一个事务中只能读到某个版本，比如，在一个事务中有两次读取，第一次读完后，数据被另一个事务修改且事务提交了，此时进行第2次读取，那么读出来的还是和第一次读取一样的数据，这就是在一个事务中如果数据被其他事务修改了，读出来的数据也一样。优点是数据读取不会阻塞写，写也不会阻塞读取。另外，如果两个事务同时修改同一行数据，会导致更新冲突错误。
6.在READ_COMMITTED_SNAPSHOT下的readcommitted隔离级别允许在同一事务中总是能读取运行的已提交的数据，而且数据读取不会阻塞写，写也不会阻塞读取，也不会导致更新冲突。
上面是关于锁定的概念，那么接下来就是如何找到阻塞的进程，并解决阻塞问题。
--会话1，修改数据，但没有提交事务 BEGINTRAN select@@SPID--输出：287 UPDATEt SETv='88888' WHEREidd=1 --会话2，由于会话一事务没有提交，导致阻塞 BEGINTRAN select@@SPID--输出：105 UPDATEt SETv='888' WHEREidd=1 --查询会话1的等待信息 selectsession_id,--查询的会话，也就是被阻塞的会话 wait_duration_ms,--等待毫秒数 wait_type,--等待类型，如：LCK_M_X表示正在等待获取排他锁 blocking_session_id--阻塞session_id会话的会话 fromsys.dm_os_waiting_tasks wheresession_id=105 --查询这个被阻塞的会话请求的资源情况 selectresource_type, request_status, request_mode, request_session_id fromsys.dm_tran_locks whererequest_session_id=105 --说明会话2在update时一共获取了4个锁，共享数据库锁、2个意向独占锁(锁定表、数据页)， --一个键锁锁住那条要更新的记录，只有这个键锁的请求状态时wait， --其他3个锁状态为grant表示已经会话2已经获得了锁。 --另一种查看阻塞会话的方法：--查看当前会话的执行请求 selectsession_id, status, blocking_session_id, wait_type, wait_time fromsys.dm_exec_requests wheresession_id=105 --配置语句等待锁释放的时间 --设置语句的锁请求超时时段 --超时时段是以毫秒为单位，超时后会返回锁定错误返回错误：(1行受影响)消息1222，级别16，状态51，第7行已超过了锁请求超时时段。语句已终止。
3、死锁
当两个事务分别锁定了资源，而又继续请求对方已获取的资源，那么就会产生死锁。
发生死锁的原因：
A、会话以不同的顺序访问表。
B、会话长时间运行事务，在一个事务中更新了很多表或行，这样增加了冲突的可能。
C、会话1申请了一些行锁，会话2申请了一些行锁，之后决定将其升级为表锁。
如果这些行在相同的数据页面中，并且两个会话同时在相同的页面上升级锁粒度，就会产生死锁。
setlock_timeout1000 --跟踪死锁--会话1 settransactionisolationlevelserializable begintran updatet setv='563' whereidd=2 waitfordelay'00:00:10' updatet setv='963' whereidd=1commit--会话2 settransactionisolationlevelserializable begintran updatet setv='234' whereidd=1 waitfordelay'00:00:10' updatet setv='987' whereidd=2 commit
再开启一个会话，开启跟踪：
/*=================================================================== 开启跟踪标志位： DBCCTRACEON(trace#[,...n],-1)[WithNo_InfoMsgs] 检查某种或某些标志位是开启，还是关闭： DBCCTRACESTATUS(trace#[,...n],-1)[WithNo_InfoMsgs] 1.trace#:指定一个或多个需要开启或需要检查状态的跟踪标志位数字 2.-1:如果指定了-1，则以全局方式打开某种或某些跟踪标志位 3.withNo_InfoMsgs:当命令中包含此参数时，则禁止DBCC输出信息性消息 =====================================================================*/ --跟踪1222能把详细的死锁信息返回到SQLServer的日志中 --标志位-1表示跟踪标志位1222应该对所有SQLServer连接全局启用 DBCCTraceOn(1222,-1) go --验证标志位是否启动 DBCCTraceStatus go --关闭标志位 DBCCTraceOff(1222,-1) go 设置死锁优先级--设置死锁的优先级,调整一个查询会话由于死锁而被终止运行的可能性 SETDeadLock_PriorityLow|Normal|High|numeric-priority --是当前连接很有可能被终止运行 setdeadlock_priorityLow --SQLServer终止回滚代价较小的连接 setdeadlock_priorityNormal --减少连接被终止的可能性，除非另一个连接也是High或数值优先级大于5 setdeadlock_priorityHigh --数值优先级：-10到10的值，-10最有可能被终止运行，10最不可能被终止运行， --两个数字谁大，谁就越不可能在死锁中被终止 setdeadlock_priority10
希望本文所述对大家SQLServer数据库程序设计有所帮助。