SQL-SERVER-阻塞与死锁.ppt

LOGOSQL Server 2008新特征新特征阻塞与死锁阻塞与死锁DBA.LOGO内容内容事务与并发事务与并发1锁定锁定2阻塞与检测阻塞与检测3死锁死锁4DBA死锁的避免与处理死锁的避免与处理5.LOGO事务事务事务：单个工作单元执行的一些列操作。事务的属性：A：原子性(Atomicity)全做或全不做C：一致性(Consistency)多个事务对data访问间不冲突I：隔离性(Isolation)用锁锁进行隔离D：持久性(Durability)运行模式：自动提交事务：每个语句一个事务显示事务BEGIN TRANCOMMIT TRANROLLBACK TRAN隐示事务DBA.LOGO并发并发并发：多用户同时对一种资源进行访问并发影响：并发控制：A的修改不会对B做的修改产生负面影响悲观并发控制：加锁成本rollback隔离级别影响影响现象现象丢失更新(LostUpdate)两事务更新同一行脏读(DirtyReads)第二个事务读其他事务更新的行不可重复读(Non-RepeatableRead)事务多次访问一行但读到不同数据幻读(PhantomReads)对行insert/delete，行所属事务读DBA.LOGO隔离级别隔离级别定义一个事务必须与其他事务所进行的资源或数据更改相隔离的程度隔离级别DBA隔离级别隔离级别说明说明可能存在可能存在未提交读最低级别，只能保证不读到物理上损坏的数据脏读、幻读、不可重复读已提交读默认级别幻读、不可重复读可重复读幻读可序列化隔离事务最高级别READ_COMMITTED_SNAPSHOT*使用版本控制提供语句级读取一致性，只需要SCH-S表级别锁，不需要行或页锁Snapshot*.LOGO锁定锁定(locking)概念：(LOCK)事务获取的一种控制资源，保护数据，防止其他事务对数据进行冲突的或不兼容的访问。可锁定资源锁模式锁兼容性自定义锁定锁升级：细粒度的锁升级为粗粒度的锁，SQL Server2008 可以用LOCK_ESCALATION显示禁止锁升级。DBA.LOGO可锁定资源可锁定资源资源资源说明说明RID堆中的单个行的行标识符KEY索引中用于保护序列化事务中的键范围的行锁PAGE数据库中的页，数据页或索引页EXTENT连续的8页HoBT堆或B树。用于保护没有聚集索引的表中的B树(索引)或堆数据页TABLE整个表FILE数据库文件APPLICATION应用程序专业资源METADATA元数据ALLOCATION_UNIT分配单元DATABASE数据库DBA.LOGO锁模式锁模式(lock mode)锁模式锁模式说明说明共享(S)SELECT，不更改、不更新数据更新(U)用于可更新资源中，防止多个会话读、锁定及随后对资源的更新而发生死锁。可以防止死锁。排他(X)修改，如INSERT、UPDATE或DELETE。保证不同时对资源进行多重更新。使用排他锁时，其他事务无法修改数据。使用NOLOCK或为提交读隔离级别时才能读意向建立锁层次结构，意向共享(IS)、意向排他(IX)、意向排他共享(SIX)架构架构修改(Sch-M)、架构稳定性(Sch-S)大容量更新(BU)键范围DBA.LOGO锁兼容性锁兼容性控制多个事务能否同时获取一个资源上的锁。兼容时才能获得。请求模式请求模式现授予模式现授予模式ISSUIXSIXX意向共享是是是是是否共享是是是否否否更新是是否否否否意向排他是否否是否否意向排他共享是否否否否否排他否否否否否否DBA.LOGO阻塞阻塞(blocking)概念前提：一事务持有数据资源上的锁动作：另一事务请求相同资源上的不兼容锁后果：新的锁请求被阻塞，请求等待至锁被释放实例：Connection1：获得某行的排他锁Connection2：申请同一行的共享锁DBA.LOGO检测阻塞检测阻塞1.SP_WHO/SP_LOCKDBA.LOGO检测阻塞检测阻塞2.SYS.DM_TRAN_LOCKSSELECTrequest_session_idas服务器进程标识符,resource_typeas被锁定资源类型,request_modeas锁模式,request_statusas请求状态FROMsys.dm_tran_locksDBA.LOGO检测阻塞检测阻塞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.LOGO终止和避免阻塞终止和避免阻塞1.Kill2.SETLOCK_TIMEOUTtimeout_periodDBA.LOGO死锁死锁DBA概念：多任务中，每个任务锁定了其他任务试图锁定的资源，导致的任务永久阻塞。例子：CREATETABLETable1(Col1intdefault(0);CREATETABLETable2(Col1intdefault(0);INSERTINTOTable1VALUES(1);INSERTINTOTable2VALUES(1);BeginTranUpdateTable1SetCol1=Col1+1;WaitForDelay00:01:00;SELECT*FROMTable2RollbackTran;BeginTranUpdateTable2SetCol1=Col1+1;WaitForDelay00:01:00;SELECT*FROMTable1RollbackTran;.LOGO死锁检测与查看死锁检测与查看DBA死锁检测：锁监视线程。定期搜索数据库引擎实例的所有任务。检测到死锁后线程选择一个死锁牺牲品来结束死锁。死锁牺牲品的事务以1025错误返回到应有程序并释放事务持有的锁。默认选择回滚开销最小的会话作为牺牲品。DEADLOCK_PRIOPRTY低的死锁被牺牲。死锁查看工具：1024：报告死锁涉及的每个节点设置格式信息。1222：设置死锁信息的格式，先进程后资源。事件探查器死锁图形事件。.LOGO降低死锁的方法降低死锁的方法DBA按同一顺序访问对象。(注：避免出现循环)避免事务中的用户交互。(注：减少持有资源的时间，较少锁竞争)保持事务简短并处于一个批处理中。(注：同(2)，减少持有资源的时间)使用较低的隔离级别。使用较低隔离级别比使用高隔离级别持有共享锁的时间更短。使用基于行版本控制的隔离级别READ_COMMITTED_SNAPSHOT设置为ON，读操作为行版本控制而不是共享锁ALLOW_SNAPSHOT_ISOLATION设置为ON，快照隔离也使用行版本控制使用绑定连接.LOGO死锁处理死锁处理DBA方法一：EXECsp_whoactive看哪个引起的阻塞，blk=53；或者execsp_lock看哪个spid处于wait状态，通过killspid干掉相关进程。方法二：设定锁超时：SELECTLOCK_TIMEOUTSETLOCK_TIMEOUT5000SQLServer内部有一个锁监视器线程执行死锁检查自动处理。在select语句上加表级锁WITH(nolock)，此方法有可能导致脏读，因为读取的未提交的数据。行版本控制的隔离级别，不加读锁。既不死锁也不读脏数据。SETALLOW_SNAPSHOT_ISOLATIONONSETREAD_COMMITTED_SNAPSHOTON.LOGODBA.