DataBase Managment System , Lecture notes of Database Programming

Download DataBase Managment System and more Lecture notes Database Programming in PDF only on Docsity!

TRANSACTION

MANAGEMENT

1

Among the many functions of Database Management System there are three core ones which ensure that the database is reliable and remains in a consistent state. They include:

1. Transaction support
2. Concurrency control services
3. Recovery services. This reliability and consistency must be maintained in the presence of failures of both hardware and software components, and when multiple users are accessing the database. 2

To provide the flexibility of user-defined transactions, DBMSs cannot restrict transactions to only a specified number of reads and writes to a database. An information system may have many different kinds of transactions. Below is typical transactions in an Order Entry System: 4

Transaction

• (^) Add order
• (^) Update order
• (^) Check status
• (^) Payment
• (^) Shipment Description
  • (^) Customer places a new order
  • (^) Customer changes details of an existing order
  • (^) Customer checks the status of an order
  • (^) Payment received from a customer
  • (^) Goods sent to a customer 5

Display final message and issue cash Else Write error message End If On Error: ROLLBACK COMMIT The START TRANSACTION and COMMIT statements define the statements in a transaction. Any other SQL statements between them are part of the transaction. Typically, a transaction involves a number of SELECT, INSERT, UPDATE, and DELETE statements. 7

Besides the START TRANSACTION and COMMIT statements, the ROLLBACK statement may be used. ROLLBACK is like an undo command in a word processor in that the effects of user actions are removed. Unlike an undo command, ROLLBACK applies to a sequence of actions not just a single action. Thus, a ROLLBACK statement causes all effects of a transaction to be removed. The database is restored to the state before the transaction was executed. 8

Exception handling allows unanticipated errors such as communication errors to be processed separately from the normal logic of the transaction. States of a transaction A transaction in a database can be in one of the following states: 10

Active − In this state, the transaction is being executed. This is the initial state of every transaction. Partially Committed − When a transaction executes its final operation, it is said to be in a partially committed state. Failed − A transaction is said to be in a failed state if any of the checks made by the database recovery system fails. A failed transaction can no longer proceed further. Aborted − If any of the checks fails and the transaction has reached a failed state, then the 11

Transaction Properties DBMSs ensure that transactions obey certain properties. The most important and widely known properties are the ACID properties (atomic, consistent, isolated, and durable). Atomic: means that a transaction cannot be subdivided. Either all the work in the transaction is completed or nothing is done. For example, the ATM transaction will not debit an account without also crediting a corresponding account. 13

The atomic property implies that partial changes made by a transaction must be undone if the transaction aborts. Consistent: means that if applicable constraints are true before the transaction starts, the constraints will be true after the transaction terminates. For example, if a user's account is balanced before a transaction, then the account is balanced after the transaction. Otherwise, the transaction is rejected and no changes take effect. 14

Durable: means that any changes resulting from a transaction are permanent. No failure will erase any changes after a transaction terminates. For example, if a bank's computer experiences a failure five minutes after a transaction completes, the results of the transaction are still recorded on the bank's database. To ensure that transactions meet the ACID properties, DBMSs provide certain services that are transparent to database developers (programmers and analysts). 16

In common usage, transparency means that you can see through an object, rendering its inner details invisible. For DBMSs, transparency means that the inner details of transaction services are invisible. Transparency is very important because services that ensure ACID transactions are difficult to implement. By providing these services, DBMSs improve the productivity of database programmers and analysts. 17

For example, if a communication failure occurs during an ATM transaction, the effects of the transaction are automatically removed from the database. On the other hand, if the DBMS crashes three seconds after an ATM transaction completes, the details of the transaction remain permanent  Concurrency transparency: means that users perceive the database as a single-user system even though there may be many simultaneous users. 19

For example, even though many users may try to reserve a popular flight using a reservation transaction, the DBMS ensures that users do not overwrite each other's work. Recovery and concurrency control involve overhead that may require additional resources and careful monitoring to reach an acceptable level of performance. The DBA must be aware of the resource implications of these services. More computing resources such as memory, disk space, and parallel processing may be useful to improve performance. 20