SQL SERVER 阻塞與死鎖.ppt

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