Database Management System - Chapter 15: Transaction Management

Chapter 15Transaction ManagementOutline Transaction basicsConcurrency controlRecovery managementTransaction design issuesWorkflow managementTransaction DefinitionSupports daily operations of an organizationCollection of database operationsReliably and efficiently processed as one unit of workNo lost dataInterference among multiple usersFailuresAirline Transaction ExampleSTART TRANSACTION Display greeting Get reservation preferences from user SELECT departure and return flight records If reservation is acceptable then UPDATE seats remaining of departure flight record UPDATE seats remaining of return flight record INSERT reservation record Print ticket if requested End If On Error: ROLLBACK COMMITATM Transaction ExampleSTART TRANSACTION Display greeting Get account number, pin, type, and amount SELECT account number, type, and balance If balance is sufficient then UPDATE account by posting debit UPDATE account by posting debit INSERT history record Display message and dispense cash Print receipt if requested End If On Error: ROLLBACK COMMITTransaction PropertiesAtomic: all or nothingConsistent: database must be consistent before and after a transactionIsolated: no unwanted interference from other usersDurable: database changes are permanent after the transaction completesTransaction Processing ServicesConcurrency controlRecovery managementService characteristicsTransparentConsume significant resourcesSignificant cost componentTransaction design importantConcurrency ControlProblem definitionConcurrency control problemsConcurrency control toolsConcurrency Control ProblemObjective: Maximize work performedThroughput: number of transactions processed per unit timeConstraint: No interference: serial effectInterference occurs on commonly manipulated data known as hot spots Lost Update Problem Uncommitted Dependency ProblemInconsistent Retrieval ProblemsInterference causes inconsistency among multiple retrievals of a subset of dataIncorrect summary Phantom readNon repeatable read Incorrect Summary ProblemLocking FundamentalsFundamental tool of concurrency controlObtain lock before accessing an itemWait if a conflicting lock is heldShared lock: conflicts with exclusive locksExclusive lock: conflicts with all other kinds of locksConcurrency control manager maintains the lock table Locking Granularity Deadlock (Mutual Waiting)Deadlock ResolutionDetectionOverhead is reasonable for deadlocks among 2 or 3 transactionsUsed by enterprise DBMSsTimeoutWaiting limitCan abort transactions that are not deadlockedTimeout interval is difficult to determineTwo Phase Locking (2PL)Protocol to prevent lost update problemsAll transactions must followConditionsObtain lock before accessing itemWait if a conflicting lock is heldCannot obtain new locks after releasing locks 2PL ImplementationOptimistic ApproachesAssumes conflicts are rareNo locksCheck for conflictsAfter each read and writeAt end of transactionEvaluationLess overheadMore variabilityRecovery ManagementDevice characteristics and failure typesRecovery toolsRecovery processesStorage Device BasicsVolatile: loses state after a shutdownNonvolatile: retains state after a shutdownNonvolatile is more reliable than volatile but failures can cause loss of dataUse multiple levels and redundant levels of nonvolatile storage for valuable dataFailure TypesLocalDetected and abnormal terminationLimited to a single transactionOperating SystemAffects all active transactionsLess common than local failuresDeviceAffects all active and past transactionsLeast commonTransaction LogHistory of database changesLarge storage overheadOperationsUndo: revert to previous stateRedo: reestablish a new stateFundamental tool of recovery management Transaction Log ExampleCheckpointsReduces restart work but adds overheadCheckpoint log recordWrite log buffers and database buffersCheckpoint interval: time between checkpointsTypes of checkpointsCache consistentFuzzyIncrementalOther Recovery ToolsForce writingCheckpoint timeEnd of transactionDatabase backupCompleteIncrementalRecovery from a Media FailureRestore database from the most recent backupRedo all committed transactions since the most recent backupRestart active transactions Recovery TimelineRecovery ProcessesDepend on timing of database writesImmediate update approach: Before commitLog records written first (write-ahead log protocol)Deferred update approachAfter commitUndo operations not needed Immediate Update Recovery Deferred Update RecoveryOracle Recovery FeaturesIncremental checkpointsImmediate update approachMean Time to Recover (MTTR) parameterMTTR advisorDynamic dictionary views to monitor recovery stateTransaction Design IssuesTransaction boundaryIsolation levelsDeferred constraint checkingSavepointsTransaction Boundary DecisionsDivision of work into transactionsObjective: minimize transaction durationConstraint: enforcement of important integrity constraintsTransaction boundary decision can affect hot spotsRegistration Form Example Transaction Boundary ChoicesOne transaction for the entire formOne transaction for the main form and one transaction for all subform recordsOne transaction for the main form and separate transactions for each subform recordAvoiding User Interaction TimeAvoid to increase throughputPossible side effects: user confusion due to database changesBalance increase in throughput with occurrences of side effectsMost situations increase in throughput more important than possible user confuusionIsolation LevelsDegree to which a transaction is separated from the actions of other transactionsBalance concurrency control overhead with interference problemsSome transactions can tolerate uncommitted dependency and inconsistent retrieval problemsSpecify using the SET TRANSACTION statementSQL Isolation Levels LevelXLocksSLocksPLocksInterferenceRead uncommittedNoneNoneNoneUncommitted dependencyRead committedLongShortNoneAll except uncommitted dependencyRepeatable readLongLongShort (S), Long (X)Phantom readsSerializableLongLongLongNoneScholar’s Lost Update Transaction ATimeTransaction BObtain S lock on SRT1Read SR (10)T2Release S lock on SRT3If SR > 0 then SR = SR -1T4T5Obtain S lock on SRT6Read SR (10)T7Release S lock on SRT8If SR > 0 then SR = SR -1 Obtain X lock on SRT9Write SR (9)T10CommitT11T12Obtain X lock on SRT13Write SR (9)Integrity Constraint TimingMost constraints checked immediatelyCan defer constraint checking to EOTSQLConstraint timing clause for constraints in a CREATE TABLE statementSET CONSTRAINTS statementSave PointsSome transactions have tentative actionsSAVEPOINT statement determines intermediate pointsROLLBACK to specified save pointsWorkflow ManagementWorkflow descriptionEnabling technologiesAdvanced transaction managementWorkflow BasicsSet of tasks to accomplish a business processHuman-oriented vs. computer-orientedAmount of judgmentAmount of automation Task structure vs. task complexityRelationships among tasksDifficulty of performing individual tasksWorkflow Classification Enabling TechnologiesDistributed object managementMany kinds of non traditional dataData often dispersed in locationWorkflow modelingSpecificationSimulationOptimizationAdvanced Transaction ManagementConversational transactionsTransactions with complex structureTransactions involving legacy systemsCompensating transactionsMore flexible transaction processing SummaryTransaction: user-defined collection of workDBMSs support ACID propertiesKnowledge of concurrency control and recovery important for managing databasesTransaction design issues are importantTransaction processing is an important part of workflow management