Database Transaction
Suppose we have to calculate total marks of a student. Then what are the steps involved in it? Get the marks in each subject (say we have 3 subjects), calculate the total and then display it. Here all the 3 steps are called as transaction and it consists of 3 small actions which cannot be further divided.
A transaction can be defined as a group of tasks. A single task is the minimum processing unit which cannot be divided further.
From the above example, we understand that a transaction is a set of atomic actions. In a database, each transaction should maintain ACID property to meet the consistency and integrity of the database.
ACID Property
- Atomicity – This property states that each transaction must be considered as a single unit and must be completed fully or not completed at all. No transaction in the database is left half completed. Database should be in a state either before the transaction execution or after the transaction execution. It should not be in a state ‘executing’.In our example above, the transaction should not be left at any one of the step above. All the 5 steps have to be either completed or none of the step has to be completed. If a transaction is failed to execute any step, then it has to rollback all the previous steps and come to the state before the transaction or it should try to complete the failed step and further steps to complete whole transaction.
- Consistency – Any transaction should not inject any incorrect or unwanted data into the database. it should maintain the consistency of the database.In above example, while calculating the total, it should not perform any other action like inserting or updating or delete. It should also not pick marks for other students. It should be picking the marks for the same student and calculating the total. Hence it maintains the consistency of the database.
- Durability – The database should be strong enough to handle any system failure. It should not be working for single transaction alone. It should be able to handle multiple transactions too. If there is any set of insert /update, then it should be able to handle and commit to the database. If there is any failure, the database should be able to recover it to the consistent state.Suppose if the system crashes after the transaction updates Tom’s address, but before committing the transaction to the database, then the database should be strong enough to recover Tom’s original address. It should not be updated to newer address
- Isolation – If there are multiple transactions executing simultaneously, then all the transaction should be processed as if they are single transaction. But individual transaction in it should not alter or affect the other transaction. That means each transaction should be executed as if they are independent.Suppose there are two transactions, updating Tom’s address and James’ phone number which are executed in parallel. Each transaction should be executed its own to complete their transaction and it should not affect the execution of other transaction.
States of Transaction
Each transaction has following 5 states:
- Active – This is the first state of transaction and here the transaction is being executed. For example, updating or inserting or deleting a record is done here. But it is still not saved to the database. When we say transaction it will have set of small steps, and those steps will be executed here.
- Partially Committed – This is also an execution phase where last step in the transaction is executed. But data is still not saved to the database. In our example of calculating total marks, final display the total marks step is executed in this state.
- Committed – In this state, all the transactions are permanently saved to the database. This step is the last step of a transaction, if it executes without fail.
- Failed – If a transaction cannot proceed to the execution state because of the failure of the system or database, then the transaction is said to be in failed state. In the total mark calculation example, if the database is not able fire a query to fetch the marks, i.e.; very first step of transaction, then the transaction will fail to execute.
- Aborted – If a transaction is failed to execute, then the database recovery system will make sure that the database is in its previous consistent state. If not, it brings the database to consistent state by aborting or rolling back the transaction. If the transaction fails in the middle of the transaction, all the executed transactions are rolled back to it consistent state before executing the transaction. Once the transaction is aborted it is either restarted to execute again or fully killed by the DBMS.
