lec 11 transaction

TransactionsIntroductionandConcurrencyControlJianlinFengSchoolofSoftwareSUNYAT-SENUNIVERSITYcourtesyofJoeHellersteinforsomeslidesConcurrencyControlandRecoveryConcurrencyControlProvidecorrectandhighlyavailabledataaccessinthepresenceofconcurrentaccessbymanyusers.RecoveryEnsuresdatabaseisfaulttolerant,andnotcorruptedbysoftware,systemormediafailure24x7accesstomissioncriticaldataAboontoapplicationauthors!ExistenceofCC&Rallowsapplicationstobewrittenwithoutexplicitconcernforconcurrencyandfaulttolerance.QueryOptimizationandExecutionRelationalOperatorsFilesandAccessMethodsBufferManagementDiskSpaceManagementDBTheselayersmustconsiderconcurrencycontrolandrecovery(Transaction,Lock,RecoveryManagers)StructureofaDBMSTransactionsandConcurrentExecutionTransaction(“xact”):DBMS’sabstractviewofauserprogram(oractivity)Asequenceofreadsandwritesofdatabaseobjects.BatchofworkthatmustcommitorabortasanatomicunitTransactionManagercontrolsexecutionofxacts.User’sprogramlogicisinvisibletoDBMS!ArbitrarycomputationpossibleondatafetchedfromtheDBTheDBMSonlyseesdataread/writtenfrom/totheDB.Challenge:provideatomicxactstoconcurrentusers!Givenonlytheread/writeinterface.ASampleTransaction1:Begin_Transaction2:get(K1,K2,CHF)fromterminal3:SelectBALANCEIntoS1FromACCOUNTWhereACCOUNTNR=K1;4:S1:=S1-CHF;5:UpdateACCOUNTSetBALANCE=S1WhereACCOUNTNR=K1;6:SelectBALANCEIntoS2FromACCOUNTWhereACCOUNTNR=K2;7:S2:=S2+CHF;8:UpdateACCOUNTSetBALANCE=S2WhereACCOUNTNR=K2;9:InsertIntoBOOKING(ACCOUNTNR,DATE,AMOUNT,TEXT)Values(K1,today,-CHF,'Transfer');10:InsertIntoBOOKING(ACCOUNTNR,DATE,AMOUNT,TEXT)Values(K2,today,CHF,'Transfer');12:IfS10ThenAbort_Transaction11:End_TransactionConcurrency:Whybother?ThelatencyargumentResponsetime:theaveragetimetakentocompleteanxact.Ashortxactcouldgetstuckbehindalongxact,leadingtounpredictabledelaysinresponsetime.ThethroughputargumentThroughput:theaveragenumberofxactscompletedinagiventime.OverlappingI/OandCPUactivityreducestheamountoftimedisksandCPUareidle.ACIDpropertiesofTransactionExecutionsAtomicity:AllactionsintheXacthappen,ornonehappen.Consistency:IftheDB(Database)startsconsistent,itendsupconsistentatendofXact.Isolation:ExecutionofoneXactisisolatedfromthatofotherXacts.Durability:IfanXactcommits,itseffectspersist.ImplicationsofAtomicityandDurabilityAtransactionendsinoneoftwoways:commitaftercompletingallitsactions“commit”isacontractwiththecalleroftheDBabort(orbeabortedbytheDBMS)afterexecutingsomeactions.Orsystemcrashwhilethexactisinprogress;treatasabort.AtomicitymeanstheeffectofabortedxactsmustberemovedDurabilitymeanstheeffectsofacommittedxactmustsurvivefailures.DBMSensurestheabovebyloggingallactions:Undotheactionsofaborted/failedxacts.Redoactionsofcommittedxactsnotyetpropagatedtodiskwhensystemcrashes.TransactionConsistencyXactspreserveDBconsistencyGivenaconsistentDBstate,produceanotherconsistentDBstateDBconsistencyexpressedasasetofdeclarativeIntegrityConstraintsCREATETABLE/ASSERTIONstatementsXactsthatviolateICsareabortedThat’salltheDBMScanautomaticallycheck!Isolation(Concurrency)DBMSinterleavesactionsofmanyxactsActions=reads/writesofDBobjectsUsersshouldbeabletounderstandanxactwithoutconsideringtheeffectofotherconcurrentlyexecutingxacts.Eachxactexecutesasifitwererunningbyitself.Concurrentaccesseshavenoeffectonanxact’sbehaviorNeteffectmustbeidenticaltoexecutingallxactsforsomeserialorder.ScheduleofExecutingTransactionsAscheduleisalistofactions(READ,WRITE,ABORT,orCOMMIT)fromasetofxacts,andtheorderinwhichtwoactionsofanxactTappearsinaschedulemustbethesameastheorderinwhichtheyappearinT.AcompletescheduleisAschedulethatcontainseitheranabortoracommitforeachxact.AScheduleInvolvingTwoTransactions:AnIncompleteScheduleSerialScheduleSerialscheduleEachxactrunsfromstarttofinish,withoutanyinterveningactionsfromotherxacts.anexample:T1;T2.SerializableScheduleTwoschedulesareequivalentif:Theyinvolvethesameactionsofthesamexacts,andtheyleavetheDBinthesamefinalstate.AserializablescheduleoverasetSofxactsisaschedulewhoseeffectonanyconsistentdatabaseinstanceisguaranteedtobeidenticaltothatofsomecompleteserialscheduleoverthesetofcommittedxactsinS.ASerializableScheduleofTwoTransactionsTheresultofthisscheduleisequivalenttotheresultoftheserialschedule:T1;T2.ImportantPointsofSerializabilityExecutingxactsseriallyindifferentordersmayproducedifferentresults,butallarepresumedtobeacceptable;theDBMSmakesnoguaranteesaboutwhichofthemwillbetheoutcomeofaninterleavedexecution.UncommittedxactscanappearinaserializablescheduleS,buttheireffectsarecancelledoutbyUNDO.ConflictingActionsNeedaneasiercheckforequivalenceofschedulesUsenotionof“conflicting”actionsAnomalieswithinterleavedexecutionaresimplycausedbyconflictingactions.Twoactionsaresaidconflictif:Theyarebydifferentxacts,theyareonthesameobject,andatleastoneofthemisawrite.Threekindsofconflicts:Write-Read(WR)conflict,Read-Write(RW)andWrite-Write(WW)conflicts.Conflicts:AnomalieswithInterleavedExecutionReadingUncommittedData(WRConflicts,“dirtyreads”):Unrepeata