Monday, 4 April 2016

Database Transaction States in DBMS

Database transaction states in DBMS, different states a transaction can go through in DBMS, Active, Partially committed, Committed, Failed, and Aborted states, Various transaction states, explain various transaction states in dbms with example


Transaction States


A transaction is a single logical unit of work that must be completed entirely (all the instructions from begin_transaction to commit) or must be aborted if not able to complete all the instructions successfully. During its execution a transaction goes through many different states. The following state transition diagram depicts the various states a single transaction has to go through.



 
State Transition Diagram of states of transaction execution
 
During its execution a transaction must be in any one of the following states;

  • Active stateInitial state.

Any transaction will be in this state during its execution. Once the transaction starts executing from the first instruction begin_transaction, the transaction will be considered in active state. During this state it performs operations READ and WRITE on some data items.
From active state, a transaction can go into one of two states, a partially committed state or a failed state.

  • Partially committed stateAfter the execution of final statement.

This is the state of a transaction that successfully executing its last instruction. That means, if an active transaction reaches and executes the COMMIT statement, then the transaction is said to be in partially committed state.
From partially committed state, a transaction can go into one of two states, a committed state or a failed state.

  • CommittedAfter successful completion of transaction.

At partially committed state the database recovery system will perform certain actions to ensure that a failure at this stage should not cause lose of any updates made by the executing transaction. If the current transaction passed this check, then the transaction reaches committed state.
From committed state, a transaction can go into terminated state.

  • FailedIf any failure occurs.

While a transaction is in the active state or in the partially committed state, the issues like transaction failure, user aborting the transaction, concurrency control issues, or any other failure, would happen. If any of these issues are raised, then the execution of the transaction can no longer proceed. At this stage a transaction will go into a failed state.
From failed state, a transaction can go into only aborted state.

  • AbortedAfter rolled back to the old consistent state.

After the failed state, all the changes made by the transaction has to be rolled back and the database has to be restored to its state prior to the start of the transaction. If these actions are completed by the DBMS then the transaction considered to be in aborted state.
From aborted state, a transaction can go into terminated state.

Example:

Let us assume a transaction T1 that transfers money from account A to account B. Before transaction let A = 1000 and B = 1000. The transaction can be represented as follows;

Transaction T1
BEGIN TRANSACTION
READ(A);
A := A - 500;
WRITE(A);
READ(B);
B := B + 500;
WRITE(B);
          COMMIT;
END TRANSACTION;


  • ACTIVE - When the transaction T1 starts, i.e., enters BEGIN TRANSACTION, the transaction in ACTIVE state. From BEGIN TRANSACTION and COMMIT, the transaction is in ACTIVE state only.
      • In ACTIVE state, A = 1000 and B = 1000.

  • PARTIALLY COMMITTED - If T1 reaches the instruction COMMIT, the transaction goes into PARTIALLY COMMITTED state.
      • In PARTIALLY COMMITTED state, A = 500 and B = 1500;

  • FAILED – It happens if one of the following occurs;
    •  If any failures happen to the transaction in ACTIVE state (refer type of failures) then the transaction goes into FAILED state.
      • If the transaction failed before WRITE(A), then A = 1000 and B = 1000. 
      • If the transaction failed after WRITE(A), then A = 500 and B = 1000. 
      • If the transaction failed before COMMIT and after WRITE(B), then A = 500 and B = 1500.
    • If any failures happen to the transaction in PARTIALLY COMMITTED state (refer type of failures) then the transaction goes into FAILED state.
      • If the transaction goes into FAILED state from PARTIALLY COMMITTED state, then A = 500 and B = 1500.
  • ABORTED - The transaction in ABORTED state has to undo the changes made so far. That is the old consistent values of data items A and B are restored.
      • In ABORTED state, A = 1000 and B = 1000. [A and B are rolled back to Old consistent state]
  • COMMITTED - If the transaction executes COMMIT successfully, that is, if it successfully writes the new value of A and B into log file or stable storage, then the transaction is said to be in COMMITTED state.
      • In COMMITTED state, A = 500 and B = 1500. [New consistent state]

********************

 Go back to Transaction Management in DBMS home page
 












4 comments:

  1. Nice artie really very usefull 👍

    ReplyDelete
  2. With the need to constantly adjust to the highly dynamic needs as well as consistently address security issues, systematic upgrade of database has been identified as a necessity lately. https://www.dbdesigner.net

    ReplyDelete

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...