ConcurrencyControl Chapter17 - - PDF document

1

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 1

ConcurrencyControl

Chapter17

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 2

ConflictSerializableSchedules

• Twoschedulesareconflictequivalent if:

Involvethesameactionsofthesametransactions

Everypairofconflictingactionsisorderedthe sameway

• ScheduleSisconflictserializable ifSis

conflictequivalenttosomeserialschedule

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 3

Example

• Aschedulethatisnotconflictserializable:
• Thecycleinthegraphrevealstheproblem.

TheoutputofT1dependsonT2,andvice- versa.

T1: R(A),W(A), R(B),W(B) T2: R(A),W(A),R(B),W(B) T1 T2 A B Dependencygraph

2

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 4

DependencyGraph

• Dependencygraph:OnenodeperXact;edge

fromTitoTj ifTjreads/writesanobjectlast writtenbyTi.

• Theorem:Scheduleisconflictserializableif

andonlyifitsdependencygraphisacyclic

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 5

Review:Strict2PL

• StrictTwo-phaseLocking(Strict2PL)Protocol:

EachXactmustobtainaS(shared)lockonobject beforereading,andanX(exclusive)lockonobject beforewriting.

Alllocksheldbyatransactionarereleasedwhen thetransactioncompletes

IfanXactholdsanXlockonanobject,noother Xactcangetalock(SorX)onthatobject.

• Strict2PLallowsonlyscheduleswhose

precedencegraphisacyclic

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 6

Two-PhaseLocking(2PL)

• Two-PhaseLockingProtocol

EachXactmustobtainaS(shared)lockonobject beforereading,andanX(exclusive)lockonobject beforewriting.

Atransactioncannotrequestadditionallocks

• nceitreleasesanylocks.

IfanXactholdsanXlockonanobject,noother Xactcangetalock(SorX)onthatobject.

3

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 7

ViewSerializability

• SchedulesS1andS2areviewequivalent if:

IfTireadsinitialvalueofAinS1,thenTialsoreads initialvalueofAinS2

IfTireadsvalueofAwrittenbyTjinS1,thenTialso readsvalueofAwrittenbyTjinS2

IfTiwritesfinalvalueofAinS1,thenTialsowrites finalvalueofAinS2 T1:R(A) W(A) T2: W(A) T3: W(A) T1:R(A),W(A) T2: W(A) T3: W(A)

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 8

LockManagement

• Lockandunlockrequestsarehandledbythelock

manager

• Locktableentry:

Numberoftransactionscurrentlyholdingalock

Typeoflockheld(sharedorexclusive)

Pointertoqueueoflockrequests

• Lockingandunlockinghavetobeatomicoperations
• Lockupgrade:transactionthatholdsasharedlock

canbeupgradedtoholdanexclusivelock

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 9

Deadlocks

• Deadlock:Cycleoftransactionswaitingfor

lockstobereleasedbyeachother.

• Twowaysofdealingwithdeadlocks:

Deadlockprevention

Deadlockdetection

4

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 10

DeadlockPrevention

• Assignprioritiesbasedontimestamps.

AssumeTiwantsalockthatTjholds.Two policiesarepossible:

Wait-Die:ItTihashigherpriority,TiwaitsforTj;

• therwiseTiaborts

Wound-wait:IfTihashigherpriority,Tjaborts;

• therwiseTiwaits
• Ifatransactionre-starts,makesureithasits
• riginaltimestamp

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 11

DeadlockDetection

• Createawaits-forgraph:

Nodesaretransactions

ThereisanedgefromTitoTjifTiiswaitingforTj toreleasealock

• Periodicallycheckforcyclesinthewaits-for

graph

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 12

DeadlockDetection(Continued)

Example: T1:S(A),R(A), S(B) T2: X(B),W(B) X(C) T3: S(C),R(C) X(A) T4: X(B) T1 T2 T4 T3 T1 T2 T3 T3

5

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 13

Multiple-GranularityLocks

• Hardtodecidewhatgranularitytolock

(tuplesvs.pagesvs.tables).

• Shouldn’thavetodecide!
• Data“containers”arenested:

Tuples Tables Pages Database contains

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 14

Solution:NewLockModes,Protocol

• AllowXactstolockateachlevel,butwitha

specialprotocolusingnew“intention”locks:

v Beforelockinganitem,Xact

mustset“intentionlocks”

• nallitsancestors.

v Forunlock,gofromspecific

togeneral(i.e.,bottom-up).

v SIXmode:LikeS&IXat

thesametime.

• IS

IX

• IS

IX √ √ √ √ √ √ S X √ √ S X √ √ √ √ √ √ √ √

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 15

MultipleGranularityLockProtocol

• EachXactstartsfromtherootofthehierarchy.
• TogetSorISlockonanode,mustholdISorIX
• nparentnode.
• WhatifXactholdsSIXonparent?Sonparent?
• TogetXorIXorSIXonanode,mustholdIXor

SIXonparentnode.

• Mustreleaselocksinbottom-uporder.

Protocoliscorrectinthatitisequivalenttodirectlysetting locksattheleaflevelsofthehierarchy.

6

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 16

Examples

• T1scansR,andupdatesafewtuples:
• T1getsanSIXlockonR,thenrepeatedlygetsanS

lockontuplesofR,andoccasionallyupgradesto Xonthetuples.

• T2usesanindextoreadonlypartofR:
• T2getsanISlockonR,andrepeatedly

getsanSlockontuplesofR.

• T3readsallofR:
• T3getsanSlockonR.
• OR,T3couldbehavelikeT2;can

uselockescalation todecidewhich.

• IS

IX

• IS

IX √ √ √ √ √ √ S X √ √ S X √ √ √ √ √ √ √ √

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 17

DynamicDatabases

• IfwerelaxtheassumptionthattheDBisa

fixedcollectionofobjects,evenStrict2PLwill notassureserializability:

• T1locksallpagescontainingsailorrecordswith

rating =1,andfindsoldest sailor(say,age =71).

• Next,T2insertsanewsailor;rating =1,age =96.
• T2alsodeletesoldestsailorwithrating=2(and,

say,age =80),andcommits.

• T1nowlocksallpagescontainingsailorrecords

withrating =2,andfindsoldest (say,age =63).

• NoconsistentDBstatewhereT1is“correct”!

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 18

TheProblem

• T1implicitlyassumesthatithaslockedthe

setofallsailorrecordswithrating =1.

• Assumptiononlyholdsifnosailorrecordsare

addedwhileT1isexecuting!

• Needsomemechanismtoenforcethis

assumption.(Indexlockingandpredicate locking.)

• Exampleshowsthatconflictserializability

guaranteesserializabilityonlyifthesetof

• bjectsisfixed!

7

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 19

IndexLocking

• Ifthereisadenseindexontherating field

usingAlternative(2),T1shouldlockthe indexpagecontainingthedataentrieswith rating =1.

• Iftherearenorecordswithrating=1,T1must

locktheindexpagewheresuchadataentrywould be,ifitexisted!

• Ifthereisnosuitableindex,T1mustlockall

pages,andlockthefile/tabletopreventnew pagesfrombeingadded,toensurethatno newrecordswithrating =1areadded.

r=1 Data Index

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 20

PredicateLocking

• Grantlockonallrecordsthatsatisfysome

logicalpredicate,e.g.age>2*salary.

• Indexlockingisaspecialcaseofpredicate

lockingforwhichanindexsupportsefficient implementationofthepredicatelock.

• Whatisthepredicateinthesailorexample?
• Ingeneral,predicatelockinghasalotof

lockingoverhead.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 21

LockinginB+Trees

• Howcanweefficientlylockaparticularleaf

node?

• Btw,don’tconfusethiswithmultiplegranularity

locking!

• Onesolution:Ignorethetreestructure,justlock

pageswhiletraversingthetree,following2PL.

• Thishasterribleperformance!
• Rootnode(andmanyhigherlevelnodes)become

bottlenecksbecauseeverytreeaccessbeginsatthe root.

8

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 22

TwoUsefulObservations

• Higherlevelsofthetreeonlydirectsearches

forleafpages.

• Forinserts,anodeonapathfromrootto

modifiedleafmustbelocked(inXmode,of course),onlyifasplitcanpropagateuptoit fromthemodifiedleaf.(Similarpointholds w.r.t.deletes.)

• Wecanexploittheseobservationstodesign

efficientlockingprotocolsthatguarantee serializabilityeventhoughtheyviolate2PL.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 23

ASimpleTreeLockingAlgorithm

• Search:Startatrootandgodown;

repeatedly,Slockchildthenunlockparent.

• Insert/Delete: Startatrootandgodown,
• btainingXlocksasneeded.Oncechildis

locked,checkifitissafe:

• Ifchildissafe,releasealllocksonancestors.
• Safenode: Nodesuchthatchangeswillnot

propagateupbeyondthisnode.

• Inserts:Nodeisnotfull.
• Deletes:Nodeisnothalf-empty.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 24

Example

ROOT

A B C D E F G H I

20 35 20* 38 44 22* 23* 24* 35* 36* 38* 41* 44* Do: 1)Search38* 2)Delete38* 3)Insert45* 4)Insert25* 23

9

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 25

ABetterTreeLockingAlgorithm (SeeBayer-Schkolnickpaper)

• Search: Asbefore.
• Insert/Delete:

Xlockonleaf.

XactusingpreviousInsert/Deleteprotocol.

• Gamblesthatonlyleafnodewillbemodified;

ifnot,Slockssetonthefirstpasstoleafare wasteful.Inpractice,betterthanpreviousalg.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 26

Example

ROOT

A B C D E F G H I

20 35 20* 38 44 22* 23* 24* 35* 36* 38* 41* 44* Do: 1)Delete38* 2)Insert25* 4)Insert45* 5)Insert45*, then46* 23

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 27

EvenBetterAlgorithm

• Search: Asbefore.
• Insert/Delete:

IXlocksinsteadofXlocksatallnodes.

lockstop-down:i.e.,startingfromnode nearesttoroot.(Top-downreduceschances

• fdeadlock.)

(ContrastuseofIXlocksherewiththeirusein multiple-granularitylocking.)

10

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 28

HybridAlgorithm

• ThelikelihoodthatwereallyneedanXlock

decreasesaswemoveupthetree.

• Hybridapproach:

SetSlocks SetSIXlocks SetXlocks

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 29

OptimisticCC(Kung-Robinson)

• Lockingisaconservativeapproachinwhich

conflictsareprevented.Disadvantages:

• Ifconflictsarerare,wemightbeabletogain

concurrencybynotlocking,andinstead checkingforconflictsbeforeXactscommit.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 30

Kung-RobinsonModel

• Xactshavethreephases:

makechangestoprivatecopiesofobjects.

public.

ROOT

• ld

new modified

• bjects

11

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 31

Validation

• Testconditionsthataresufficient toensure

thatnoconflictoccurred.

• EachXactisassignedanumericid.
• Justuseatimestamp.
• XactidsassignedatendofREADphase,just

beforevalidationbegins.(Whythen?)

• ReadSet(Ti): SetofobjectsreadbyXactTi.
• WriteSet(Ti): SetofobjectsmodifiedbyTi.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 32

Test1

• ForalliandjsuchthatTi<Tj,checkthatTi

completesbeforeTjbegins.

Ti Tj

R V W R V W

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 33

Test2

• ForalliandjsuchthatTi<Tj,checkthat:

Ti Tj

R V W R V W

DoesTjreaddirtydata?DoesTioverwriteTj’swrites?

12

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 34

Test3

• ForalliandjsuchthatTi<Tj,checkthat:

Ti Tj

R V W R V W

DoesTjreaddirtydata?DoesTioverwriteTj’swrites?

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 35

ApplyingTests1&2:SerialValidation

• TovalidateXactT:

valid =true; //S=setofXactsthatcommittedafterBegin(T)

< foreach TsinSdo{

if ReadSet(Ts)doesnotintersectWriteSet(Ts) then valid =false; } if valid then{installupdates;//Writephase CommitT} > else RestartT

endofcriticalsection

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 36

CommentsonSerialValidation

• AppliesTest2,withTplayingtheroleofTj

andeachXactinTs(inturn)beingTi.

• AssignmentofXactid,validation,andthe

Writephaseareinsideacriticalsection!

• I.e.,Nothingelsegoesonconcurrently.
• IfWritephaseislong,majordrawback.
• OptimizationforRead-onlyXacts:
• Don’tneedcriticalsection(becausethereisno

Writephase).

13

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 37

SerialValidation(Contd.)

• Multistageserialvalidation: Validateinstages,at

eachstagevalidatingTagainstasubsetoftheXacts thatcommittedafterBegin(T).

• Onlylaststagehastobeinsidecriticalsection.
• Starvation:RunstarvingXactinacriticalsection(!!)
• SpaceforWriteSets: TovalidateTj,musthave

WriteSetsforallTiwhereTi<TjandTiwasactive whenTjbegan.TheremaybemanysuchXacts,and wemayrunoutofspace.

• Tj’svalidationfailsifitrequiresamissingWriteSet.
• NoproblemifXactidsassignedatstartofReadphase.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 38

OverheadsinOptimisticCC

• Mustrecordread/writeactivityinReadSetand

WriteSetperXact.

• Mustcreateanddestroythesesetsasneeded.
• Mustcheckforconflictsduringvalidation,and

mustmakevalidatedwrites``global’’.

• Criticalsectioncanreduceconcurrency.
• Schemeformakingwritesglobalcanreduceclustering
• fobjects.
• OptimisticCCrestartsXactsthatfailvalidation.
• Workdonesofariswasted;requiresclean-up.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 39

``Optimistic’’2PL

• Ifdesired,wecandothefollowing:

writesglobal,thenreleasealllocks.

• IncontrasttoOptimisticCCasinKung-

Robinson,thisschemeresultsinXactsbeing blocked,waitingforlocks.

• However,novalidationphase,norestarts

(modulodeadlocks).

14

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 40

TimestampCC

• Idea: Giveeachobjectaread-timestamp

(RTS)andawrite-timestamp(WTS),give eachXactatimestamp(TS)whenitbegins:

• fXactTj,andTS(Ti)<TS(Tj),thenaimust
• ccurbeforeaj.Otherwise,restart

violatingXact.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 41

WhenXactTwantstoreadObjectO

• IfTS(T)<WTS(O), thisviolatestimestamp
• rderofTw.r.t.writerofO.
• So,abortTandrestartitwithanew,largerTS.(If

restartedwithsameTS,Twillfailagain!Contrast useoftimestampsin2PLforddlkprevention.)

• IfTS(T)>WTS(O):

• ChangetoRTS(O)onreadsmustbewrittento

disk!Thisandrestartsrepresentoverheads.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 42

WhenXactTwantstoWriteObjectO

• IfTS(T)<RTS(O), thisviolatestimestamporder
• fTw.r.t.writerofO;abortandrestartT.
• IfTS(T)<WTS(O), violatestimestamporderof

Tw.r.t.writerofO.

• ThomasWriteRule: Wecansafelyignoresuch
• utdatedwrites;neednotrestartT!(T’swriteis

effectivelyfollowedbyanother write,withnointerveningreads.) Allowssomeserializablebutnon conflictserializableschedules:

• Else, allowTtowriteO.

T1 T2 R(A) W(A) Commit W(A) Commit

15

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 43

TimestampCCandRecoverability

• TimestampCCcanbemodified

toallowonlyrecoverableschedules:

updateWTS(O)whenthewriteisallowed.)

writerofOcommits.

• SimilartowritersholdingXlocksuntilcommit,

butstillnotquite2PL.

T1 T2 W(A) R(A) W(B) Commit

v Unfortunately,unrecoverable

schedulesareallowed:

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 44

MultiversionTimestampCC

• Idea:Letwritersmakea“new”copywhile

readersuseanappropriate“old”copy:

O O’ O’’

MAIN SEGMENT (Current versionsof DBobjects) VERSION POOL (Olderversionsthat maybeusefulfor someactivereaders.) v Readersarealwaysallowedtoproceed.

– Butmaybeblockeduntilwritercommits.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 45

MultiversionCC(Contd.)

• Eachversionofanobjecthasitswriter’sTSas

itsWTS,andtheTSoftheXactthatmost recentlyreadthisversionasitsRTS.

• Versionsarechainedbackward;wecan

discardversionsthatare“toooldtobeof interest”.

• EachXactisclassifiedasReader orWriter.
• Writermaywritesomeobject;Readerneverwill.
• XactdeclareswhetheritisaReaderwhenitbegins.

16

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 46

ReaderXact

• Foreachobjecttoberead:

(Startswithcurrentversioninthemain segmentandchainsbackwardthrough earlierversions.)

• Assumingthatsomeversionofeveryobject

existsfromthebeginningoftime,Reader Xactsareneverrestarted.

• However,mightblockuntilwriterofthe

appropriateversioncommits.

T

• ldnew

WTStimeline

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 47

WriterXact

• Toreadanobject,followsreaderprotocol.
• Towriteanobject:

withapointertoV,withWTS(CV)=TS(T), RTS(CV)=TS(T).(WriteisbuffereduntilT commits;otherXactscanseeTSvaluesbut can’treadversionCV.)

T

• ldnew

WTS

• CV

V

RTS(V)

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 48

TransactionSupportinSQL-92

• Eachtransactionhasanaccessmode,a

diagnosticssize,andanisolationlevel.

No No No Serializable Maybe No No RepeatableReads Maybe Maybe No ReadCommitted Maybe Maybe Maybe ReadUncommitted Phantom Problem Unrepeatable Read Dirty Read IsolationLevel

17

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 49

Summary

• Thereareseverallock-basedconcurrency

controlschemes(Strict2PL,2PL).Conflicts betweentransactionscanbedetectedinthe dependencygraph

• Thelockmanagerkeepstrackofthelocks

issued.Deadlockscaneitherbepreventedor detected.

• Naïvelockingstrategiesmayhavethe

phantomproblem

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 50

Summary(Contd.)

• Indexlockingiscommon,andaffects

performancesignificantly.

• Neededwhenaccessingrecordsviaindex.
• Neededforlockinglogicalsetsofrecords (index

locking/predicatelocking).

• Tree-structuredindexes:
• Straightforwarduseof2PLveryinefficient.
• Bayer-Schkolnickillustratespotentialfor

improvement.

• Inpractice,bettertechniquesnowknown;do

record-level,ratherthanpage-levellocking.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 51

Summary(Contd.)

• Multiplegranularitylockingreducestheoverhead

involvedinsettinglocksfornestedcollectionsofobjects (e.g.,afileofpages);shouldnotbeconfusedwithtree indexlocking!

• OptimisticCCaimstominimizeCCoverheadsinan

``optimistic’’environmentwherereadsarecommonand writesarerare.

• OptimisticCChasitsownoverheadshowever;most

realsystemsuselocking.

• SQL-92providesdifferentisolationlevelsthatcontrol

thedegreeofconcurrency

18

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 52

Summary(Contd.)

• TimestampCCisanotheralternativeto2PL;allows

someserializableschedulesthat2PLdoesnot(although converseisalsotrue).

• EnsuringrecoverabilitywithTimestampCCrequires

abilitytoblockXacts,whichissimilartolocking.

• MultiversionTimestampCCisavariantwhichensures

thatread-onlyXactsareneverrestarted;theycan alwaysreadasuitableolderversion.Additional

• verheadofversionmaintenance.