《數(shù)據(jù)庫系統(tǒng)》英文教學(xué)課件

《數(shù)據(jù)庫系統(tǒng)》英文教學(xué)課件,數(shù)據(jù)庫系統(tǒng),數(shù)據(jù)庫,系統(tǒng),英文,教學(xué),課件 Transaction Management OverviewR&G Chapter 16There are three side effects of acid.Enhanced long term memory,decreased short term memory,and I forget the third.-Timothy LearyConcurrency Control&RecoveryConcurrency ControlProvide correct and highly available data access in the presence of concurrent access by many usersRecoveryEnsures database is fault tolerant,and not corrupted by software,system or media failure24x7 access to mission critical dataA boon to application authors!Existence of CC&R allows applications be written without explicit concern for concurrency and fault toleranceQuery Optimizationand ExecutionRelational OperatorsFiles and Access MethodsBuffer ManagementDisk Space ManagementDBThese layers must consider concurrencycontrol and recovery(Transaction,Lock,Recovery Managers)Structure of a DBMSTransactions and Concurrent Execution Transaction(“xact”)-DBMSs abstract view of a user program(or activity):A sequence of reads and writes of database objects.Unit of work that must commit or abort as an atomic unitTransaction Manager controls the execution of transactions.Users program logic is invisible to DBMS!Arbitrary computation possible on data fetched from the DBThe DBMS only sees data read/written from/to the DB.Challenge:provide atomic transactions to concurrent users!Given only the read/write interface.Concurrency:Why bother?The latency argumentThe throughput argumentBoth are critical!ACID properties of Transaction Executions A tomicity:All actions in the Xact happen,or none happen.C onsistency:If each Xact is consistent,and the DB starts consistent,it ends up consistent.I solation:Execution of one Xact is isolated from that of other Xacts.D urability:If a Xact commits,its effects persist.Atomicity and DurabilityA transaction ends in one of two ways:commit after completing all its actions“commit”is a contract with the caller of the DBabort(or be aborted by the DBMS)after executing some actions.Or system crash while the xact is in progress;treat as abort.Two important properties for a transaction:Atomicity:Either execute all its actions,or none of themDurability:The effects of a committed xact must survive failures.DBMS ensures the above by logging all actions:Undo the actions of aborted/failed transactions.Redo actions of committed transactions not yet propagated to disk when system crashes.A.C.I.D.Transaction ConsistencyTransactions preserve DB consistencyGiven a consistent DB state,produce another consistent DB stateDB Consistency expressed as a set of declarative Integrity Constraints CREATE TABLE/ASSERTION statementsE.g.Each DB student can only register in one project group.Each group must have 2 students.Application-levelE.g.Bank account total of each customer must stay the same during a“transfer”from savings to checking accountTransactions that violate ICs are abortedThats all the DBMS can automatically check!A.C.I.D.Isolation(Concurrency)DBMS interleaves actions of many xacts concurrentlyActions=reads/writes of DB objectsDBMS ensures xacts do not“step onto”one another.Each xact executes as if it were running by itself.Concurrent accesses have no effect on a Transactions behaviorNet effect must be identical to executing all transactions for some serial order.Users&programmers think about transactions in isolationWithout considering effects of other concurrent transactions!A.C.I.D.ExampleConsider two transactions(Xacts):T1:BEGIN A=A+100,B=B-100 ENDT2:BEGIN A=1.06*A,B=1.06*B END1st xact transfers$100 from Bs account to As2nd credits both accounts with 6%interest.Assume at first A and B each have$1000.What are the legal outcomes of running T1 and T2?T1;T2(A=1166,B=954)T2;T1(A=1160,B=960)In either case,A+B=$2000*1.06=$2120 There is no guarantee that T1 will execute before T2 or vice-versa,if both are submitted together.Example(Contd.)Consider a possible interleaved schedule:T1:A=A+100,B=B-100 T2:A=1.06*A,B=1.06*BvThis is OK(same as T1;T2).But what about:T1:A=A+100,B=B-100 T2:A=1.06*A,B=1.06*BResult:A=1166,B=960;A+B=2126,bank loses$6!The DBMSs view of the second schedule:T1:R(A),W(A),R(B),W(B)T2:R(A),W(A),R(B),W(B)Scheduling Transactions:DefinitionsSerial schedule:no concurrencyDoes not interleave the actions of different transactions.Equivalent schedules:same result on any DB stateFor any database state,the effect(on the set of objects in the database)of executing the first schedule is identical to the effect of executing the second schedule.Serializable schedule:equivalent to a serial scheduleA schedule that is equivalent to some serial execution of the transactions.(Note:If each transaction preserves consistency,every serializable schedule preserves consistency.)Anomalies with Interleaved ExecutionReading Uncommitted Data(WR Conflicts,“dirty reads”):Unrepeatable Reads(RW Conflicts):T1:R(A),W(A),R(B),W(B),AbortT2:R(A),W(A),CT1:R(A),R(A),W(A),CT2:R(A),W(A),CAnomalies(Continued)Overwriting Uncommitted Data(WW Conflicts):T1:W(A),W(B),CT2:W(A),W(B),CLock-Based Concurrency ControlA simple mechanism to allow concurrency but avoid the anomalies just describedTwo-phase Locking(2PL)Protocol:Always obtain a S(shared)lock on object before readingAlways obtain an X(exclusive)lock on object before writing.If an Xact holds an X lock on an object,no other Xact can get a lock(S or X)on that object.DBMS internally enforces the above locking protocolTwo phases:acquiring locks,and releasing themNo lock is ever acquired after one has been released“Growing phase”followed by“shrinking phase”.Lock Manager tracks lock requests,grants locks on database objects when they become available.Strict 2PL2PL allows only serializable schedules but is subjected to cascading aborts.Example:rollback of T1 requires rollback of T2!To avoid Cascading aborts,use Strict 2PLStrict Two-phase Locking(Strict 2PL)Protocol:Same as 2PL,except:A transaction releases no locks until it completesT1:R(A),W(A),AbortT2:R(A),W(A),R(B),W(B)Introduction to Crash RecoveryRecovery ManagerUpon recovery from crash:Must bring DB to a consistent transactional state Ensures transaction Atomicity and DurabilityUndoes actions of transactions that do not commitRedoes lost actions of committed transactions lost during system failures or media failures Recovery Manager maintains log information during normal execution of transactions for use during crash recoveryThe LogLog consists of“records”that are written sequentially.Stored on a separate disk from the DBTypically chained together by Xact idLog is often duplexed and archived on stable storage.Log stores modifications to the databaseif Ti writes an object,write a log record with:If UNDO required need“before image”IF REDO required need“after image”.Ti commits/aborts:a log record indicating this action.Need for UNDO/REDO depend on Buffer Mgr(!)UNDO required if uncommitted data can overwrite stable version of committed data(STEAL buffer management).REDO required if xact can commit before all its updates are on disk(NO FORCE buffer management).Logging ContinuedWrite Ahead Logging(WAL)protocolLog record must go to disk before the changed page!implemented via a handshake between log manager and the buffer manager.All log records for a transaction(including its commit record)must be written to disk before the transaction is considered“Committed”.All log related activities are handled transparently by the DBMS.As was true of CC-related activities such as lock/unlock,dealing with deadlocks,etc.ARIES RecoveryThere are 3 phases in ARIES recovery protocol:Analysis:Scan the log forward(from the most recent checkpoint)to identify all Xacts that were active,and all dirty pages in the buffer pool at the time of the crash.Redo:Redoes all updates to dirty pages in the buffer pool,as needed,to ensure that all logged updates are in fact carried out and written to disk.Undo:The writes of all Xacts that were active at the crash are undone(by restoring the before value of the update,as found in the log),working backwards in the log.At the end-all committed updates and only those updates are reflected in the database.Some care must be taken to handle the case of a crash occurring during the recovery process!SummaryConcurrency control and recovery are among the most important functions provided by a DBMS.Concurrency control(Isolation)is automatic.DBMS issues proper Two-Phase Locking(2PL)requestsEnforces lock discipline(S&X)End result promised to be“serializable”:equivalent to some serial scheduleAtomicity and Durability ensured by Write-Ahead Logging(WAL)and recovery protocolused to undo the actions of aborted transactions(no subatomic stuff visible after recovery!)used to redo the lost actions of committed transactions