Suppose X has Rs.500 and Y has Rs.800 in their respective bank accounts and X sends some money let’s say Rs.200 to Y, this is referred to as transaction between X and Y.
● Transaction is a set of interrelated operations that combinedly result in some unit of work.
The operations involved in this transaction are:
1.) Read the value (Rs.500) from X’s account in a variable- Read(X).
2.) Write the updated value (Rs.300) of variable in database- Write(X).
3.) Read the value (Rs.800) from Y’s account in a variable- Read(Y).
4.) Write the updated value (Rs.1000) of variable in database- Write(Y).
●A set of events created by some user program that manipulate (insert, update etc.) the data stored in database is referred to as the database transaction.
This transaction, let’s say T can be defined stepwise as:
X: = X − 200;
Y: = Y + 200;
● A database transaction cycle consists of 5 states. These states can be described using a transaction model:
1.) During the execution, transaction stays in active state. It is the initial state of transaction.
2.) If the last operation of transaction gets executed without any error, then it moves to the partially committed state.
3.) In case any error occurs during any operation, then the transaction is sent to the failed state.
4.) After entering the failed state, the database need to discard all the changes occurred during transaction (rollback) and then abort the process by moving to abort state. The transaction can abort in 2 ways:
i.) If the transaction was aborted as a result of some hardware or software error that was not created through the internal logic of the transaction, the system will restart the transaction as a new process.
ii.) If the transaction was aborted because of some internal logical error that can be corrected only by rewriting the application program, or because the input was bad, or because the desired data was not found in the database, the system will kill the process.
5.) If the transaction has reached the partially committed state, and no error occurs during this state, then the transaction moves to the final or committed state as a successful completion.
● 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.
● 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.
● 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.
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.