A transaction is a sequence of one or more SQL statements that together forms a logical unit of work. Each statement in the transaction performs a part of the task, but all of them are required to complete the task. All the statements forming a transaction must be executed for the database to be in a consistent state. A transaction occurs when the database is modified.
Here is an example of a typical transaction. A customer orders a product. The order processing program will: 1) Query the table containing product details to see if the product is in stock. 2) Insert the order details into a table that holds the order details. 3) Update the table containing product details to reduce the quantity on hand by the quantity order.
These three actions, in the sequence shown above, form a single logical transaction. The concept of transaction processing is critical for programs that update a database because it ensures the integrity of the database.
As a rule, the statements in a transaction are executed as a single unit of work in the database. Either all the statement will be executed successfully, or none of the statements will be executed.
The DBMS is responsible for ensuring the consistent state of the database even in the case of the application program aborting in the middle of the transaction, or a hardware failure in the middle of a transaction.
Consider the above example of a transaction. What do you suppose will happen if the order processing program aborted after step(2)? The database would reflect a partial transaction. It would be in an inconsistent state. To maintain consistency, the DBMS undoes all the changes made if a transaction is not completed. Thus, a DBMS guarantees that if a transaction executes some updates, and then for whatever reasons, a failure occurs before the transaction reaches its normal termination, then those updates will be undone.
To ensure integrity of the data, we require that the database system maintains the following properties of the transactions:
(1)Atomicity: Either operations of the transactions are reflected properly in the database or none are. The basic idea behind ensuring atomicity is as follows. The database system keeps track of the old values of any data on which a transaction performs a write, and, if the transaction does not complete its execution the old values are restored to make it appear as though the transaction never executed. It is handled by a component called the Transaction Management component.
(2)Consistency: If the database is consistent before an execution of the transaction, the database remains consistent after the execution of the transaction. Execution of a transaction in isolation preserves the consistency of the database. Ensuring consistency for an individual transaction is the responsibility of the application programmer who codes the transaction.
(3)Durability: The durability property guarantees that, once a transaction completes successfully, all the updates that it carried out on the database persist, even if there is a system failure after the transaction completes execution.
We can guarantee durability by ensuring that either
a) The updates carried out by the transaction have been written to disk before the transaction completes
b) Information about the updates carried out by the transaction and written to disk is sufficient to enable the database to reconstruct the
updates when the database system is restarted after the failure.
Ensuring durability is the responsibility of a component of the database system called the Recovery-Management component.
(4)Isolation: Even if the consistency and atomicity properties are ensured for each transaction, if several transactions are executed concurrently, their operations may interleave in some undesirable way, resulting in an inconsistent state. The isolation property of a transaction ensures that the concurrent execution of transaction results in a system state that is equivalent to a state that could have been obtained had these transactions executed one at a time in some order.
