Transaction Management

From the perspective of the database user, a collection of multiple database activities frequently appears to be a single unit. For instance, transferring money from a checking account to a savings account is seen by the consumer as a single operation; nevertheless, the database system sees it as multiple activities. It is obvious that all of these activities must take place, or none at all, in the event of a failure. If the savings account was not credited but the checking account was debited, it would not be acceptable.
Transactions are groups of activities that collectively constitute a single logical unit of work. Either the full transaction executes, or none of it does, hence a database system must guarantee effective transaction execution despite errors. In our funds transfer example, a transaction computing the customer's total money might see the checking account balance before it is debited by the funds transfer transaction, but see the savings balance after it is credited. As such, it must manage concurrent execution of transactions in a way that avoids the introduction of inconsistency. This would lead to an inaccurate outcome.

Transaction Concept

A transaction is a segment of program execution that accesses and may update different pieces of data. A transaction is typically started by a user program written in a high-level data manipulation language. This user program uses statements of the type begin transaction and end transaction to define its boundaries. We need that the database system uphold the following characteristics of the transactions in order to guarantee the data's integrity:

• Atomicity:
  It is the property that either all operations in a transaction are correctly reflected in the database, or none are.
• Consistency:
  Database consistency is preserved when a transaction is executed in isolation, meaning that no other transactions are running simultaneously.
• Isolation:
  The System ensures that, for each pair of transactions Ta and Tb, it appears to Ta that either Tb concluded execution before Ta started, or Tb started execution after Ta finished, despite the fact that several transactions may execute concurrently. As a result, no transaction is aware of the other transactions that are running simultaneously in the system.
• Durability:
  Even in the event of a system failure, changes made by a transaction that has successfully completed remain in the database.

These characteristics are known as ACID properties, an acronym created from the initial letters of the four characteristics mentioned above. Despite the fact that the database is permanently stored on disk, some of it is also temporarily present in main memory. 

Transactions use two operations to access data:

• Read(x):
  In this operation, data item x is moved from the database to a local buffer that belongs to the transaction that carried out the read operation using the read() method.
• Write(x):
  This operation transfers the data item x back to the database from the local buffer of the transaction that carried out the write.

A write operation does not always result in an immediate update of the data on the disk in a real database system. The write operation could be conducted on the disk later after being temporarily saved in memory. Assume that writing is currently finished right away.
Let Tx represent a transfer of Rs. 4000 from account A to account B. This money transfer request is performed as below:

Tx:
read(A);
A:=A-4000;
Write(A);
Read(B);
B:=B+4000;
Write(B);

Let's think about each ACID criteria that applies to this transaction.

1. Atomicity:
   Suppose that the balances of accounts A and B are 5000 and 2000 just before to the execution of transaction Tx. Let's say a problem happened during the execution of Tx after the write (A) operation but before the write (B) operation. The values of the amount that will thereafter be shown in the database are 1000 and 2000. Failure of the system led to the destruction of 4000. A + B's total is no longer held constant. An inconsistent state is one like this. We must make sure that these discrepancies are hidden in a database system. Even if transaction Tx is fully processed, the system must eventually be in an inconsistent state. There is a place where A is calculated at 1000 and B is not calculated as 6000. But gradually the consistent state takes the place of this inconsistent state. Therefore, if the transaction never began or was guaranteed to finish, such an inconsistent state would not be apparent other than when the transaction was being carried out. This is the basis for the atomicity requirement: if it exists, a transaction's actions must either all be reflected in the database or none at all. When a transaction makes a write to a disk, the database system keeps track of the previous values on the disk and, if the transaction does not succeed, restores the previous values to make it look as though the transaction never took place. The database system itself is in charge of ensuring atomicity; the transaction-management component takes care of it.
2. Consistency:
   The total of A and B must remain unchanged once the transaction is completed in order to satisfy the consistency criterion. Without the necessity for consistency, money might be generated or obtained through a transaction. It is simple to confirm that if the database is consistent before a transaction is executed, it will stay so after the operation has been completed. The application programmer who programs a transaction is responsible for ensuring consistency for that transaction.
3. Isolation:
   Even though the atomicity and consistency features are guaranteed for each transaction, if many transactions are carried out at once, their activities may unintentionally interleave, leading to an inconsistent state. As we previously observed, when the operation to transfer funds is being processed, the database is momentarily inconsistent. At this midpoint, a second simultaneously executing transaction that reads A and B and computes A + B will get an inconsistent result. Even after both transactions have finished, the database may still be inconsistent if the second transaction changes A and B using the inconsistent values it read.
   Executing transactions serially—that is, one after another—can help you prevent this. However, concurrent execution improves performance. As a result, many solutions have been created that enable numerous transactions to run simultaneously.
   The isolation attribute of a transaction makes sure that the system state produced by the concurrent execution of transactions is similar to the state that would have been produced had these transactions been carried out sequentially in some order.
4. Durability:
   Once the transaction has been successfully executed and the user who initiated the transaction has been notified that the transfer of funds has occurred, no system failure must result in a loss of data relating to this transfer of cash. The durability attribute ensures that all database modifications made once a transaction succeeds will remain in place even if there is a system failure following the transaction's successful completion. For the time being, we will assume that while data stored in main memory may be lost in the event of a computer system failure, data stored on disk will never be lost. Durability can be ensured by one of the following ways:
   • Before the transaction is finished, the modifications it has made have been written to disk.
   • When the database system is restarted after a failure, the updates can be reconstructed from the information that was written to disk about the changes made by the transaction.

The database system's "recovery management component" is in charge of making sure about the durability.