Transaction Management in DBMS
A transaction is
a set of logically related operations. A transaction is a single logical unit
of work which accesses and possibly modifies the contents of a database.
Transactions access data using read and write operations.
For example, you are transferring money from your bank
account to a friend’s account; the set of operations would be like this:
Simple Transaction Example
1. Read your account balance
2. Deduct the amount from your balance
3. Write the remaining balance to your account
4. Read your friend’s account balance
5. Add the amount to his account balance
6. Write the new updated balance to his account
2. Deduct the amount from your balance
3. Write the remaining balance to your account
4. Read your friend’s account balance
5. Add the amount to his account balance
6. Write the new updated balance to his account
The above whole set of operations can be called a
transaction.
In
order to maintain consistency in a database, before and after the transaction,
certain properties called ACID properties are followed.
Atomicity
Atomicity means that either the entire transaction takes place at once or
doesn’t happen at all. Transactions do not occur partially. Each transaction is
considered as one unit and either runs to completion or is not executed at all.
It involves the following two operations.
Abort: If a transaction aborts, changes made to database are not visible.
Commit: If a transaction commits, changes made are visible.
Atomicity is also known as the ‘All or nothing rule’.
Abort: If a transaction aborts, changes made to database are not visible.
Commit: If a transaction commits, changes made are visible.
Atomicity is also known as the ‘All or nothing rule’.
Consider the following transaction T consisting
of T1 and T2: Transfer of 100 from account X to
account Y.
If the transaction fails after completion of T1 but
before completion of T2.( say, after write(X) but
before write(Y)), then amount has been deducted from X but
not added to Y. This results in an inconsistent database state.
Therefore, the transaction must be executed in entirety in order to ensure
correctness of database state.
Consistency
Consistency means that integrity constraints must be maintained so that the database is consistent before and after the transaction. It refers to the correctness of a database.
Consistency means that integrity constraints must be maintained so that the database is consistent before and after the transaction. It refers to the correctness of a database.
For
example,
The total amount before and after the transaction must be maintained.
Total before T occurs = 500 + 200 = 700.
Total after T occurs = 400 + 300 = 700.
Therefore, database is consistent. Inconsistency occurs in case T1 completes but T2 fails. As a result T is incomplete.
The total amount before and after the transaction must be maintained.
Total before T occurs = 500 + 200 = 700.
Total after T occurs = 400 + 300 = 700.
Therefore, database is consistent. Inconsistency occurs in case T1 completes but T2 fails. As a result T is incomplete.
Isolation
Isolation property ensures that multiple transactions can occur concurrently without leading to the inconsistency of database state. Transactions occur independently without interference. Changes occurring in a particular transaction will not be visible to any other transaction until that particular change in that transaction is written to memory or has been committed. This property ensures that the execution of transactions concurrently will result in a state that is equivalent to a state achieved these were executed serially in some order.
Let X= 500, Y = 500.
Consider two transactions T and T”.
Isolation property ensures that multiple transactions can occur concurrently without leading to the inconsistency of database state. Transactions occur independently without interference. Changes occurring in a particular transaction will not be visible to any other transaction until that particular change in that transaction is written to memory or has been committed. This property ensures that the execution of transactions concurrently will result in a state that is equivalent to a state achieved these were executed serially in some order.
Let X= 500, Y = 500.
Consider two transactions T and T”.
Suppose T has been executed till Read
(Y) and then T’’ starts. As a result , interleaving
of operations takes place due to which T’’ reads correct value
of X but incorrect value of Y and sum
computed by
T’’: (X+Y = 50, 000+500=50, 500)
is thus not consistent with the sum at end of transaction:
T: (X+Y = 50, 000 + 450 = 50, 450).
This results in database inconsistency, due to a loss of 50 units. Hence, transactions must take place in isolation and changes should be visible only after they have been made to the main memory.
T’’: (X+Y = 50, 000+500=50, 500)
is thus not consistent with the sum at end of transaction:
T: (X+Y = 50, 000 + 450 = 50, 450).
This results in database inconsistency, due to a loss of 50 units. Hence, transactions must take place in isolation and changes should be visible only after they have been made to the main memory.
Durability:
This property ensures that once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and they persist even if a system failure occurs. These updates now become permanent and are stored in non-volatile memory. The effects of the transaction, thus, are never lost.
The ACID properties provide a mechanism
to ensure correctness and consistency of a database.
DBMS Transaction States Diagram
Active State
If
a transaction is in execution then it is said to be in active state. It doesn’t
matter which step is in execution, until unless the transaction is executing,
it remains in active state.
Failed State
If
a transaction is executing and a failure occurs, either a hardware failure or a
software failure then the transaction goes into failed state from the active
state.
Partially Committed State
A
transaction contains number of read and writes operations. Once the whole
transaction is successfully executed, the transaction goes into partially
committed state where we have all the read and write operations performed on
the main memory (local memory) instead of the actual database. The reason of
having this state is because a transaction can fail during execution so if the
changes are made in the actual database instead of local memory, database may
be left in an inconsistent state in case of any failure. This state helps
us to rollback the changes made to the database in case of a failure during
execution.
Committed State
If
a transaction completes the execution successfully then all the changes made in
the local memory during partially committed state are
permanently stored in the database.
Aborted State
If
a transaction fails during execution then the transaction goes into a failed
state. The changes made into the local memory (or buffer) are rolled back to
the previous consistent state and the transaction goes into aborted state from
the failed state.
Terminated State
Transaction
get into terminated state to finish either after committed state or after
aborted state.
No comments:
Post a Comment