Database Transaction Control

Introduction to Database Transaction

  • Database Transaction is an atomic unit that contains one or more SQL statements.
  • It is a series of operations that performs as a single unit of work against a database.
  • It is a logical unit of work.
  • It has a beginning and an end to specify its boundary.

    • Let's take an simple example of bank transaction, Suppose a Bank clerk transfers Rs. 1000 from X's account to Y's account.

    X's Account

    open-account (X)
    prev-balance = X.balance
    curr-balance = prev-balance – 1000
    X.balance = curr-balance
    close-account (X)


    Decreasing Rs. 1000 from X's account, saving new balance that is current balance and after completion of transaction the last step is closing the account.

    Y's Account

    open-account (Y)
    prev - balance = Y.balance
    curr - balance = prev-balance + 1000
    Y.balance = curr-balance
    close-account (Y)


    Adding Rs. 1000 in the Y's account and saving new balance that is current balance and after completion of transaction the last step is closing the account.

  • The above example defines a very simple and small transaction that tells how the transaction management actual works.

Transaction Properties

Following are the Transaction Properties, referred to by an acronym ACID properties:

1. Atomicity
2. Consistency
3. Isolation
4. Durability

  • ACID properties are the most important concepts of database theory.
  • A transaction is a small unit of program which contains several low level tasks.
  • These properties guarantee that the database transactions are processed reliably.
1. Atomicity
  • Atomicity defines that all operations of the transactions are either executed or none.
  • Atomicity is also known as 'All or Nothing', it means that either perform the operations or not perform at all.
  • It is maintained in the presence of deadlocks, CPU failures, disk failures, database and application software failures.
  • It can be turned off at system level and session level.
2. Consistency
  • Consistency defines that after the transaction is finished, the database must remain in a consistent state.
  • It preserves consistency of the database.
  • If execution of transaction is successful, then the database remains in a consistent state. If the transaction fails, then the transaction will be rolled back and the database will be restored to a state consistent.
3. Isolation
  • Isolation defines that the transactions are securely and independently processed at the same time without interference.
  • Isolation property does not ensure the order of transactions.
  • The operations cannot access or see the data in an intermediate state during a transaction.
  • Isolation is needed when there are concurrent transactions occurring at the same time.
4. Durability
  • Durability states that after completion of transaction successfully, the changes are required for the database.
  • Durability holds its latest updates even if the system fails or restarts.
  • It has the ability to recover committed transaction updates even if the storage media fails.

Transaction States

  • A transaction is a small unit of program which contains several low level tasks.
  • It is an event which occurs on the database.
It has the following states,

1. Active
2. Partially Committed
3. Failed
4. Aborted
5. Committed

transaction states

1. Active : Active is the initial state of every transaction. The transaction stays in Active state during execution.
2. Partially Committed : Partially committed state defines that the transaction has executed the final statement.
3. Failed : Failed state defines that the execution of the transaction can no longer proceed further.
4. Aborted : Aborted state defines that the transaction has rolled back and the database is being restored to the consistent state.
5. Committed : If the transaction has completed its execution successfully, then it is said to be committed.
▲