Transaction Processing And Concurrency Control

1. Transaction Processing And Concurrency Control

2. INTRODUCTION Transaction is a logical unit of work that represents real-world events of any organisation or an enterprise whereas concurrency control is the management of concurrent transaction execution. Transaction processing systems execute database transactions with large databases and hundreds of concurrent users, for example, railway and air reservations systems, banking system, credit card processing, stock market monitoring, super market inventory and checkouts and so on.

3. Transaction processing and concurrency control form important activities of any database system.

4. TRANSACTION CONCEPTS A transaction is a logical unit of work of database processing that includes one or more database access operations. A transaction can be defined as an action or series of actions that is carried out by a single user or application program to perform operations for accessing the contents of the database. The operations can include retrieval, (Read), insertion (Write), deletion and modification.

5. A transaction must be either completed or aborted. A transaction is a program unit whose execution may change the contents of a database. It can either be embedded within an application program or can be specified interactively via a high-level query language such as SQL. Its execution preserves the consistency of the database.

6. No intermediate states are acceptable. If the database is in a consistent state before a transaction executes, then the database should still be in consistent state after its execution. Therefore, to ensure these conditions and preserve the integrity of the database a database transaction must be atomic (also called serialisability). Atomic transaction is a transaction in which either all actions associated with the transaction are executed to completion or none are performed.

7. In other words, each transaction should access shared data without interfering with the other transactions and whenever a transaction successfully completes its execution; its effect should be permanent. However, if due to any reason, a transaction fails to complete its execution (for example, system failure) it should not have any effect on the stored database.

8. This basic abstraction frees the database application programmer from the following concerns: Inconsistencies caused by conflicting updates from concurrent users. Partially completed transactions in the event of systems failure. User-directed undoing of transactions.

9. A transaction is a sequence of READ and WRITE actions that are grouped together to from a database access. Whenever we Read from and/or Write to (update) the database, a transaction is created. A transaction may consist of a simple SELECT operation to generate a list of table contents, or it may consist of a series of related UPDATE command sequences.

10. A transaction can include the following basic database access operations: Read_item(X): This operation reads a database item named X into a program variable Y. Execution of Read-item(X) command includes the following steps: Find the address of disk block that contains the item X. Copy that disk block into a buffer in main memory. Copy item X from the buffer to the program variable named Y.

11. Write_item(X): This operation writes the value of a program variable Y into the database item named X. Execution of 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. Copy item X from the program variable named Y into its correct location in the buffer. Store the updated block from the buffer back to disk.

12. Example of transaction that updates columns (attributes) in several relation (table) rows (tuples) by incrementing their values by 500: BEGIN_TRANSACTION_1: READ (TABLE = T1, ROW = 15, OBJECT = COL1); :COL1 = COL1 + 500; WRITE (TABLE = T1, ROW = 15, OBJECT = COL1, VALUE =:COL1); READ (TABLE = T2, ROW = 15, OBJECT = COL2); :COL2 = COL2 + 500; WRITE (TABLE = T2, ROW = 30, OBJECT = COL2, VALUE =:COL2); END_OF_TRANSACTION_1;

13. As can be seen from the above update operation, the transaction is basically divided into three pairs of READ and WRITE operations. Each operation reads the value of a column from a table and increments it by the given amount. It then proceeds to write to new value back into the column before proceeding to the next table.

14. Fig. example of a typical loan transaction that updates a salary database table of M/s KLY Assocrates. In this example, a loan amount of INR 10000.00 is being subtracted from an already stored loan value of INR 80000.00. After the update, it leaves INR 70000.00 as loan balance in the database. transaction that changes the contents of the database must alter the database from one consistent state to another.

15. A consistent database state is one in which all data integrity constraints are satisfied. To ensure database consistency, every transaction must begin with the database in a known consistent state.

17. Transaction Execution and Problems A transaction which successfully completes its execution is said to have been committed. Otherwise, the transaction is aborted. Thus, if a committed transaction performs any update operation on the database, its effect must be reflected on the database even if there is a failure.

18. A transaction can be in one of the following states: Active state: After the transaction starts its operation. Partially committed: When the last state is reached. Aborted: When the normal execution can no longer be performed. Committed: After successful completion of transaction.

19. A transaction may be aborted when the transaction itself detects an error during execution which it cannot recover from, for example, a transaction trying to debit loan amount of an employee from his insufficient gross salary. A transaction may also be aborted before it has been committed due to system failure or any other circumstances beyond its control. A transaction is said to be in a committed state if it has partially committed and it can be ensured that it will never be aborted.

21. Fig a state transition diagram that describes how a transaction moves through its execution states. A transaction goes into an active state immediately after it starts execution, where it can issue READ and WRITE operations. When the transaction ends, it moves to the partially committed state. To this point, some recovery protocols need to ensure that a system failure will not result in an inability to record the changes of the transaction permanently. Once this check is successful, the transaction is said to have reached its commit point and enters the committed state.

22. Once a transaction is committed, it has concluded its execution successfully and all its changes must be recorded permanently in the database. However, a transaction can go to an aborted state if one of checks fails or if the transaction is aborted during its active state. The transaction may then have to be rolled back to undo the effect of its WRITE operations on the database. In the terminated state, the transaction information maintained in system tables while the transaction has been running is removed. Failed or aborted transactions may be restarted later, either automatically or after being resubmitted by the user as new transactions.

23. Transaction Execution with SQL The American National Standards Institute (ANSI) has defined standards that govern SQL database transactions. Transaction support is provided by two SQL statements namely COMMIT and ROLLBACK.

24. The ANSI standards require that, when a transaction sequence is initiated by a user or an application program, it must continue through all succeeding SQL statements until one of the following four events occur: A COMMIT statement is reached, in which case all changes are permanently recorded within the database. The COMMIT statement automatically ends the SQL transaction. The COMMIT operations indicates successful end-of-transaction.

25. A ROLLBACK statement is reached, in which case all the changes are aborted and the database is rolled back to its previous consistent state. The ROLLBACK operation indicates unsuccessful end-of-transaction

26. The end of a program is successfully reached, in which case all changes are permanently recorded within the database. This action is equivalent to COMMIT. The program is abnormally terminated, in which case the changes made in the database are aborted and the database is rolled back to its previous consistent state. This action is equivalent to ROLLBACK.

27. Example of COMMIT, which updates an employee's loan balance (EMP_LOAN-BAL) in the table EMPLOYEE. UPDATE EMPLOYEE SET EMP-LOAN-B AL = EMP-LOAN-B AL – 10000 WHERE EMP-ID ='E0001'; COMMIT;

28. As shown in the above example, a transaction begins implicitly when the first SQL statement is encountered. Not all SQL implementations follow the ANSI standard. Some SQL statement use following transaction execution statement to indicate the beginning and end of a new transaction: BEGIN TRANSACTION_T1, READ (TABLE = EMPLOYEE, EMP-ID = ' E0001', OBJECT = EMP-LOAN-B AL); : EMP-LOAN-B AL = EMP-LOAN-B AL - 10000; WRITE (TABLE = EMPLOYEE, EMP-ID = ' E0001', OBJECT = EMP-LOAN-BAL, VALUE =: EMP-LOAN-BAL); END TRANSACTION_T1;

29. Transaction Properties A transaction must have the following four properties, called ACID properties (also called ACIDITY of a transaction), to ensure that a database remains stable state after the transaction is executed: Atomicity. Consistency. Isolation. Durability.

30. Atomicity: The atomicity property of a transaction requires that all operations of a transaction be completed, if not, the transaction is aborted. In other words, a transaction is treated as single, individual logical unit of work. Therefore, a transaction must execute and complete each operation in its logic before it commits its changes.

31. As stated earlier, the transaction is considered as one operation even though there are multiple read and writes. Thus, transaction completes or fails as one unit. The atomicity property of transaction is ensured by the transaction recovery subsystem of a DBMS. In the event of a system crash in the midst of transaction execution, the recovery techniques undo any effects of the transaction on the database.

32. 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 modifications (updates) 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.

33. 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 enforces integrity constraints. A database program should be written in a way that guarantees that, if the database is in a consistent state before executing the transaction, it will be in a consistent state after the complete execution of the transaction, assuming that no interference with other transactions occur. In other words, a transaction must transform the database from one consistent state to another consistent state.

34. Isolation Isolation property of a transaction means that the data used during the execution of a transaction cannot be used by a second transaction until the first one is completed. This property isolates transactions from one another. In other words, if a transaction T1 is being executed and is using the data item X, that data item cannot be accessed by any other transaction (T2………..Tn)until T1 ends. The isolation property is enforced by the concurrency control subsystem of the DBMS.

35. Durability: The durability property of transaction indicates the performance of the database's consistent state. It states that 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.

36. When a transaction is completed, the 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.

37. Transaction Log (or Journal) To support transaction processing, DBMSs maintain a transaction record of every change made to the database into a log (also called journal). Log is a record of all transactions and the corresponding changes to the database. The information stored in the log is used by the DBMS for a recovery requirement triggered by a ROLLBACK statement, which is program's abnormal termination, a system (power or network) failure, or disk crash.

38. Some relational database management systems (RDBMSs) use the transaction log to recover a database forward to a currently consistent state. 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.

39. The beginning and the ending (COMMIT) of the transaction are also recorded in the transaction log. The uses of a transaction log increases the processing overhead of a DBMS and the overall cost of the system.

40. For each transaction, the following data is recorded on the log: A start-of-transaction marker. The transaction identifier which could include who and where information. The record identifiers which include the identifiers for the record occurrences. The operation(s) performed on the records (for example, insert, delete, modify). The previous value(s) of the modified data. This information is required for undoing the changes made by a partially completed transaction. It is called the undo log. Where the modification made by the transaction is the insertion of a new record, the previous values can be assumed to be null.

41. The updated value(s) of the modified record(s). This information is required for making sure that the changes made by a committed transaction are in fact reflected in the database and can be used to redo these modifications. This information is called the redo part of the log. In case the modification made by the transaction is the deletion of a record, the updated values can be assumed to be null. A commit transaction marker if the transaction is committed, otherwise an abort or rollback transaction marker.

43. The log is written before any updates are made to the database. This is called write-ahead log strategy. I n this strategy, a transaction is not allowed to modify the physical database until the undo portion of the log is written to stable database. Table example of a transaction log. SQL sequences are reflected for database tables EMPLOYEE.

44. In case of a system failure, the DBMS examines the transaction log for all uncommitted or incomplete transactions and restores (ROLLBACK) the database to its previous state based on the information in the transaction log. When the recovery process is completed, the DBMS writes in the transaction log all committed transactions that were not physically written to the physical database before the failure occurred.

45. The TRNASACTION-ID is automatically assigned by the DBMS. If a ROLLBACK is issued before the termination of a transaction, the DBMS restores the database only for that particular transaction, rather than for all transactions, in order to maintain the durability of the previous transactions. In other words, committed transactions are not rolled back.

46. CONCURRENCY CONTROL Concurrency control is the process of managing simultaneous execution of transactions (such as queries, updates, inserts, deletes and so on) in a multiprocessing database system without having them interfere with one another. This property of DBMS allows many transactions to access the same database at the same time without interfering with each other.

47. The primary goal of concurrency is to ensure the atomicity of the execution of transactions in a multi-user database environment. Concurrency controls mechanisms attempt to interleave (parallel) READ and WRITE operations of multiple transactions so that the interleaved execution yields results that are identical to the results of a serial schedule execution.

48. Problems of Concurrency Control When concurrent transactions are executed in an uncontrolled manner, several problems can occur. The concurrency control has the following three main problems: Lost updates. Dirty read (or uncommitted data). Unrepeatable read (or inconsistent retrievals)

49. Lost Update Problem A lost update problem occurs when two transactions that access the same database items have their operations in a way that makes the value of some database item incorrect. In other words, if transactions T1 and T2 both read a record and then update it, the effects of the first update will be overwritten by the second update.

50. Let us consider an example where two accountants in a Finance Department of M/s KLY Associates are updating the salary record of a marketing manager 'Rahul'. The first accountant is giving an annual salary adjustment to 'Rahul' and the second accountant is reimbursing the travel expenses of his marketing tours to customer organisation.