Chapter20_2021 newV.pptx

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe  CHAPTER 20 Introduction to Transaction Processing Concepts and Theory Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Introduction Transaction Describes local unit of database proces...

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe  CHAPTER 20 Introduction to Transaction Processing Concepts and Theory Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Introduction Transaction Describes local unit of database processing Transaction processing systems Systems with large databases and hundreds of concurrent users Require high availability and fast response time Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20- 3 20.1 Introduction to Transaction Processing Single-user DBMS At most one user at a time can use the system Example: home computer Multiuser DBMS Many users can access the system (database) concurrently Example: airline reservations system Multiple users can access databases—and use computer systems—simultaneously because of the concept of multiprogramming Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20- 4 Introduction to Transaction Processing (cont’d.) Multiprogramming Allows operating system to execute multiple processes concurrently Executes commands from one process, then suspends that process and executes commands from another process, etc. Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20- 5 Introduction to Transaction Processing (cont’d.) Interleaved processing Parallel processing Processes C and D in figure below Figure 20.1 Interleaved processing versus parallel processing of concurrent transactions Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20-6 Transactions Transaction: an executing program Forms logical unit of database processing Begin and end transaction statements Specify transaction boundaries Read-only transaction Read-write transaction Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20- 7 Database Items Database represented as collection of named data items Size of a data item called its granularity Data item size varies, it can be: Record Disk block Attribute value of a record Transaction processing concepts independent of item granularity Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20- 8 Read and Write Operations read_item(X) Reads a database item named X into a program variable named X write_item(X) Writes the value of program variable X into the database item named X Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20- 9 read_item(X) 1. Find the address of the disk block that contains item X. 2. Copy that disk block into a buffer in main memory (if that disk block is not already in some main memory buffer). The size of the buffer is the same as the disk block size. 3. Copy item X from the buffer to the program variable named X. Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 16- 10 write_item(X) 1. Find the address of the disk block that contains item X. 2. Copy that disk block into a buffer in main memory (if that disk block is not already in some main memory buffer). 3. Copy item X from the program variable named X into its correct location in the buffer. 4. Store the updated disk block from the buffer back to disk (either immediately or at some later point in time). Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 16- 11 Read and Write Operations (cont’d.) Read set of a transaction Set of all items read Write set of a transaction Set of all items written Figure 20.2 Two sample transactions (a) Transaction T1 (b) Transaction T2 Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20- 12 DBMS Buffers DBMS will maintain several main memory data buffers in the database cache When buffers are occupied, a buffer replacement policy is used to choose which buffer will be replaced Example policy: least recently used Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20- 13 Concurrency Control Transactions submitted by various users may execute concurrently Access and update the same database items Some form of concurrency control is needed The lost update problem Occurs when two transactions that access the same database items have operations interleaved Results in incorrect value of some database items Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20- 14 The Lost Update Problem Figure 20.3 Some problems that occur when concurrent execution is uncontrolled (a) The lost update problem Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20-15 The Temporary Update (dirty read) Problem Figure 20.3 (cont’d.) Some problems that occur when concurrent execution is uncontrolled (b) The temporary update problem Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20-16 The Incorrect Summary Problem Figure 20.3 (cont’d.) Some problems that occur when concurrent execution is uncontrolled (c) The incorrect summary problem Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20-17 The Unrepeatable Read Problem Transaction T reads the same item twice Value is changed by another transaction T between the two reads T receives different values for the two reads of the same item Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20- 18 Why Recovery is Needed Committed transaction Effect recorded permanently in the database Aborted transaction Does not affect the database The DBMS must not permit some operations of a transaction T to be applied to the database while other operations of T are not, because the whole transaction is a logical unit of database processing. If a transaction fails after executing some of its operations but before executing all of them, the operations already executed must be undone and have no lasting effect. Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20- 19 Types of transaction failures(most common) Computer failure (system crash): Media failure, HW, SW,network Transaction or system error: Div/0, integer overflow, error in parameter, logical programming error, user may interrupt T Local errors or exception conditions detected by the transaction: Data may not be found, exception condition(insufficient balance) Concurrency control enforcement: Violation such as serializability deadlock Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 16- 20 Types of transaction failures(not common) Disk failure: R/W head Physical problems or catastrophes: Power or AC failure System must keep sufficient information to recover quickly from the failure Disk failure or other catastrophes have long recovery times Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 16- 21 20.2 Transaction and System Concepts System must keep track of when each transaction starts, terminates, commits, and/or aborts BEGIN_TRANSACTION READ or WRITE END_TRANSACTION COMMIT_TRANSACTION ROLLBACK (or ABORT) Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20- 22 Transaction and System Concepts (cont’d.) Figure 20.4 State transition diagram illustrating the states for transaction execution Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20-23 The System Log System log keeps track of transaction operations Sequential, append-only file Not affected by failure (except disk or catastrophic failure) Log buffer Main memory buffer When full, appended to end of log file on disk Log file is backed up periodically Undo and redo operations based on log possible Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20- 24 Example of entry in log file 1. [start_transaction, T]. Indicates that transaction T has started execution. 2. [write_item, T, X, old_value, new_value]. Indicates that transaction T has changed the value of database item X from old_value to new_value. 3. [read_item, T, X]. Indicates that transaction T has read the value of database item X. 4. [commit, T]. Indicates that transaction T has completed successfully, and affirms that its effect can be committed (recorded permanently) to the database. 5. [abort, T]. Indicates that transaction T has been aborted. Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 16- 25 20.6 Transaction Support in SQL No explicit Begin_Transaction statement Every transaction must have an explicit end statement COMMIT ROLLBACK Access mode is READ ONLY or READ WRITE Diagnostic area size option Integer value indicating number of conditions held simultaneously in the diagnostic area Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20- 26 Transaction Support in SQL (cont’d.) Isolation level option Dirty read Nonrepeatable read Phantoms Table 20.1 Possible violations based on isolation levels as defined in SQL Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 20- 27