Database Transaction Processing is a system which maintains the integrity of the data before and after the database transactions. The main responsibilities of a database transaction processing system are to maintain the Atomicity, Consistency, Isolation and Durability of database. These four properties are combinedly known as the ACID property.
Suppose the X’s account gets updated from Rs.500 to Rs.300 but due to some issues like the transaction failed, the operating system crashed, or the computer stopped operating, Y’s account could not get updated from Rs.800 to Rs.1000 then it will show the system failures. The atomicity takes care of either both accounts get updated or none gets.
● Atomicity: This property ensures that either all the operations involved in transaction are saved in database completely or all the operations are rejected and the original records are restored. It is also referred as the All-or-None property. Ensuring atomicity is the responsibility of a component of the database called the recovery system.
Suppose the previous transaction between X and Y starts at time t1 and completes at time t2. During this period, another transaction completes at time t3 (t1<t3<t2) such that Y sends 1% of his money to Z and Z already has Rs.100 in his account. Now the system can execute these operations in various orders, two of them are:
1.) a.) update X’s account from Rs.500 to Rs.300
b.) update Y’s account from Rs.800 to Rs.1000
c.) update Y’s account from Rs.1000 to Rs.990
d.) update Z’s account from Rs.100 to Rs.110
2.) a.) update X’s account from Rs.500 to Rs.300
b.) update Y’s account from Rs.800 to Rs.792
c.) update Y’s account from Rs.792 to Rs.992
d.) update Z’s account from Rs.100 to Rs.108
Both of these cases will end up with distinct values of Y’s account’s money. That’s why we need to manage the concurrent transactions which refers to the isolation property.
● Consistency: A transaction must not violate any integrity constraints during its execution, and if the transaction leaves the database in an illegal state it is aborted and an error is thrown. This characteristic of a database is called the consistency. Ensuring consistency for an individual transaction is the responsibility of the application programmer who codes the transaction.
● Isolation: The database system must take special actions to ensure that transactions operate properly without interference from concurrently executing database statements. Ensuring the isolation property is the responsibility of a component of the database system called the concurrency-control system.
Suppose the transaction between X and Y completes at t2 and the system crashed at the same instance, which results in the loss of memory of system about this transaction, means database will restore its previous state. Thus, a transaction’s actions must persist across crashes and the durability property ensures it.
● Durability: After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures. The recovery system of the database is responsible for ensuring durability, in addition to ensuring atomicity.
During transaction X can send any amount of money to Y in form of integer or float only it cannot be in form of string, boolean, etc. Except that X+Y must be same before and after the transaction. This property is known as the consistency of database.
These are the basic points of a database transaction processing system.
The concurrency control system and the recovery system can be covered in next articles.