數(shù)據(jù)庫系統(tǒng)教學(xué)課件第15講并發(fā)控制技術(shù)

討論的，及如何有1.加鎖的目效使用？1.鎖1)及為何要引入鎖不同類型鎖的,作用?指多大對(duì)象？可大可小(大到整個(gè)數(shù)據(jù)庫，小到一個(gè)記錄或?qū)傩灾?引入鎖機(jī)制：是為了保證數(shù)據(jù)的一致性(事務(wù)的隔離性)和提高系統(tǒng)的并發(fā)處理能力!或者說，為保了證應(yīng)用的有效性(兩人不會(huì)訂到同一位置火車票，查看到一個(gè)不存在的成績--如教務(wù)處剛誤輸一個(gè)學(xué)生的成績后馬上又刪除)2)陣，什么是鎖相容矩一個(gè)S鎖能帶來多大并發(fā)能力?S鎖已明顯增強(qiáng)了并發(fā)能力：因?yàn)榭赡?5%以上的應(yīng)用是讀數(shù)據(jù)，更新應(yīng)用的頻率非常小。沒有S鎖，系統(tǒng)的發(fā)處理能力會(huì)大大降低，僅比串行調(diào)度略好。2一鎖與封鎖協(xié)議3)議，加鎖后的調(diào)什么是封鎖協(xié)度什么樣?T:lock-X(B);4串行化調(diào)度？）這是一個(gè)可grant-X(B,T1)1指一組加鎖規(guī)則！read(B);B:=B-50;write(B);unlock(B);仍不是可串釋放-X(B,T1)grant-S(A,T2)行化調(diào)度！若：Ais100Bis200則將會(huì)顯示：T2調(diào)度A+B=250-----------而串行調(diào)度<T1,T2>后T2顯示A+B=300-----------且串行調(diào)度<T2,T1>后T2顯示A+B=300轉(zhuǎn)賬T2:lock-S(A);read(A);unlock(A);lock-S(B);read(B);unlock(B);display(A+B)沖突操作釋放-S(A,T2)grant-S(B,T2)釋放-S(B,T2)沖突操作grant-X(A,T1)Lock-X(A)read(A)查總賬A:=A+50write(A)unlock(A)釋放-X(A,T1)（并發(fā)控制器）圖25-43二兩階段封鎖協(xié)議3)A可正常執(zhí)行，等兩階段封鎖調(diào)度價(jià)的串行調(diào)度？grant-X(B,T3)-成功封鎖點(diǎn)grant-X(A,T3)-成功grant-S(A,T4)-等待釋放--X(B,T3)grant-釋放S(A,T4)X(A,T3)-成功grant-S(B,T4)-成功封鎖點(diǎn)釋放-S(A,T4)釋放-S(B,T4)調(diào)度A順利執(zhí)行！兩階段封鎖-并發(fā)調(diào)度A并發(fā)調(diào)度A等價(jià)的串行調(diào)度6二兩階段封鎖協(xié)議3)B也能夠順利執(zhí)兩階段封調(diào)度行完成？grant-X(B,T3)-成功grant-S(A,T4)-成功封鎖點(diǎn)唯一差異封鎖點(diǎn)grant-X(A,T3)-等待封鎖點(diǎn)grant-S(B,T4)-等待封鎖點(diǎn)調(diào)度A和B有何差異？調(diào)度B形成死鎖！兩階段封鎖-并發(fā)調(diào)度B調(diào)度A順利執(zhí)行！兩階段封鎖-并發(fā)調(diào)度A7二兩階段封鎖協(xié)議若T5撤銷，會(huì)引發(fā)級(jí)聯(lián)卷回(因T6,T7讀了T5寫的數(shù)據(jù))（符合兩階段封鎖協(xié)議）要求更強(qiáng)！8四死鎖處理預(yù)防：避免死鎖出現(xiàn)！三老禮讓:新事務(wù)不等待,主動(dòng)回滾!年輕禮讓:老事務(wù)不等待,強(qiáng)制使新事務(wù)回滾!11

AtransactionT1executingwithSnapshotIsolation?takessnapshotofcommitteddataatstart?alwaysreads/modifiesdatainitsownsnapshot?updatesofconcurrenttransactionsarenotvisibletoT1?writesofT1completewhenitcommits

Concurrentupdatesinvisibletosnapshotread

Problemofinvisibleupdates:Lostupdate

Solution:?First-committer-winsrule:

CommitsonlyifnootherconcurrenttransactionhasalreadywrittendatathatT1intendstowrite.?First-updater-wins

Checkforconcurrentupdateswhenwriteoccursbylockingitem

Butlockshouldbeheldtillallconcurrenttransactionshavefinished

SIbreaksserializabilitywhentxnsmodifydifferentitems,eachbasedonapreviousstateoftheitemtheothermodified?Notverycommoninpractice

E.g.,theTPC-CbenchmarkrunscorrectlyunderSI

whentxnsconflictduetomodifyingdifferentdata,thereisusuallyalsoashareditemtheybothmodifytoo(likeatotalquantity)soSIwillabortoneofthem?Butdoesoccur

Applicationdevelopersshouldbecarefulaboutwriteskew

SIcanalsocausearead-onlytransactionanomaly,whereread-onlytransactionmayseeaninconsistentstateevenifupdatersareserializable?Weomitdetails

Usingsnapshotstoverifyprimary/foreignkeyintegritycanleadtoinconsistency?Integrityconstraintcheckingusuallydoneoutsideofsnapshot

Readingisneverblocked,?andalsodoesn’tblockothertxnsactivities

PerformancesimilartoReadCommitted

Avoidstheusualanomalies?Nodirtyread?Nolostupdate?Nonon-repeatableread?Predicatebasedselectsarerepeatable(nophantoms)

ProblemswithSI?SIdoesnotalwaysgiveserializableexecutions

Serializable:amongtwoconcurrenttxns,oneseestheeffectsoftheother

InSI:neitherseestheeffectsoftheother?Result:Integrityconstraintscanbeviolated(考慮下面兩個(gè)事務(wù)：T34:read(A);read(B);ifA=0thenB:=B+1;write(B);T35:read(B);r