1、第15講:(第12章) 第15講-事務管理(并發控制技術)1課程名稱: 數據庫系統 -2一 鎖與封鎖協議1.鎖2)什么是鎖相容矩陣，一個S鎖能帶來多大并發能力? 討論1.加鎖的目的，及如何有效使用？1)為何要引入鎖,及不同類型鎖的作用? 引入鎖機制：是為了保證數據的一致性(事務的隔離性)和提高系統的并發處理能力!或者說，為保了證應用的有效性(兩人不會訂到同一位置火車票，查看到一個不存在的成績-如教務處剛誤輸一個學生的成績后馬上又刪除)可大可小(大到整個數據庫，小到一個記錄或屬性值)指多大對象？S鎖已明顯增強了并發能力：因為可能95%以上的應用是讀數據，更新應用的頻率非常小。沒有S鎖，系統的發處

2、理能力會大大降低，僅比串行調度略好。32.封鎖協議 T1: lock-X(B); read (B); B:=B-50; write (B); unlock(B); Lock-X(A) read(A) A:=A+50 write(A) unlock(A)T2: lock-S(A); read (A); unlock(A); lock-S(B); read (B); unlock(B); display(A+B)圖25-44）這是一個可串行化調度？仍不是可串行化調度！若：A is 100B is 200則 T2調度將會顯示：A+B=250-而串行調度后T2顯示A+B=300-且串行調度后T2顯示A

3、+B=300沖突操作沖突操作3)什么是封鎖協議，加鎖后的調度什么樣? 轉 賬查總賬指一組加鎖規則！grant-X(B,T1)grant-S(A,T2)釋放-S(B,T2)釋放-S(A,T2)（并發控制器）釋放-X(B,T1)grant-S(B,T2)grant-X(A,T1)釋放-X(A,T1)一 鎖與封鎖協議43.死鎖與活鎖grant-X(B,T3)-成功grant-S(A,T4) -成功grant-S(B,T4) 等待grant-X(A,T3) -等待形成兩個事務相互等待對方釋放資源各自才能往下繼續做的僵局！Starvation餓死(活鎖) is also possible if conc

4、urrency control manager is badly designed. For example:1) A transaction may be waiting for an X-lock on an item, while a sequence of other transactions request and are granted an S-lock on the same item. 2) The same transaction is repeatedly rolled back due to deadlocks.出現一個事務永遠(長時間)等待某一數據項被其它事務釋放后才

5、能進行封鎖的現象！ T1 T2 T3Lock-S(A)Read(A)lock-S(B)read(B)unlock(B)lock-S(B)read(B)unlock(B) Lock-X(B)Write(B)unlock(B) Tngrant-X(B,T1) 永遠等待grant-S(B,T2) 成功grant-S(B,T2) 成功釋放-S(B,T2)釋放-S(B,T3)一 鎖與封鎖協議5二 兩階段封鎖協議1. 兩階段封鎖協議這些協議都是兩階段封鎖協議嗎，封鎖點在何處？2)兩階段封鎖協議的主要用途？T1和T2不是兩階段封鎖協議，T3和T4都是兩階段封鎖協議！封鎖點封鎖點（兩階段封鎖協議可串行化的例示

6、分析）（p.376但兩階段封鎖并不能保證不會發生死鎖的示例分析）6兩階段封鎖協議可串行化(例示)兩階段封鎖-并發調度A封鎖點封鎖點grant-X(B,T3)-成功grant-X(A,T3)-成功grant-S(A,T4)-等待grant-S(B,T4)-成功grant-S(A,T4)-成功釋放-X(B,T3)釋放-X(A,T3)釋放-S(A,T4)釋放-S(B,T4)并發調度A等價的串行調度3)兩階段封鎖調度A可正常執行，等價的串行調度？二 兩階段封鎖協議調度A順利執行！7兩階段封鎖并不能保證不會發生死鎖(示例)二 兩階段封鎖協議兩階段封鎖-并發調度B調度A和B有何差異？封鎖點封鎖點grant

7、-X(B,T3)-成功grant-X(A,T3)-等待grant-S(A,T4)-成功grant-S(B,T4)-等待兩階段封鎖-并發調度A封鎖點封鎖點調度B形成死鎖！3)兩階段封調度B也能夠順利執行完成？調度A順利執行！唯一差異82.其它的兩階段封鎖協議（符合兩階段封鎖協議）二 兩階段封鎖協議要求更強！若T5撤銷，會引發級聯卷回(因T6,T7讀了T5寫的數據)9四 死鎖處理死鎖特點：多個事務相互等待某一方釋放資源！1.死鎖檢測方法討論4. DBMS如何檢查和處理死鎖?grant-X(B,T3)-成功grant-S(A,T4) -成功grant-S(B,T4) 等待grant-X(A,T3)

8、-等待其等待圖中一定帶環102.死鎖的解除方法四 死鎖處理(某一事務)對法1-2可能引起不足的避免113.死鎖的預防方法p.381四 死鎖處理三預防：避免死鎖出現！老禮讓: 新事務不等待,主動回滾!年輕禮讓:老事務不等待,強制使新事務回滾!Motivation: Decision support queries that read large amounts of data have concurrency conflicts with OLTP transactions that update a few rowsPoor performance resultsSolution 1: Giv

9、e logical “snapshot” of database state to read only transactions, read-write transactions use normal lockingMultiversion 2-phase lockingWorks well, but how does system know a transaction is read only?Solution 2: Give snapshot of database state to every transaction, updates alone use 2-phase locking

10、to guard against concurrent updatesProblem: variety of anomalies such as lost update can resultPartial solution: snapshot isolation level (next slide)Proposed by Berenson et al, SIGMOD 1995Variants implemented in many database systems E.g. Oracle, PostgreSQL, SQL Server 2005Snapshot Isolation快照隔離A t

11、ransaction T1 executing with Snapshot Isolationtakes snapshot of committed data at startalways reads/modifies data in its own snapshotupdates of concurrent transactions are not visible to T1 writes of T1 complete when it commitsSnapshot IsolationSnapshot ReadConcurrent updates invisible to snapshot

12、readSnapshot WriteProblem of invisible updates: Lost updateSolution:First-committer-wins rule:Commits only if no other concurrent transaction has already written data that T1 intends to write.First-updater-winsCheck for concurrent updates when write occurs by locking itemBut lock should be held till

13、 all concurrent transactions have finishedUpdate checkingSI breaks serializability when txns modify different items, each based on a previous state of the item the other modifiedNot very common in practiceE.g., the TPC-C benchmark runs correctly under SIwhen txns conflict due to modifying different

14、data, there is usually also a shared item they both modify too (like a total quantity) so SI will abort one of themBut does occurApplication developers should be careful about write skewSI can also cause a read-only transaction anomaly, where read-only transaction may see an inconsistent state even

15、if updaters are serializableWe omit detailsUsing snapshots to verify primary/foreign key integrity can lead to inconsistencyIntegrity constraint checking usually done outside of snapshotSnapshot Isolation AnomaliesReading is never blocked, and also doesnt block other txns activitiesPerformance simil

16、ar to Read CommittedAvoids the usual anomaliesNo dirty readNo lost updateNo non-repeatable readPredicate based selects are repeatable (no phantoms)Problems with SISI does not always give serializable executionsSerializable: among two concurrent txns, one sees the effects of the otherIn SI: neither s

17、ees the effects of the otherResult: Integrity constraints can be violatedBenefits of SI19練習(考慮下面兩個事務：T34:read(A);read(B);if A=0 then B:=B+1;write(B);T35:read(B);read(A);if B=0 then A:=A+1;write(A);a. 給事務T34與T35增加加鎖、解鎖指令，使它們遵從兩階段封鎖協議。b. 這兩個事務會引起死鎖嗎？說明：兩階段封鎖協議(保證沖突可串行化，但不保證不發生死鎖)1.增長階段：事務可以獲得鎖，但不能釋放鎖；2.縮減階段：事務可以釋放鎖，但不能獲得新鎖。20T 34: lock-S(A)read(A)lock-X(B)read(B)if A = 0 then B := B + 1write(B)unlock(A)unlock(B)解答b. 有可能發生死鎖。如：T 35: lock-S(B)read(B)lock-