• A transaction is the sequence of one or more SQL statements that are combined together to form a single unit of work.

Transaction State Diagram:

Transaction must be in one of these states:

1.Active:

• It is the initial state if a transaction.

• Execution of transaction starts in an active state.

• Transaction remains in an active state till its execution is in process.

2. Partially Committed:

• When the last operation of a transaction is executed it goes to a partially committed state.

• Here there is a possibility that the transaction may be aborted or else it goes to committed state.

3.Failed:

• A transaction goes to a Failed state if it is determined that it can no longer proceed with its normal exection.

4.Aborted:

• Failed transaction when rolled back is in an aborted state.

• In this stage system has two options:

1) Restart the transaction: A restarted transaction is considered to be new transaction which may recover from possible failure.

2) Kill the transaction: A transaction can be killed to recover from failure.

5.Committed:

• The transaction when successfully completed comes to this state.

• Transaction is said to be terminated if its neither committed nor aborted.

Properties of Transaction:

A database guarantees the following four properties to ensure database reliability, as follows:

• Atomicity:

A database follows the all or nothing rule, i.e., the database considers all transaction operations as one whole unit or atom. Thus, when a database processes a transaction, it is either fully completed or not executed at all. Suppose A is transferring Rs 100 to B's account. Computers are electronic device and are prone to failure. Assume A has initially Rs 300 and B has Rs 500. Now it may happen that when A has initiated the transfer, in the midst of transferring from A to B, system fail. Now balance is deducted from A's account but has not been added to B's account. Hence we need either the transaction executes fully or just revert back to initial state.

• Consistency:

Ensures that only valid data following all rules and constraints is written in the database. When a transaction results in invalid data, the database reverts to its previous state, which abides by all customary rules and constraints. This must be totally ensured by the programmer. Referring to above example, this basically means sum of balances of both A's and B's account are same before and after transaction.

• Isolation:

Ensures that transactions are securely and independently processed at the same time without interference, but it does not ensure the order of transactions. For example, user A withdraws Rs 100 and user B withdraws Rs 250 from user Z’s account, which has a balance of Rs 1000. Since both A and B draw from Z’s account, one of the users is required to wait until the other user transaction is completed, avoiding inconsistent data. If B is required to wait, then B must wait until A’s transaction is completed, and Z’s account balance changes to Rs 900. Now, B can withdraw Rs 250 from this Rs 900 balance.

• Durability:

It may happen that even the transaction is successful, system fails. In the above example, user B may withdraw $100 only after user A’s transaction is completed and is updated in the database. If the system fails before A’s transaction is logged in the database, A cannot withdraw any money, and Z’s account returns to its previous consistent state.