Skip to content

Transaction State

Statement

Name the five states of transactions and use an example to discuss.

Solution

  • A transaction must be in one of the following states (Silberschatz et all, 2001):
    • Active: the transaction is being executed.
    • Partially committed: the transaction statements have been sent and the results of one or more of the statements are not reported yet.
    • Failed: one or more of the statements included in the transaction have failed.
    • Aborted: in response to a Failed state, the executed statements are rolled back and the database has been restored to its original state prior to the transaction.
    • Committed: all the statements in the transaction have been executed successfully and changes are successfully stored; the database has a new state.
  • The diagram below (Silberschatz et all, 2001, p.570) outlines those states:

transaction-states

  • An example would be transferring 10$ money from account A to account B; the transaction would include two statements:
    • deduct 10$ from account A.
    • accredit 10$ to account B.
  • When the transaction is sent to the database it would have two statements, and once it landed on the DBMS its state is changed to Active.
  • During processing, the DBMS needs to check authorization. Statement 1 or statement 2 can get executed first.
  • Probably statement 2 (crediting statement) only fails if the account does not exist, but Statement 1 is more complicated and can fail if account A does not have 10$.
  • If the DBMS supports concurrency both statements start executing at the same time, and they report their results to the caller.
  • While any of the two statements is still executing and has not been reported back yet; the transaction is in a Partially committed state.
  • Suppose that statement 2 has failed; it will report back to its caller and the rollback process starts to reverse any changes that have been committed. and the transaction enters a Failed state.
  • The DBMS generates the reverse statements usually before the transaction starts.
  • After the rollback finishes, the entire transaction is marked as Aborted and exits.
  • If no failure has been reported, and both statements get executed successfully; the transaction is marked as Committed and exits.

References

  • Silberschatz, A., Korth, H.F., & Sudarshan, S. (2001). Database System Concepts (4th ed.). New York, NY: McGraw-Hill. Available at Database System Concepts 4th Edition By Silberschatz-Korth-Sudarshan.pdf