Lesson 4: Transaction Processing

4 Transaction Processing 4.1 What is a transaction? A transaction is a logical unit of processing in a DBMS which entails one or more database access operations. All types of database access operations which are held between the begin and end transaction statements are considered as a single logical transaction. During the transaction the database is inconsistent. Once the transaction is committed the database state is changed from one consistent state to another. In a nutshell, database transactions represent realworld events of any enterprise. These sets of instructions (statements) do several read and write operations on database. Thus, transactions can be considered as the various read and write operations done by the user programs on a database. 4.1.1 Key Notations in Transaction Management The key notations in transaction management are as follows:  Object. The smallest data item which is read or updated by a transaction  Transaction: It is termed as the execution of query in DBMS. Transaction is represented by the symbol T 4.1.2  Read Operation. Read operation on particular object is notated by symbol R (objectname).  Write Operation. Write operation on particular object is notated by symbol W (objectname).   Commit. This term used to denote the successful completion of one Transaction. Abort. This term used to denote the unsuccessful or interrupted Transaction. Transaction states The various states of a Database Transaction are listed below State Active State Partially Committed Committed State Failed State Terminated State Transaction types A transaction enters into an active state when the execution process begins. During this state read or write operations can be performed. A transaction goes into the partially committed state after the end of a transaction. A transaction is in committed state, after it has already completed its execution successfully. Moreover, all of its changes are recorded to the database permanently. A transaction is in failed state when any one of the checks fails or if the transaction is aborted while it is in the active state. A transaction is in terminated state when certain transactions which are leaving the system can't be restarted. 1 Figure 1.1 Transaction States in DBMS 4.1.3 Transaction Management A user program executed by DBMS may have several transactions. In the web environment, there is a possibility of several users accessing data that is stored in the same database. To improve the effective throughput of the DBMS, there is need for a mechanism to enable concurrent transactions. A Transaction Manager is responsible for scheduling the transactions and providing the safest path to complete available tasks. To maintain the data accuracy and consistency in consideration of concurrent access and system failures, the DBMS need to ensure that four important properties of a transaction are upheld. These properties are called as ACID properties. 4.1.4 ACID Properties of DBMS ACID is an acronym for Atomicity, Consistency, Isolation, and Durability. Atomicity Either all operations in a transaction are carried out or none are. The meaning is the transaction cannot be subdivided, and hence, it must be processed in its entirety or not at all. Users should not have to worry about the effect of incomplete transactions in case of any system crash occurs. Durability Durable means the changes are permanent. Once a transaction is committed, no subsequent failure of the database can reverse the effect of the transaction. If the system crashes before the changes made by a completed transaction are written to disk, then it should be remembered and restored during the system restart phase. If the transaction is interrupted in the middle way it leaves the database in the inconsistency state. These types of transactions are called as partial transactions. Partial transactions should be avoided in order to maintain consistency of a database. To reverse the actions done by the partial transactions, DBMS maintains certain log files. Each and every moment of disk writes 2 are recorded in this log before they are reflected to disk. These are used to undo the operations done when the system failure occurs. Consistency The database must remain in a consistent state after any transaction. No transaction should have any adverse effect on the data residing in the database. If the database was in a consistent state before the execution of a transaction, it must remain consistent after the execution of the transaction as well. Isolation In DBMS system, where many transactions are executed simultaneously and in parallel, each of the transaction will be carried out and executed as if it is the only transaction in the system. Thus, the execution of one transaction should not affect the execution of the other transactions. To enforce this concept DBMS has to maintain certain scheduling algorithms. One of the scheduling algorithms used is Serial Scheduling (will be discussed in later in the lesson). 4.2 Scheduling A schedule is series of operations from one transaction to another transaction. It is used to preserve the order of the operation in each of individual transactions. A schedule can either be a serial schedule, non-serial schedule, or a serializable schedule. 4.2.1 Serial scheduling In this scheduling method, transactions are executed one by one from the start to finish. For example, if there are two transactions T1 and T2, either all operations in T1 are completed before beginning the operations in T2, or all operations in T2 are completed before beginning the operations in T1. The two schedules illustrated below are serial. 4.2.2 Non-serial schedule A non-serial schedule is achieved whenever interleaving of operations between two or more transactions is allowed / possible. In this case there may be several possible orders in which the DBMS can execute the individual operations of the transactions involved. For example, the two schedules illustrated below are non-serial. 3 First schedule 4.2.3 Second schedule Serializable schedule A non-serial schedule is said to be serializable if its result is equal to the result of each individual transactions executed serially (one after the other). The serializability of schedules is used to find non-serial schedules that allow the transaction to execute concurrently without interfering with one another. It identifies the correctness of nonserial schedules. For example, the two non-serial schedules illustrated in section 4.2.2 are both non-serial; However, the first schedule is not serializable! The testing of serializability of a schedule is done using a serialization graph. 4.3 Interleaved Execution In order to achieve concurrency of transactions, operations of several transactions are intertwinned in a serial manner and executed one by one according to a schedule. In such a schedule, the switching of execution from operations of one transaction to operations of another transaction is referred to as Interleaved Execution. In the above example, consider two transactions (T1, T2) involving two objects (A, B). Commit denotes successful completion of both transactions. First, one read and one write operation are done on the object A by Transaction T2. This is followed by T1 which performs one read operation on object A. The process goes on with object B and finally both Transactions are concluded successfully. 4 4.3.1 Anomalies due to Interleaved Transactions If all the transactions in DBMS systems are doing read operations on the database, then no problem will arise. When the read and write operations done alternatively there is a possibility of some type of anomalies. These are classified into three categories. a) Write–Read Conflicts (WR Conflicts) b) Read–Write Conflicts (RW Conflicts) c) Write–Write Conflicts (WW Conflicts) 4.3.2 WR Conflicts (Dirty Read) This happens when the Transaction T1 is trying to read object A which has been modified by another Transaction T2, but has not yet completed (committed). This type read is called as dirty read. Example; Consider two Transactions T1 and T2, each of which, when executed in isolation, preserves database consistency. T1 transfers $100 from A to B, and T2 increments both A and B by 6%. Explanation Suppose if the transactions are interleaved according to the above schedule then the account transfer program T1 deducts $100 from account A, then the interest deposit program T2 reads the current values of accounts A and B and adds 6% interest to each, and then the account transfer program credits $100 to account B. The outcome of this execution will be different from the normal execution whereby the two instructions are executed one by one. This type of anomalies leaves the database in inconsistency state. 5 4.3.3 RW Conflicts (Unrepeatable Read) In this case anomalous behavior could result is that a Transaction T2 could change the value of an object A that has been read by a Transaction T1, while T1 is still in progress. If T1 tries to read A again it will get different results. This type of read is called as Unrepeatable Read. Example Explanation At first, T1 checks whether the money in account A is more than $100. Immediately it is interleaved and T2 also checks the account for money and reduce it by $100. After T2, T1 tries to reduce the same account A. If the initial amount in A is $101 then, after the execution of T2 only $1 will remain in account A. Now T1 will try to reduce it by $100. This makes the Database inconsistent. 4.3.4 WW Conflicts The third type of anomalous behavior is that one transaction is updating an object while another one is also in progress. Example Consider the two Transactions T1, T2. Consider the two objects A, B. 6 Explanation If A and B are two accounts and their values have to be kept equal always, Transaction T1 updates both objects to 3,000 and T2 updates both objects to 2,000. At first T1 updates the value of object A to 3,000. Immediately T2 makes A as 2,000 and B as 2,000 and committed. After the completion of T2, T1 updates B to 3,000. Now the value of A is 2,000 and value of B is 3,000, they are not equal. Constraint violated in this case due to serial scheduling. 7