Suppose a user at ATM machine withdraws money from his account. Suppose he withdrew $500. What are the transactions involved in this operation ?
- Check the Account for balance in his account
- If sufficient amount exists, give the money to user.
- Calculate balance = balance – 500
- Update account to new balance
These are the four transactions in this case. Withdrawal operation is complete when all these transactions are complete, or none of them are complete at all. Suppose system could not proceed after transaction (i). What happens now? Though the (i) transaction is complete successfully, other transaction will not perform. Hence the withdrawal operation is not complete. But this failure will hardly affect the database as it did not modify DB. First step simply read the data from DB. But what will happen if the system fails after step (ii) or (iii)? The user gets money but his balance remains un-deducted! This is not expected in a DB. If user gets money his account also has to be updated to correct balance amount. Hence even though the transactions are independent small piece of task, they all have to be executed in a sequence as though they are interlinked and should be completed.
In order to ensure that a transaction is completed fully, it defines four properties of it. In a database, each transaction should maintain ACID property to meet the consistency and integrity of the database.
Table of Contents
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 of calculating total marks 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.
This can be clearly visualized in our ATM example. If the transaction fails after (ii) or (iii), then the user will get money, but his account will not be updated. This should not happen. Whenever a transaction is executed, all the steps in it should be completed or not completed at all. Although ATM withdrawal has money single steps in it, all should be executed as if they are part of single transaction and completed. The state of DB should be either at before ATM withdrawal (of course, user without withdrawn money) or after ATM withdrawal (user with money and account updated). This will make the system in consistent state, and hence there will not be any incorrect data in DB.
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 of total marks, 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.
In the ATM withdrawal example, it should make sure withdrawn amount is 500 and deducted amount from the account is also 500. It should not give 500 to the user and deduct 100 from his account. It should always make sure system is well balanced before and after the transaction. There should not be any mistakes in DB because of the transaction.
One of the methods to achieve consistency of DB is to define primary and foreign keys. These keys will not restrict unwanted data insert/ delete/update, by checking the integrity of data in DB. Suppose the transaction was to delete a department for which employees are still working. Then the system will not allow deleting the department, unless all its employees are deleted from the system. This is because foreign key is defined on employee table for its department.
Another way is by maintaining the log for each transaction. It will make sure if there is any failure in middle of any transaction, the data will be recovered by seeing the log. Say, the system fails after step (ii) in ATM withdrawal. The system would have kept the log of transaction (ii) before executing it and after executing it. Hence when system recovers, it sees the log for last executed transaction, and understands that system has not been updated for the amount withdrawn and updates it.
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.
Similarly, in money withdrawal from ATM, if manager checks his account balance while he is withdrawing the money, then manager should see the balance either before withdrawing the money or after withdrawing the money. Manger viewing his balance is considered as another transaction and user’s transaction should not give inconsistent data to manager’s transaction. Manager should not be able to see the amount after step (ii) above. Manager should see the balance only after, it is updated in the database. i.e.; each transaction should be executed in sequence rather than executing it in parallel. This will make sure that each individual transaction is completed and any other dependent transaction will get the consistent data out of it. Any failure to any transaction will not affect other transaction in this case. Hence it makes all the transaction consistent.
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
In ATM withdrawal example, if the system failure happens after step (ii) or (iii), the system should be strong enough to update DB with his new balance, after system recovers. It should keep log of each transaction and its failure. So when the system recovers, it should be able to know when a system has failed and if there is any pending transaction, then it should be updated to DB.