SQL SERVER 阻塞與死鎖.ppt

SQLServer2008新特征 阻塞與死鎖 DBA 內(nèi)容 DBA 事務(wù) 事務(wù) 單個工作單元執(zhí)行的一些列操作 事務(wù)的屬性 A 原子性 Atomicity 全做或全不做C 一致性 Consistency 多個事務(wù)對data訪問間不沖突I 隔離性 Isolation 用鎖進(jìn)行隔離D 持久性 Durability 運行模式 自動提交事務(wù) 每個語句一個事務(wù)顯示事務(wù)BEGINTRANCOMMITTRANROLLBACKTRAN隱示事務(wù) DBA 并發(fā) 并發(fā) 多用戶同時對一種資源進(jìn)行訪問并發(fā)影響 并發(fā)控制 A的修改不會對B做的修改產(chǎn)生負(fù)面影響悲觀并發(fā)控制 加鎖成本rollback隔離級別 DBA 隔離級別 定義一個事務(wù)必須與其他事務(wù)所進(jìn)行的資源或數(shù)據(jù)更改相隔離的程度隔離級別 DBA 鎖定 locking 概念 LOCK 事務(wù)獲取的一種控制資源 保護(hù)數(shù)據(jù) 防止其他事務(wù)對數(shù)據(jù)進(jìn)行沖突的或不兼容的訪問 可鎖定資源鎖模式鎖兼容性自定義鎖定鎖升級 細(xì)粒度的鎖升級為粗粒度的鎖 SQLServer2008可以用LOCK ESCALATION顯示禁止鎖升級 DBA 可鎖定資源 DBA 鎖模式 lockmode DBA 鎖兼容性 控制多個事務(wù)能否同時獲取一個資源上的鎖 兼容時才能獲得 DBA 阻塞 blocking 概念前提 一事務(wù)持有數(shù)據(jù)資源上的鎖動作 另一事務(wù)請求相同資源上的不兼容鎖后果 新的鎖請求被阻塞 請求等待至鎖被釋放實例 Connection1 獲得某行的排他鎖Connection2 申請同一行的共享鎖 DBA 檢測阻塞 1 SP WHO SP LOCK DBA 檢測阻塞 2 SYS DM TRAN LOCKSSELECTrequest session idas 服務(wù)器進(jìn)程標(biāo)識符 resource typeas 被鎖定資源類型 request modeas 鎖模式 request statusas 請求狀態(tài) FROMsys dm tran locks DBA 檢測阻塞 3 Othersys dm exec connections 阻塞鏈中進(jìn)程相關(guān)聯(lián)接信息 可了解聯(lián)接中最后讀寫的時間及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 概念 多任務(wù)中 每個任務(wù)鎖定了其他任務(wù)試圖鎖定的資源 導(dǎo)致的任務(wù)永久阻塞 例子 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 死鎖檢測 鎖監(jiān)視線程 定期搜索數(shù)據(jù)庫引擎實例的所有任務(wù) 檢測到死鎖后線程選擇一個死鎖犧牲品來結(jié)束死鎖 死鎖犧牲品的事務(wù)以1025錯誤返回到應(yīng)有程序并釋放事務(wù)持有的鎖 默認(rèn)選擇回滾開銷最小的會話作為犧牲品 DEADLOCK PRIOPRTY低的死鎖被犧牲 死鎖查看工具 1024 報告死鎖涉及的每個節(jié)點設(shè)置格式信息 1222 設(shè)置死鎖信息的格式 先進(jìn)程后資源 事件探查器死鎖圖形事件 降低死鎖的方法 DBA 按同一順序訪問對象 注 避免出現(xiàn)循環(huán) 避免事務(wù)中的用戶交互 注 減少持有資源的時間 較少鎖競爭 保持事務(wù)簡短并處于一個批處理中 注 同 2 減少持有資源的時間 使用較低的隔離級別 使用較低隔離級別比使用高隔離級別持有共享鎖的時間更短 使用基于行版本控制的隔離級別READ COMMITTED SNAPSHOT設(shè)置為ON 讀操作為行版本控制而不是共享鎖ALLOW SNAPSHOT ISOLATION設(shè)置為ON 快照隔離也使用行版本控制使用綁定連接 死鎖處理 DBA 方法一 EXECsp whoactive看哪個引起的阻塞 blk 53 或者execsp lock看哪個spid處于wait狀態(tài) 通過killspid干掉相關(guān)進(jìn)程 方法二 設(shè)定鎖超時 SELECT LOCK TIMEOUTSETLOCK TIMEOUT5000SQLServer內(nèi)部有一個鎖監(jiān)視器線程執(zhí)行死鎖檢查自動處理 在select語句上加表級鎖WITH nolock 此方法有可能導(dǎo)致臟