Transaction Management OverviewAdministriviaReview – Last WeekReview – The Big PictureConcurrency Control and RecoveryConcurrency Control & RecoveryRoadmapSlide 8TransactionsTransactions and Concurrent ExecutionACID properties of Transaction ExecutionsAtomicity and DurabilityTransaction ConsistencyIsolation (Concurrency)ExampleExample (Contd.)Scheduling TransactionsAnomalies with Interleaved ExecutionAnomalies (Continued)How to prevent anomolies? Locks!Locks not enoughLock-Based Concurrency ControlSlide 23Strict 2PL (cont)What about Durability?Introduction to Crash RecoveryThe LogLogging ContinuedARIES RecoverySummaryTransaction Management OverviewR & G Chapter 16Lecture 19There are three side effects of acid. Enhanced long term memory, decreased short term memory, and I forget the third.- Timothy LearyAdministrivia•Homework 3 Due Next Sunday, 7pm–Use “ANALYZE” to get correct statistics•No office hours Thursday, today 1:30-2:30 insteadReview – Last Week•Tree Indexes – BTrees and ISAM–good for range queries, o.k. for equality queries–ISAM for static data, B-Trees for dynamic data•Hash Indexes–best for equality queries, useless for range queries–Static Hashing–Extendable Hashing–Linear HashingReview – The Big Picture•Data Modelling–Relational & E-R–Database design and Functional Dependencies•Storing Data–File Organization–File Indexes, Trees and Hash Tables–Buffer Pool Management•Query Languages–SQL, Relational Algebra, Relational Calculus•Query Optimization–External Sorting–Join Algorithms–Query Plans, Cost Estimation•Database Applications•Transactions and Concurrency Control•Logging and Crash RecoveryConcurrency Control and Recovery•They help support A.C.I.D. propertiesDurabilityIsolationConsistencyAtomicity•More formal definitions shortlyConcurrency Control & Recovery•Concurrency Control–Provide correct and highly available access to data in the presence of concurrent access by large and diverse user populations•Recovery–Ensures database is fault tolerant, and not corrupted by software, system or media failure–7x24 access to mission critical data•Existence of CC&R allows applications to be written without explicit concern for concurrency and fault toleranceRoadmap•Overview (Today)•Concurrency Control (2 lectures)•Recovery (1-2 lectures)Query Optimizationand ExecutionRelational OperatorsFiles and Access MethodsBuffer ManagementDisk Space ManagementDBThese layers must consider concurrencycontrol and recovery(Transaction, Lock, Recovery Managers)Structure of a DBMSTransactions•Transaction is unit of atomicity–transaction should either finish, or never start•Transaction is a sequence of operations–for database, only reads, writes matterTransactions and Concurrent Execution •Transaction - DBMS’s abstract view of user program (or activity): –A sequence of reads and writes of database objects.–Unit of work that must commit and abort as a single atomic unit•Transaction Manager controls the execution of transactions.•User program may carry out many operations on the data retrieved from the database, but the DBMS is only concerned about what data is read/written from/to the database.•Concurrent execution of multiple transactions essential for good performance.–Disk is the bottleneck (slow, frequently used)–Must keep CPU busy w/many queries –Better response timeACID properties of Transaction Executions•AA tomicity: All actions in the Xact happen, or none happen.•CC onsistency: If each Xact is consistent, and the DB starts consistent, it ends up consistent.•II solation: Execution of one Xact is isolated from that of other Xacts.•D D urability: If a Xact commits, its effects persist.Atomicity and Durability•A transaction might commit after completing all its actions, or it could abort (or be aborted by the DBMS) after executing some actions. Also, the system may crash while the transaction is in progress. •Important properties:–Atomicity : Either executing all its actions, or none of its actions.–Durability : The effects of committed transactions 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.Transaction Consistency•A transaction performed on a database that is internally consistent will leave the database in an internally consistent state. •Consistency of database is expressed as a set of declarative Integrity Constraints –CREATE TABLE/ASSERTION statements•E.g. Each CS186 student can only register in one project group. Each group must have 3 students.–Application-level•E.g. Bank account of each customer must stay the same during a transfer from savings to checking account•Transactions that violate ICs are aborted.Isolation (Concurrency)•Concurrency is achieved by DBMS, which interleaves actions (reads/writes of DB objects) of various transactions.•DBMS ensures transactions do not step onto one another.•Each transaction executes as if it was running by itself.–Transaction’s behavior is not impacted by the presence of other transactions that are accessing the same database concurrently. –Net effect must be identical to executing all transactions for some serial order.–Users understand a transaction without considering the effect of other concurrently executing transactions.Example•Consider two transactions (Xacts):T1: BEGIN A=A+100, B=B-100 ENDT2: BEGIN A=1.06*A, B=1.06*B END•1st xact transfers $100 from B’s account to A’s•2nd 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*BThis is OK (same as T1;T2). But what about:T1: A=A+100, B=B-100 T2: A=1.06*A, B=1.06*B•Result: A=1166, B=960; A+B = 2126, bank loses $6 !•The DBMS’s 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•Serial schedule: Schedule that does not interleave the actions of different
View Full Document
Unlocking...