Database Transaction

Transaction is a Collection of operations that forms a single logical unit of work.

A transaction is an atomic unit of work that either Fails or Succeed. There is no such term Partial Transaction.

It can be made up of many steps; each step in the transaction must succeed for the transaction to be completed successfully. If any part of the transaction fails then the entire transaction fails.

When Transaction Fails : System needs to return to the state that it was before the transaction was started. This is called Rollback.

Commit => Any change made during transaction process, by this transaction are committed.

Abort/Error => All changes made during transaction process, are not made to database. The result of this is as the transaction never started.

Transaction Properties

ACID Properties

A ==> Atomicity (for integrity; either all operation are reflected or none of them)

C ==> Consistency Preservation

I ==> Isolation (Independent)

D ==> Durability (Backup)

Atomicity

Requires the database modifications must follow an “ All or Nothing” rule. Each transaction is said to be atomic, if any one part of transaction fails; the entire transaction fails and the database state is left unchanged.

A system must guarantees the atomicity in every situation, including power failures/error/crashes or any undefined failure.

This guarantees that an incomplete transaction does not exists in DBMS.

Consistency ( Compatibility )

Ensures the transaction performed on the database, will take it from one consistent state to another consistent state ( means changes the values in an instance are consistent with changes to the other values in the same instant ) and valid data will be written to the database.

If for some reason, a transaction is executed that violates the database’s consistency rules; the entire transaction will be rolled back & the database will be restored to the latest consistent state.

Eg. Primary key constraints that defines the each row in the table independent ( no data conflicts occurs )

Isolation

That no transaction should be able to interface with another transaction at all.

It should not be possible that two transaction affects the same rows running concurrently, as the outcome would be unpredicted & the system thus, made unreliable.

To implement this property, use serial model where any two transactions can occur on the same data at the same time where the result is predictable.

Durability

Once transaction has been committed, it will remain so. It ensures that any transaction committed to the database will be lost.

Durability ensures through the use of database backups and transaction logs that facilitates the restoration of committed transaction in spite of any subsequent software or hardware failures.

Every committed transaction is protected against power loss/crash errors and can’t be lost by the system.

Eg . while making online payment, if you click even a refresh button then the whole transaction drops and the amount you entered for payment is made back to your account.