Unit - 1

1. Unit - 1 Transaction Processing

2. Introduction :- • A Transaction is a Logical Unit of Work. • It Begins with the Execution of a BEGIN TRANSACTION operation and end with the execution of a COMMIT or ROLLBACK operation. • Consider the following example which shows the pseudocode for a transaction whose purpose is to transfer the sum of $100 from account 123 to 456. • “Transfer money from one account to another” in fact involves two separate updates on the Database.

3. BEGIN TRANSACTION ; UPDATE ACC 123 {BALANCE := BALANCE - $100} ; IF any error occurred THEN GO TO UNDO ; END IF ; UPDATE ACC 456 {BALANCE := BALANCE + $100} ; IF any error occurred THEN GO TO UNDO ; END IF ; COMMIT ; GO TO FINISH ; UNDO : ROLLBACK ; FINISH : RETURN ;

4. No suppose if any incorrect state is arises in between these two updates, then one of the update is to be executed and other not. • Ideally, we would like to give an guarantee that both updates will be executed. • Unfortunately, it is impossible to provide any such guarantee-there is always a chance that things will go wrong, and go wrong more over at the worst possible movement. • For example, a system crash might occur between the two updates, or an arithmetic overflow might occurs on the second of them and so on. • But a system that supports the transaction management does provide the next best thing to such a guarantee. • Specifically, it guarantees that if the transaction execute some updates and then a failure occurs before the transaction reaches its planned transmission, then those updates will be undone.

5. Thus, the transaction either executes entirely or it is totally canceled. • The system that provides this atomicity is known as the transaction manager also known as a transaction processing monitor or TP monitor and the COMMIT and ROLLBACK operations are the key to the way6 it work. • The COMMIT operation signals the successful end-of-transmission: It tells the transaction manager that a logical unit of work has been successfully completed, the data base should now be in correct state again, and all of the updates made by that unit of work can be “COMMETED” (i.e., recorded in the database).

6. By contrast, the ROLLBACK operation signals unsuccessful end-of-transmission : It tells the transaction manager that something has gone wrong, the database might be in a incorrect state, and all of the updates made by the logical unit of work so far must be “ROLLBACK” (i.e., Undone). • In example, therefore, if we get through the two updates successfully, we issue a COMMIT to record those updates “permanently” in the database. If any thing goes wrong, however if either update rises an error condition we issue a ROLLBACK instead, to undo all updates so far.

7. Implicit ROLLBACK : • The example includes explicit tests for errors and issues an explicate ROLLBACK if an error is declared. • But we cannot assume that transaction always include explicate tests for all possible errors. • Therefore the system will issue an implicit ROLLBACK for any transaction that fails for any reason to reach its planned transmission. • Message Handling : • A typical transaction will not only update the database, it will also send some kind of message back to the end user indicating what has happened. • Message sending has additional implication of recovery.

8. Recovery Log : • You might be wondering how it is possible to undo an update. • The answer is that the system maintains a LOG or JOURNAL on tap or disk, on which detail of all updates are recorded. • Thus, if it becomes necessary to undo some particular update, the system can use the corresponding log record to restore the particular updated object to its previous value. • The log will consist of two portions : an active or online portion and an archive or offline portion. • The online portion is the portion used during normal system operation to record details of updates, and is normally kept on disk. When the online portion becomes full, its contains are transfer to the offline portion, which can be kept on tap.

9. Program execution is a sequence of transmission : • COMMITE and ROLLBACK terminate the transaction not the application program. • In general, a single program execution will consist of a sequence of several transactions running one after another.

10. Transaction Recovery :- • A transaction begins by executing a BEGIN TRANSACTION operation and end by execution either a COMMIT or a ROLLBACK operation. • COMMIT establish a Commit Point. • A commit point thus corresponds to the successful end of a logical unit of work, and hence to a point at which the database is supposed to in a correct state. • ROLLBACK in contrast, roll the database back to a state it was in at BEGIN TRANSACTION, which efficiently means back to the previous commit point. • The term “Database” should be understood to mean just that portion of the total database being accessed by the transaction under consideration.

11. Other transaction might be executing in parallel with that transaction and making changes to their own portions, and so “total database” might in fact not be in a fully correct state at a commit point. • Effect to database by executing COMMIT :- • All database updates made by the executing program since the previous commit point are committed; that is they are made permanent, in the sense that they are guaranteed to be recorded in the database. Prior to the commit point, all such updates must be regarded as tentative only-tentative in the sense they might subsequently be undone. Once committed, an update is guaranteed never to be undone.

12. All database positioning is lost and all tuple locks are released. “Database Positioning” is refers to the idea that any given time an executing program will typically have addressability to certain tuples in the database(via certain CURSORS) this addressability is lost via commit point. “Tuple Lock” Explain in next chapter. • If transaction successfully commits, then the system will guarantee that its updates will be permanently recorded in the database even if the system crashes very next movement.

13. It is quit possible that the system might crash after the COMMIT has been honored but before the updates have been physically written to the database-they might still be written in the main memory buffer and so lost at the time of crash. • Even if that happens, the system’s restart procedure will still record those updates in the database. • Thus, the restart procedure will recover any transactions that completed successfully but did not manage to get their updates physically written prior to the crash.

14. The ACID Properties :- • We can summarized the following section by saying that transactions process what are usually called “ACID Properties”. • Atomicity : • The atomicity property of a transaction requires that all operations of the transaction be completed, if not, the transaction is aborted. • In other word a transaction is treated as single, individual unit of work. Therefore, a transaction must executed and complete each operation in its logic before it commits its changes. • This property is ensure by the transaction recovery subsystem of a DBMS. • Atomicity is also known as All or Nothing.

15. Consistency :- • Database consistency is the property that every transaction sees a consistent database instance. • In other words, execution of a transaction must leave a database in either its prior stable state or a new stable state that reflects the new modification made by the transaction. • If the transaction fails, the database must be returned to the state it was in prior to the execution of the failed transaction. • If the transaction commits, the database must reflect the new changes. • Thus, all resources are always in a consistent state. • \the preservation of consistency is generally the responsibility of the programmers who write the database programs or of the DBMS module that enforce integrity constraints.

16. Isolation : • isolation property of the transaction means that the data used during the execution of the transaction cannot be used by a second transaction until the first one is completed. • This property isolate transaction from one another. • If a transaction T1 is being executed and it use the data item X that data item cannot be accessed by any other transaction (T2,…Tn) until T1 ends. • The transaction must act as if it is the only one running against the database. • No other transaction is allowed to see the changes made by a transaction until the transaction safely terminates and return a database into a new stable or prior stable state. • Thus, transaction do not interfere with each other.

17. Durability: • The durability property of transaction indicates the performance of the database’s consistent state. • It state the changes made by a transaction are permanent. • They cannot be lost by either a system failure or by the erroneous operation of a faulty transaction. • When a transaction is completed, a database reaches a consistent state and that state cannot be lost, even in the event of system’s failure. • Durability property is the responsibility of the recovery subsystem of the DBMS.

18. Transaction Log:- • To support the transaction processing, DBMS maintain a transaction records of every change made to the database into a LOG(also called as JOURNAL). • DBMS maintain this log to keep track of all transaction operations that affect the values of database items. • This helps DBMS to be able to recover from failures that affect the transactions. • Log is record of all transactions and the corresponding changes to the database. • The information stored in the log are used by the DBMS for a recovery requirement triggered by a ROLLBACK statement, which is program’s abnormal failure, a system failure or a disc crash.

19. Some Relational Database Management Systems (RDBMSs) use the transaction log to recover a database forward to a currently consistent state. • After a server failure, these RDBMSs automatically rolls back uncommitted transactions and rolls forward transaction that were committed but not yet written to the physical database storage. • The DBMS automatically update the transaction log while executing transactions that modify the database. • The transaction log stores before-and-after data about the database and any of the tables, rows and attribute values that participated in the transaction. • The BEGNINIG and the ENDING of the transaction are also recorded in the log. • The uses of transaction log increase the processing overhead of a DBMS and the overall cost of the system.

20. For each transaction following data ir recorded on the log : • A start-of-transaction marker. • The record identifier which could include who and where information. • The operation(s) perform on the records(E.g. INSERT, DELETE…). • The previous value(s) of the modified data. This information is required for undoing the changes made by partially completed transaction. It is called the undo log. If the modification made by the transaction, the previous value can be assume to be NULL. • The updated value(s) of the modified record(s). This information is required for making sure that the changes made by the transaction are in fact reflected in the database and can be used to redo these modifications.If the modification made by the transaction is deletion of the record the updated value can be assumed to be NULL. • A commit transaction marker if the transaction is committed otherwise an abort or rollback transaction marker.

21. Transaction log itself is a database. • It is managed by DBMS like any other database. • The transaction log is kept on disc, so it is not affected by any type of failure except for disk failure. • Because of the transaction log contain some of the most critical data in a DBMS, some implementation support periodic backups of transaction logs on several different disks or on tapes to reduce the risk of a system failure.

22. SYSTEM RECOVERY :- • The system must be prepared to recover, not only from purely “Local Failure” such as an overflow expression within an individual transaction, but also from “Global failures” such as a power outage. • A Local Failure, by definition, affects only the transaction in which the failure has actually occurred. • A Global Failure, by contrast, affects all of the transactions in progress at the time of failure and hence has significant system wide implication.

23. System Failure :- (e.g., Power Outage) • It affect all transactions currently in progress but do not physically damage the database. A system failure is sometime called “Soft Crash”. • Media Failure :- (e.g., Head Crash on the Disk) • Media Failure, which do cause damage to the database or some portion thereof, and affect at least those transactions currently using that portion. A media failure is some time called “Hard Crash”. • The key point regarding the system failure is that the contents of main memory are lost (In particular the database buffers are lost).

24. The precise state of any transaction that was in progress at the time of failure is therefore no longer known; Such a transaction therefore never be completed, and so must be undone that is, roll back-when the system is restarted. • Furthermore, it might be necessary to redo certain transactions at restart time that did successfully completed prior to the crash but did not manage to get their updates transferred from the buffers in the main memory to the physical database.

25. Question :- How does the system know at restart time which transactions to undo and which to redo? • Answer :- At certain prescribed intervals-typically whenever some number of records have been written to the log-the system automatically takes a checkpoint. Taking checkpoint involves : • Forcing the content of the main memory buffer out to the physical database. • Forcing a special checkpoint record out to the physical log. • The checkpoint record contain the list of all transactions that were in progress at the time the checkpoint was taken.

26. A System Failure has occurred at time tf. • The most recent checkpoint prior to time tf was taken at time tc.

27. Transaction type T1 completed successfully prior to time tc. • Transaction type T2 started prior to time tc and completed successfully after time tc and before time tf. • Transaction type T3 also started prior to time tc but did not completed by time tf. • Transaction type T4 started after then time tc and completed before the time tf. • Transaction type T5 also started after the time tc but did not complete before the time tf. • It should be clear that when the system is restarted, transactions of type T3 an T5 must be undone, and transactions of type T2 and T4 must be redone. • Note, that transaction of type T1 do not enter into the restart process at all, because their updates were forced to the database at time tc as a part of the checkpoint process.

28. Note too that transaction that completed unsuccessfully before time tf also do not enter into the restarted process at all. • At restarted time, therefore, the system first goes through the following procedure in order to identify all transactions of type T2-T5. • Start with two lists of transactions, the UNDO and the REDO list. • Set the UNDO list equal to the list of all transactions given in the most recent checkpoint record and the REDO list to empty. • Search forward through the log starting from the checkpoint record. • If BEGIN TRANSACTION log record is found for transaction T, add T to the UNDO list. • If a COMMIT log record is found for transaction T, move T from the UNDO list to the REDO list. • When the end of the log is reached, the UNDO and REDO list identify, respectively, transaction of type T3 and T5 and transactions of type T2 and T4.

29. The system now works backward through the log, undoing the transactions in the UNDO list; then it works forward again, redoing the transactions in the REDO list. • Restoring the database to the correct state by redoing works is sometimes called forward recovery. • Restoring it to a correct state by undoing work is sometimes called backward recovery. • Forward recovery redoes updates in the order in which they were originally done, while backward recovery undoes updates in the reverse of that order. • Finally, when all such recovery activity is completed, then and then the system is ready to accept new work.

30. ARIES :- • The description given up to now of the recovery procedure is very much simplified. • It shows “UNDO” operations being done before “REDO” operations • Early systems did work that way, but for efficiency reasons modern systems typically do the same things in other way; this schema is called ARIES. • ARIES operates in three broad phases : • Analysis : Build the UNDO and REDO list. • Redo : Start from a position in the log determined in the analysis phase and restore the database to the state it was in at the time of crash. • Undo : Undo the effect of the transactions that failed to commit.

31. Performing Basic TRANSACTION :- • A transaction is a sequence of one or more SQL statements that together form a logical unit of work. • The SQL statements that form the transaction are typically closely related and perform interdependent actions. • Each statement in the transaction performs some part of a task, but all of them are required to complete the task.

32. In a basic transaction, SQL statements are executed as a unit. • If one of the statements fails, the entire transaction should be rolled back; otherwise, any changes made by the statements are saved to the database. • To support the creation of a transaction, SQL provides the START TRANSACTION, COMMIT, and ROLLBACK statements. • The START TRANSACTION statement begins the transaction, the COMMIT statement commits changes to the database, and the ROLLBACK statement undoes any changes made by the statement and returns the database to the state it was in when the transaction began. • The transaction begins with the START TRANSACTION statement, which notifies SQL that the statements that follow should be treated as a unit, until the transaction has been ended.

34. Examples :- • Here are some examples of typical transactions for the sample database, along with the SQL statement sequence that comprises each transaction: • Add-an-orderTo accept a customer's order, the order entry program should : • query the PRODUCTS table to ensure that the product is in stock. • insert the order into the ORDERS table. • update the PRODUCTS table, subtracting the quantity ordered from the quantity-on-hand of the product. • update the SALESREPS table, adding the order amount to the total sales of the salesperson who took the order. • update the OFFICES table, adding the order amount to the total sales of the office where the salesperson works.

35. EXAMPLE-2 :- • Cancel-an-orderTo cancel a customer's order, the program should : • Delete the order from the ORDERS table. • Update the PRODUCTS table, adjusting the quantity-on-hand total for the product. • Update the SALESREPS table, subtracting the order amount from the salesperson's total sales. • Update the OFFICES table, subtracting the order amount from the office's total sales.

36. EXAMPLE-3 :- • Reassign-a-customer When a customer is reassigned from one salesperson to another, the program should : • Update the CUSTOMERS table to reflect the change. • Update the ORDERS table to show the new salesperson for all orders placed by the customer. • Update the SALESREPS table, reducing the quota for the salesperson losing the customer. • Update the SALESREPS table, raising the quota for the salesperson gaining the customer.

37. The statements in a transaction will be executed as an atomic unit of work in the database. Either all of the statements will be executed successfully, or none of the statements will be executed. • The DBMS is responsible for keeping this commitment even if the application program aborts or a hardware failure occurs in the middle of the transaction.