SQL SERVER 阻塞與死鎖

1、LOGO.1SQL Server 2008新特征新特征阻塞與死鎖阻塞與死鎖DBALOGO.2內(nèi)容內(nèi)容事務(wù)與并發(fā)事務(wù)與并發(fā)1鎖定鎖定2阻塞與檢測(cè)阻塞與檢測(cè)3死鎖死鎖4DBA死鎖的避免與處理死鎖的避免與處理5LOGO.3事務(wù)事務(wù) 事務(wù)：?jiǎn)蝹€(gè)工作單元執(zhí)行的一些列操作。 事務(wù)的屬性： A：原子性(Atomicity) 全做或全不做 C：一致性(Consistency) 多個(gè)事務(wù)對(duì)data訪問間不沖突 I：隔離性(Isolation) 用鎖鎖進(jìn)行隔離 D：持久性(Durability) 運(yùn)行模式： 自動(dòng)提交事務(wù)：每個(gè)語(yǔ)句一個(gè)事務(wù) 顯示事務(wù) BEGIN TRAN COMMIT TRAN ROLLBACK

2、TRAN 隱示事務(wù)DBALOGO.4并發(fā)并發(fā) 并發(fā)：多用戶同時(shí)對(duì)一種資源進(jìn)行訪問 并發(fā)影響： 并發(fā)控制：A的修改不會(huì)對(duì)B做的修改產(chǎn)生負(fù)面影響 悲觀并發(fā)控制：加鎖成本 rollback 隔離級(jí)別影響影響現(xiàn)象現(xiàn)象丟失更新(Lost Update)兩事務(wù)更新同一行臟讀(Dirty Reads)第二個(gè)事務(wù)讀其他事務(wù)更新的行不可重復(fù)讀(Non-Repeatable Read) 事務(wù)多次訪問一行但讀到不同數(shù)據(jù)幻讀(Phantom Reads)對(duì)行insert/delete，行所屬事務(wù)讀DBALOGO.5隔離級(jí)別隔離級(jí)別 定義一個(gè)事務(wù)必須與其他事務(wù)所進(jìn)行的資源或數(shù)據(jù)更改相隔離的程度 隔離級(jí)別DBA隔離級(jí)別隔

3、離級(jí)別說明說明可能存在可能存在未提交讀最低級(jí)別，只能保證不讀到物理上損壞的數(shù)據(jù)臟讀、幻讀、不可重復(fù)讀已提交讀默認(rèn)級(jí)別幻讀、不可重復(fù)讀可重復(fù)讀幻讀可序列化隔離事務(wù)最高級(jí)別READ_COMMITTED_SNAPSHOT*使用版本控制提供語(yǔ)句級(jí)讀取一致性，只需要SCH-S表級(jí)別鎖，不需要行或頁(yè)鎖Snapshot*LOGO.6鎖定鎖定(locking) 概念：(LOCK)事務(wù)獲取的一種控制資源，保護(hù)數(shù)據(jù)，防止其他事務(wù)對(duì)數(shù)據(jù)進(jìn)行沖突的或不兼容的訪問。 可鎖定資源 鎖模式 鎖兼容性 自定義鎖定 鎖升級(jí)：細(xì)粒度的鎖升級(jí)為粗粒度的鎖，SQL Server2008 可以用LOCK_ESCALATION顯示禁止鎖

4、升級(jí)。DBALOGO.7可鎖定資源可鎖定資源資源資源說明說明RID堆中的單個(gè)行的行標(biāo)識(shí)符KEY索引中用于保護(hù)序列化事務(wù)中的鍵范圍的行鎖PAGE數(shù)據(jù)庫(kù)中的頁(yè)，數(shù)據(jù)頁(yè)或索引頁(yè)EXTENT連續(xù)的8頁(yè)HoBT堆或B樹。用于保護(hù)沒有聚集索引的表中的B樹(索引)或堆數(shù)據(jù)頁(yè)TABLE整個(gè)表FILE數(shù)據(jù)庫(kù)文件APPLICATION應(yīng)用程序?qū)I(yè)資源METADATA元數(shù)據(jù)ALLOCATION_UNIT分配單元DATABASE數(shù)據(jù)庫(kù)DBALOGO.8鎖模式鎖模式(lock mode)鎖模式鎖模式說明說明共享(S)SELECT，不更改、不更新數(shù)據(jù)更新(U)用于可更新資源中，防止多個(gè)會(huì)話讀、鎖定及隨后對(duì)資源的更新而發(fā)生

5、死鎖?？梢苑乐顾梨i。排他(X)修改，如INSERT、UPDATE或DELETE。保證不同時(shí)對(duì)資源進(jìn)行多重更新。使用排他鎖時(shí)，其他事務(wù)無(wú)法修改數(shù)據(jù)。使用NOLOCK或?yàn)樘峤蛔x隔離級(jí)別時(shí)才能讀意向建立鎖層次結(jié)構(gòu)，意向共享(IS)、意向排他(IX)、意向排他共享(SIX)架構(gòu)架構(gòu)修改(Sch-M)、架構(gòu)穩(wěn)定性(Sch-S)大容量更新(BU)鍵范圍DBALOGO.9鎖兼容性鎖兼容性 控制多個(gè)事務(wù)能否同時(shí)獲取一個(gè)資源上的鎖。兼容時(shí)才能獲得。請(qǐng)求模式請(qǐng)求模式現(xiàn)授予模式現(xiàn)授予模式ISSUIXSIXX意向共享是是是是是否共享是是是否否否更新是是否否否否意向排他是否否是否否意向排他共享是否否否否否排他否否否否否

6、否DBALOGO.10阻塞阻塞(blocking) 概念 前提：一事務(wù)持有數(shù)據(jù)資源上的鎖 動(dòng)作：另一事務(wù)請(qǐng)求相同資源上的不兼容鎖 后果：新的鎖請(qǐng)求被阻塞，請(qǐng)求等待至鎖被釋放 實(shí)例：Connection1：獲得某行的排他鎖Connection2：申請(qǐng)同一行的共享鎖DBALOGO.11檢測(cè)阻塞檢測(cè)阻塞1. SP_WHO/SP_LOCKDBALOGO.12檢測(cè)阻塞檢測(cè)阻塞2. SYS.DM_TRAN_LOCKSSELECT request_session_id as 服務(wù)器進(jìn)程標(biāo)識(shí)符,resource_type as 被鎖定資源類型,request_mode as 鎖模式,request_statu

7、s as 請(qǐng)求狀態(tài)FROM sys.dm_tran_locksDBALOGO.13檢測(cè)阻塞檢測(cè)阻塞3. Othersys.dm_exec_connections(阻塞鏈中進(jìn)程相關(guān)聯(lián)接信息) -可了解聯(lián)接中最后讀寫的時(shí)間及SQL語(yǔ)句select * from sys.dm_exec_connectionsCROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)as STwhere session_id IN(55,56); sys.dm_exec_sessions (查詢阻塞中涉及的會(huì)話)sys.dm_exec_requests (活動(dòng)請(qǐng)求

8、信息)DBALOGO.14終止和避免阻塞終止和避免阻塞KillSET LOCK_TIMEOUT timeout_periodDBALOGO.15死鎖死鎖DBA 概念：多任務(wù)中，每個(gè)任務(wù)鎖定了其他任務(wù)試圖鎖定的資源，導(dǎo)致的任務(wù)永久阻塞。 例子：CREATE TABLE Table1(Col1 int default(0);CREATE TABLE Table2(Col1 int default(0);INSERT INTO Table1 VALUES(1);INSERT INTO Table2 VALUES(1); Begin Tran Update Table1 Set Col1=Col1+1

9、; WaitFor Delay 00:01:00; SELECT * FROM Table2Rollback Tran; Begin Tran Update Table2 Set Col1=Col1+1; WaitFor Delay 00:01:00; SELECT * FROM Table1Rollback Tran;LOGO.16死鎖檢測(cè)與查看死鎖檢測(cè)與查看DBA 死鎖檢測(cè)：鎖監(jiān)視線程。定期搜索數(shù)據(jù)庫(kù)引擎實(shí)例的所有任務(wù)。檢測(cè)到死鎖后線程選擇一個(gè)死鎖犧牲品來(lái)結(jié)束死鎖。死鎖犧牲品的事務(wù)以1025錯(cuò)誤返回到應(yīng)有程序并釋放事務(wù)持有的鎖。默認(rèn)選擇回滾開銷最小的會(huì)話作為犧牲品。DEADLOCK_PRI

10、OPRTY低的死鎖被犧牲。 死鎖查看工具： 1024：報(bào)告死鎖涉及的每個(gè)節(jié)點(diǎn)設(shè)置格式信息。 1222：設(shè)置死鎖信息的格式，先進(jìn)程后資源。 事件探查器死鎖圖形事件。LOGO.17降低死鎖的方法降低死鎖的方法DBA 按同一順序訪問對(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，快照隔離也使用行版本控制 使用綁定連接LOGO.18死鎖處理死鎖處理DBA方法一：EXEC sp_who active看哪個(gè)引起的阻塞，blk = 53；或者exec sp_lock 看哪個(gè)spid 處于wait狀態(tài)，通過kill spid干掉相關(guān)進(jìn)程。方法二：設(shè)定鎖超時(shí)：SELECT LOCK_TIMEOUTSET LOCK_TIMEOUT 5000SQL Server內(nèi)部有一個(gè)鎖監(jiān)視器線程執(zhí)行死鎖檢查自動(dòng)處理。在select語(yǔ)句上加表級(jí)鎖WITH(