Design of Main Memory Database System/Transaction

Chapter 7: Transaction Management

A transaction is basic unit of work that comprises many database operations. Transactions shall either be committed or rolled back. This means it shall decide whether to submit all the changes to DBMS or to neglet all the changes and take the database state to the state when that transaction started.

Transaction Stages

For recovery purposes, DBMS should maintain the state of the transaction. A transaction state changes are given below

Transaction Start
Database operations (INSERT, UPDATE, DELETE, SELECT)
Commit or Abort

Database operations include read and modifications of data records. The effect of running SELECT statement is read and effect of running INSERT, UPDATE and DELETE is data record modifications.

The commit operation informs the successful end of transaction to the transaction manager. After commit operation, database should be in consistent state and all the updates made by that transaction should be made permanent.

The rollback or abort operation informs the transaction manager that there is an error in one of the operations involved in the transaction and database is in inconsistent state. All the updates made by the transaction must be undone on the database to get it back to the previous consistent state; that is state at which the transaction started.

Transaction pseudo code for money transfer

BEGIN TRANSACTION;

Read account1 balance

If read failed GOTO UNDO;

Reduce Rs 100 from account1 balance

If reduce failed GOTO UNDO;

Read account2 balance

If read failed GOTO UNDO;

Add Rs 100 to account2 balance

If add failed GOTO UNDO;

COMMIT;

GOTO FINISH;

UNDO:

ROLLBACK;

FINISH:

RETURN;

Transaction Properties

Transaction processing guarantees four important properties (referred as ACID properties)

Atomicity — Each transaction is treated as all or nothing- it either commits or aborts. If a transaction commits, all its effects remain. If it aborts, all its effects are undone.

Consistency — Transactions preserve data consistency; it always takes the database from one consistent state to another consistent state. Intermediate state shall be inconsistent, but commit or rollback operation should again take the database to another new consistent state or to the old consistent state.

Isolation — Transactions should be isolated from one another. Even though all transactions run in parallel, updates made by one transaction should not be visible to other and vice versa until the transaction commits.

Durability — Transaction commit should ensure that its updates are present in the database, even if there is a subsequent system crash.

Correctness Principle

If a transaction executes in the absence of any other transaction or system error, and it starts with the database in a consistent state, then the database is also in a consistent state when the transaction ends. This means that if we control simultaneous transactions and system errors, transactions always ensure correctness.The former is controlled by concurrency manager and the later is controlled by logging and recovery manager.