TransactionManagementOverview Chapter16 - - PDF document

▶
transaction management overview
SMART_READER_LITE
LIVE PREVIEW

TransactionManagementOverview Chapter16 - - PDF document

Dec 01, 2023 356 likes •421 views

TransactionManagementOverview Chapter16 DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 1 Transactions

slide-1
SLIDE 1

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 1

TransactionManagementOverview

Chapter16

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 2

Transactions

  • Concurrentexecutionofuserprogramsisessentialfor

goodDBMSperformance.

✁

Becausediskaccessesarefrequent,andrelativelyslow,itis importanttokeepthecpuhummingbyworkingonseveral userprogramsconcurrently.

  • Auser’sprogrammaycarryoutmanyoperationson

thedataretrievedfromthedatabase,buttheDBMSis

  • nlyconcernedaboutwhatdataisread/written

from/tothedatabase.

  • Atransaction istheDBMS’sabstractviewofauser

program:asequenceofreadsandwrites.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 3

ConcurrencyinaDBMS

  • Userssubmittransactions,andcanthinkofeach

transactionasexecutingbyitself.

✁

ConcurrencyisachievedbytheDBMS,whichinterleaves actions(reads/writesofDBobjects)ofvarioustransactions.

✁

Eachtransactionmustleavethedatabaseinaconsistent stateiftheDBisconsistentwhenthetransactionbegins.

  • DBMSwillenforcesomeICs,dependingontheICs

declaredinCREATETABLEstatements.

  • Beyondthis,theDBMSdoesnotreallyunderstandthe

semanticsofthedata.(e.g.,itdoesnotunderstandhow theinterestonabankaccountiscomputed).

  • Issues: Effectofinterleaving transactions,andcrashes.
slide-2
SLIDE 2

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 4

AtomicityofTransactions

  • Atransactionmight commit aftercompletingallits

actions,oritcouldabort (orbeabortedbytheDBMS) afterexecutingsomeactions.

  • AveryimportantpropertyguaranteedbytheDBMS
  • foralltransactionsisthattheyareatomic. Thatis,a

usercanthinkofaXactasalwaysexecutingallits actionsinonestep,ornotexecutinganyactionsatall.

✁

DBMSlogs allactionssothatitcanundo theactionsof abortedtransactions.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 5

Example

  • Considertwotransactions(Xacts):

T1: BEGINA=A+100,B=B-100END T2: BEGINA=1.06*A,B=1.06*BEND

  • Intuitively,thefirsttransactionistransferring$100

fromB’saccounttoA’saccount.Thesecondis creditingbothaccountswitha6%interestpayment.

  • ThereisnoguaranteethatT1willexecutebeforeT2or

vice-versa,ifbotharesubmittedtogether.However, theneteffectmustbeequivalenttothesetwo transactionsrunningseriallyinsomeorder.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 6

Example(Contd.)

  • Considerapossibleinterleaving(schedule):

T1: A=A+100, B=B-100 T2: A=1.06*A, B=1.06*B

  • ThisisOK.Butwhatabout:

T1: A=A+100, B=B-100 T2: A=1.06*A,B=1.06*B

  • TheDBMS’sviewofthesecondschedule:

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

slide-3
SLIDE 3

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 7

SchedulingTransactions

  • Serialschedule: Schedulethatdoesnotinterleavethe

actionsofdifferenttransactions.

  • Equivalentschedules: Foranydatabasestate,theeffect

(onthesetofobjectsinthedatabase)ofexecutingthe firstscheduleisidenticaltotheeffectofexecutingthe secondschedule.

  • Serializableschedule:Aschedulethatisequivalentto

someserialexecutionofthetransactions. (Note:Ifeachtransactionpreservesconsistency,every serializableschedulepreservesconsistency.)

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 8

AnomalieswithInterleavedExecution

  • ReadingUncommittedData(WRConflicts,

“dirtyreads”):

  • UnrepeatableReads(RWConflicts):

T1: R(A),W(A), R(B),W(B),Abort T2: R(A),W(A),C T1: R(A), R(A),W(A),C T2: R(A),W(A),C

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 9

Anomalies(Continued)

  • OverwritingUncommittedData(WW

Conflicts):

T1: W(A), W(B),C T2: W(A),W(B),C

slide-4
SLIDE 4

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 10

Lock-BasedConcurrencyControl

  • StrictTwo-phaseLocking(Strict2PL)Protocol:
✁

EachXactmustobtainaS(shared)lockonobjectbefore reading,andanX(exclusive)lockonobjectbeforewriting.

✁

Alllocksheldbyatransactionarereleasedwhenthe transactioncompletes

✁

IfanXactholdsanXlockonanobject,nootherXactcan getalock(SorX)onthatobject.

  • Strict2PLallowsonlyserializableschedules.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 11

AbortingaTransaction

  • IfatransactionTi isaborted,allitsactionshavetobe

undone.Notonlythat,ifTjreadsanobjectlast writtenbyTi,Tj mustbeabortedaswell!

  • Mostsystemsavoidsuchcascadingaborts byreleasing

atransaction’slocksonlyatcommittime.

✁

IfTi writesanobject,Tj canreadthisonlyafterTi commits.

  • Inordertoundo theactionsofanabortedtransaction,

theDBMSmaintainsalog inwhicheverywriteis recorded.Thismechanismisalsousedtorecover fromsystemcrashes:allactiveXactsatthetimeofthe crashareabortedwhenthesystemcomesbackup.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 12

TheLog

  • Thefollowingactionsarerecordedinthelog:
✁

Tiwritesanobject:theoldvalueandthenewvalue.

  • Logrecordmustgotodisk before thechangedpage!
✁

Ticommits/aborts:alogrecordindicatingthisaction.

  • LogrecordsarechainedtogetherbyXactid,soit’s

easytoundoaspecificXact.

  • Logisoftenduplexedandarchived onstablestorage.
  • Alllogrelatedactivities(andinfact,allCCrelated

activitiessuchaslock/unlock,dealingwithdeadlocks etc.)arehandledtransparentlybytheDBMS.

slide-5
SLIDE 5

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 13

RecoveringFromaCrash

  • Thereare3phasesintheAries recoveryalgorithm:
✁

Analysis:Scanthelogforward(fromthemostrecent checkpoint)toidentifyallXactsthatwereactive,andalldirty pagesinthebufferpoolatthetimeofthecrash.

✁

Redo:Redoesallupdatestodirtypagesinthebufferpool, asneeded,toensurethatallloggedupdatesareinfact carriedoutandwrittentodisk.

✁

Undo:ThewritesofallXactsthatwereactiveatthecrash areundone(byrestoringthebeforevalueoftheupdate, whichisinthelogrecordfortheupdate),working backwardsinthelog.(Somecaremustbetakentohandle thecaseofacrashoccurringduringtherecoveryprocess!)

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 14

Summary

  • Concurrencycontrolandrecoveryareamongthe

mostimportantfunctionsprovidedbyaDBMS.

  • Usersneednotworryaboutconcurrency.
✁

Systemautomaticallyinsertslock/unlockrequestsand schedulesactionsofdifferentXactsinsuchawayasto ensurethattheresultingexecutionisequivalentto executingtheXactsoneaftertheotherinsomeorder.

  • Write-aheadlogging(WAL)isusedtoundothe

actionsofabortedtransactionsandtorestorethe systemtoaconsistentstateafteracrash.

✁

Consistentstate:OnlytheeffectsofcommitedXactsseen.