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



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.