SQL SERVER 阻塞与死锁.ppt

SQLServer2008新特征 阻塞与死锁 DBA 内容 DBA 事务 事务 单个工作单元执行的一些列操作 事务的属性 A 原子性 Atomicity 全做或全不做C 一致性 Consistency 多个事务对data访问间不冲突I 隔离性 Isolation 用锁进行隔离D 持久性 Durability 运行模式 自动提交事务 每个语句一个事务显示事务BEGINTRANCOMMITTRANROLLBACKTRAN隐示事务 DBA 并发 并发 多用户同时对一种资源进行访问并发影响 并发控制 A的修改不会对B做的修改产生负面影响悲观并发控制 加锁成本rollback隔离级别 DBA 隔离级别 定义一个事务必须与其他事务所进行的资源或数据更改相隔离的程度隔离级别 DBA 锁定 locking 概念 LOCK 事务获取的一种控制资源 保护数据 防止其他事务对数据进行冲突的或不兼容的访问 可锁定资源锁模式锁兼容性自定义锁定锁升级 细粒度的锁升级为粗粒度的锁 SQLServer2008可以用LOCK ESCALATION显示禁止锁升级 DBA 可锁定资源 DBA 锁模式 lockmode DBA 锁兼容性 控制多个事务能否同时获取一个资源上的锁 兼容时才能获得 DBA 阻塞 blocking 概念前提 一事务持有数据资源上的锁动作 另一事务请求相同资源上的不兼容锁后果 新的锁请求被阻塞 请求等待至锁被释放实例 Connection1 获得某行的排他锁Connection2 申请同一行的共享锁 DBA 检测阻塞 1 SP WHO SP LOCK DBA 检测阻塞 2 SYS DM TRAN LOCKSSELECTrequest session idas 服务器进程标识符 resource typeas 被锁定资源类型 request modeas 锁模式 request statusas 请求状态 FROMsys dm tran locks DBA 检测阻塞 3 Othersys dm exec connections 阻塞链中进程相关联接信息 可了解联接中最后读写的时间及SQL语句select fromsys dm exec connectionsCROSSAPPLYsys dm exec sql text most recent sql handle asSTwheresession idIN 55 56 sys dm exec sessions 查询阻塞中涉及的会话 sys dm exec requests 活动请求信息 DBA 终止和避免阻塞 KillSETLOCK TIMEOUTtimeout period DBA 死锁 DBA 概念 多任务中 每个任务锁定了其他任务试图锁定的资源 导致的任务永久阻塞 例子 CREATETABLETable1 Col1intdefault 0 CREATETABLETable2 Col1intdefault 0 INSERTINTOTable1VALUES 1 INSERTINTOTable2VALUES 1 BeginTranUpdateTable1SetCol1 Col1 1 WaitForDelay 00 01 00 SELECT FROMTable2RollbackTran BeginTranUpdateTable2SetCol1 Col1 1 WaitForDelay 00 01 00 SELECT FROMTable1RollbackTran 死锁检测与查看 DBA 死锁检测 锁监视线程 定期搜索数据库引擎实例的所有任务 检测到死锁后线程选择一个死锁牺牲品来结束死锁 死锁牺牲品的事务以1025错误返回到应有程序并释放事务持有的锁 默认选择回滚开销最小的会话作为牺牲品 DEADLOCK PRIOPRTY低的死锁被牺牲 死锁查看工具 1024 报告死锁涉及的每个节点设置格式信息 1222 设置死锁信息的格式 先进程后资源 事件探查器死锁图形事件 降低死锁的方法 DBA 按同一顺序访问对象 注 避免出现循环 避免事务中的用户交互 注 减少持有资源的时间 较少锁竞争 保持事务简短并处于一个批处理中 注 同 2 减少持有资源的时间 使用较低的隔离级别 使用较低隔离级别比使用高隔离级别持有共享锁的时间更短 使用基于行版本控制的隔离级别READ COMMITTED SNAPSHOT设置为ON 读操作为行版本控制而不是共享锁ALLOW SNAPSHOT ISOLATION设置为ON 快照隔离也使用行版本控制使用绑定连接 死锁处理 DBA 方法一 EXECsp whoactive看哪个引起的阻塞 blk 53 或者execsp lock看哪个spid处于wait状态 通过killspid干掉相关进程 方法二 设定锁超时 SELECT LOCK TIMEOUTSETLOCK TIMEOUT5000SQLServer内部有一个锁监视器线程执行死锁检查自动处理 在select语句上加表级锁WITH nolock 此方法有可能导致脏