!" #Dr. Chris Irwin Davis !Email: [email protected] Phone: (972) 883-3574 Office: ECSS 4.705Chapter 21: Transaction ProcessingCS-6360 Database Design!" #© 2014 Chris Irwin Davis■21.1 Introduction to Transaction Processing!■21.2 Transaction and System Concepts!■21.3 Desirable Properties of Transactions!■21.4 Characterizing Schedules Based on Recoverability!■21.5 Characterizing Schedules Based on Serializability2!" #© 2014 Chris Irwin DavisIntroduction to Transaction Processing■Single-User System:!■At most one user at a time can use the system. !■Multiuser System:!■Many users can access the system concurrently.!■Concurrency!■Interleaved processing:!□Concurrent execution of processes is interleaved in a single CPU!■Parallel processing:!□Processes are concurrently executed in multiple CPUs. 3!" #Transaction Processing Hierarchy■Schedule!■Transaction!□Operation!- Sub-operation4!" #Introduction to Transaction Processing ■A Transaction:!■Logical unit of database processing that includes one or more access operations: read (retrieval) or write (insert or update, delete).!■A transaction (set of operations) may be !■stand-alone specified in a high level language like SQL !■submitted interactively, or !■may be embedded within a program.!■Transaction boundaries:!■Begin and End transaction.!■An application program may contain several transactions separated by the Begin and End transaction boundaries.5!" #© 2014 Chris Irwin DavisIntroduction to Transaction Processing ■Simple Model of a Database !■for purposes of discussing transactions:!■A database is a collection of named data items!■Granularity of data - a field, a record , or a whole disk block!■Concepts are independent of granularity!■Basic operations are read and write!■read_item(X): Reads a database item named X into a program variable. To simplify our notation, we assume that the program variable is also named X.!■write_item(X): Writes the value of program variable X into the database item named X.6!" #Introduction to Transaction Processing READ AND WRITE OPERATIONS:!•Basic unit of data transfer from the disk to the computer main memory is one block. !•In general, a data item (what is read or written) will be the field of some record in the database, although it may be a larger unit such as a record or even a whole block.!•read_item(X) command includes the following steps:!°Find the address of the disk block that contains item X.!°Copy that disk block into a buffer in main memory (if that disk block is not already in some main memory buffer).!°Copy item X from the buffer to the program variable named X. 7!" #Introduction to Transaction Processing READ AND WRITE OPERATIONS (cont.):!•write_item(X) command includes the following steps:!°Find the address of the disk block that contains item X.!°Copy that disk block into a buffer in main memory (if that disk block is not already in some main memory buffer).!°Copy item X from the program variable named X into its correct location in the buffer.!°Store the updated block from the buffer back to disk (either immediately or at some later point in time). 8!" #Two Sample Transactions9!" #Two Sample Transactions10Transfer Arrival!" #Introduction to Transaction Processing Why Concurrency Control is needed:!•The Lost Update Problem!°This occurs when two transactions that access the same database items have their operations interleaved in a way that makes the value of some database item incorrect. !•The Temporary Update (or Dirty Read) Problem !°This occurs when one transaction updates a database item and then the transaction fails for some reason (see Section 21.1.4). The updated item is accessed by another transaction before it is changed back (reverted) to its original value. 11!" #Introduction to Transaction Processing •The Incorrect Summary Problem!°If one transaction is calculating an aggregate summary function on a number of records while other transactions are updating some of these records, the aggregate function may calculate some values before they are updated and others after they are updated.!•The Unrepeatable Read Problem!°Another problem that may occur is called unrepeatable read, where a transaction T reads the same item twice and the item is changed by another transaction Tʹ′ between the two reads. Hence, T receives different values for its two reads of the same item. !°This may occur, for example, if during an airline reservation transaction, a customer inquires about seat availability on several flights. When the customer decides on a particular flight, the transaction then reads the number of seats on that flight a second time before completing the reservation, and it may end up reading a different value for the item.12!" #Introduction to Transaction Processing Concurrent execution is uncontrolled: "(a) The lost update problem. 13!" #Introduction to Transaction Processing 14Concurrent execution is uncontrolled: "(b) The temporary update problem.!" #Introduction to Transaction Processing 15Concurrent execution is uncontrolled: "(c) The incorrect summary problem.!" #Introduction to Transaction Processing Why recovery is needed: !• Whenever a transaction is submitted to a DBMS for execution, the system is responsible for making sure that either… !- All the operations in the transaction are completed successfully and their effect is recorded permanently in the database, or !- The transaction does not have any effect on the database or any other transactions (i.e. full revert)!• In the first case, the transaction is said to be committed, whereas in the second case, the transaction is aborted.16!" #Introduction to Transaction Processing• The DBMS must not permit only 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.17!" #Types of FailuresFailures are generally classified as transaction, system, and media failures. There are several possible reasons for a transaction to fail in the middle of execution: !1. A computer failure (system crash):!A hardware or software error occurs in the computer system during transaction execution. If the hardware crashes, the contents of the
View Full Document
Unlocking...