5.1 Transaction
Recovery and Concurrency
Recovery and Concurrency in a DBMS are part of the general topic of transaction management. Hence we shall begin the discussion by examining the fundamental notion of a transaction.
1 Transaction
A transaction is a logical unit of work.
Consider the following example:
The procedure for transferring an amount of Rs. 100/- from the account of one customer to another is given.
Hence to guarantee database consistency it has to be ensured that either both updates are performed or none are performed. If, after one update and before the next update, something goes wrong due to problems like a system crash, an overflow error, or a violation of an integrity constraint etc., then the first update needs to be undone.
This is true with all transactions. Any transaction takes the database from one consistent state to another. It need not necessarily preserve consistency of database at all intermediate points. Hence it is important to ensure that either a transaction executes in its entirety or is totally cancelled. The set of programs which handles this forms the transaction manager in the DBMS. The transaction manager uses COMMIT and ROLLBACK operations for ensuring atomicity of transactions.
COMMIT – The COMMIT operation indicates successful completion of a transaction which means that the database is in a consistent state and all updates made by the transaction can now be made permanent. If a transaction successfully commits, then the system will guarantee that its updates will be permanently installed in the database even if the system crashes immediately after the COMMIT.
ROLLBACK – The ROLLBACK operation indicates that the transaction has been unsuccessful which means that all updates done by the transaction till then need to be undone to bring the database back to a consistent state. To help undoing the updates once done, a system log or journal is maintained by the transaction manager. The before- and after-images of the updated tuples are recorded in the log.
The properties of transaction can be summarised as ACID properties - ACID standing for atomicity, consistency, isolation and durability.
Atomicity: A transaction is atomic. Either all operations in the transaction have to be performed or none should be performed.
Consistency: Transactions preserve database consistency. i.e., A transaction transforms a consistent state of the database into another without necessarily preserving consistency at all intermediate points.
Isolation: Transactions are isolated from one another. i.e., A transaction's updates are concealed from all others until it commits (or rolls back).
Durability: Once a transaction commits, its updates survive in the database even if there is a subsequent system crash.
5. Recovery and Concurrency
Recovery and Concurrency in a DBMS are part of the general topic of transaction management. Hence we shall begin the discussion by examining the fundamental notion of a transaction.
1 Transaction
A transaction is a logical unit of work.
Consider the following example:
The procedure for transferring an amount of Rs. 100/- from the account of one customer to another is given.
EXEC SQL WHENEVER SQLERROR GOTO
UNDO
EXEC SQL UPDATE DEPOSIT
SET BALANCE=BALANCE-100
WHERE CUSTID=from_cust;
EXEC SQL UPDATE DEPOSIT
SET BALANCE=BALANCE+100
WHERE CUSTID=to_cust:
EXEC SQL COMMIT;
GOTO FINISH
UNDO:
EXEC SQL ROLLBACK;
FINISH:
RETURN;
Here, it has to be noted that the single operation “amount transfer” involves two database updates – updating the record of from_cust and updating the record of to_cust. In between these two updates the database is in an inconsistent (or incorrect in this example) state. i.e., if only one of the updates is performed, one cannot say by seeing the database contents whether the amount transfer operation has been done or not.
Hence to guarantee database consistency it has to be ensured that either both updates are performed or none are performed. If, after one update and before the next update, something goes wrong due to problems like a system crash, an overflow error, or a violation of an integrity constraint etc., then the first update needs to be undone.
This is true with all transactions. Any transaction takes the database from one consistent state to another. It need not necessarily preserve consistency of database at all intermediate points. Hence it is important to ensure that either a transaction executes in its entirety or is totally cancelled. The set of programs which handles this forms the transaction manager in the DBMS. The transaction manager uses COMMIT and ROLLBACK operations for ensuring atomicity of transactions.
COMMIT – The COMMIT operation indicates successful completion of a transaction which means that the database is in a consistent state and all updates made by the transaction can now be made permanent. If a transaction successfully commits, then the system will guarantee that its updates will be permanently installed in the database even if the system crashes immediately after the COMMIT.
ROLLBACK – The ROLLBACK operation indicates that the transaction has been unsuccessful which means that all updates done by the transaction till then need to be undone to bring the database back to a consistent state. To help undoing the updates once done, a system log or journal is maintained by the transaction manager. The before- and after-images of the updated tuples are recorded in the log.
The properties of transaction can be summarised as ACID properties - ACID standing for atomicity, consistency, isolation and durability.
Atomicity: A transaction is atomic. Either all operations in the transaction have to be performed or none should be performed.
Consistency: Transactions preserve database consistency. i.e., A transaction transforms a consistent state of the database into another without necessarily preserving consistency at all intermediate points.
Isolation: Transactions are isolated from one another. i.e., A transaction's updates are concealed from all others until it commits (or rolls back).
Durability: Once a transaction commits, its updates survive in the database even if there is a subsequent system crash.
5. Recovery and Concurrency
Leave a Comment