Chapter 21 Transaction Processing CS 6360 Database Design Dr Chris Irwin Davis Email cid021000 utdallas edu Phone 972 883 3574 O ce ECSS 4 705 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 Serializability 2014 Chris Irwin Davis 2 Introduction 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 2014 Chris Irwin Davis 3 Transaction Processing Hierarchy Schedule Transaction Operation Sub operation 4 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 Introduction 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 2014 Chris Irwin Davis 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 Transactions 9 Two Sample Transactions Transfer Arrival 10 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 Concurrent execution is uncontrolled b The temporary update problem 14 Introduction to Transaction Processing Concurrent execution is uncontrolled c The incorrect summary problem 15 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 Failures Failures 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 computer s internal memory may be lost 2 A transaction or system error Some operation in the transaction may cause it to fail such as integer overflow or division by zero Transaction failure may also occur because of erroneous parameter values or because of a logical programming error In addition the user may interrupt the transaction during its execution 18 Types of Failures
View Full Document
Unlocking...