Transaction Scope

DATABASE ACID CONCEPT

Nicholas Gary in 1981 published whitepaper with transaction concept that had below rules.

CONSISTENCY : Transaction must obey rules and regulation.

ATOMICITY : Transaction must all fail or succeed together.

DURABILITY : Changes much be persistent

Haerder & Reuter wrote "Principal of transaction oriented database recovery" which includes commit, rollback redologs and recovery mechanism, which includes below rules 

ACID Concepts

ATOMICITY ::  If one transaction in set fails all should fail and entire transaction can only succeed if all sub-transaction passes.

E.g.  If your are transferring X amount from Individual A to Individual B  there are two updates to be done

1. Deduct amount X from A's account
2. Deposit amount X to B's account

In this case both the transaction should be successful else none should happen. it should not be the case that only one of the transaction is successful and other failed this may result in inconsistency of data.

CONSISTENCY  ::  transaction must comply system rules and standards 

e.g Select , insert , update & delete must be as per system rules.

This is more dependent on system to system what rules that particular system, this may be valid as per database rules but not as per system.

E.g of system rule.  

Rule 1:  Minimum balance of any account cannot be less then 1000.

E.g. 

• A's account Balance 2500  
• B's account Balance 5000 
• Transaction Transfer 2000 from A's account to B's Account.

This transaction is purely valid but does not comply system rules hence this transaction need to be rolled back and amount on both the account remains same.

ISOLATION ::  Each user cannot see transaction of other user until transaction is complete 
When ever any transaction is going on user will gain lock of that data so that no other user can read or perform operation until transaction is not complete

E.g. if you do not provide isolation you may lead to below problem 

This can be the issue if we allow multiple transaction on same data at a same time.

We have various level of locking in order to avoid inconsistency

• Tables
• Data Rows
• Data blocks
• Cached Items
• Connections
• Entire System

Until lock is not released no other user is allowed to update same data.

Example of simple locking to maintain consistency and isolation

Time	User 1 Actions	User 2 Actions	User 3 Actions
1	Starts Transaction
2		Starts Transaction
3			Starts Transaction
4	Updates row 2 in table A
5		Attempts to update row 2 in table A
		U2 Is Blocked by U1
6			Attempts to delete row 2 in table A
			U3 Is Blocked by U1
7	Commits transaction
		Update completes	U3 Is Blocked by U2
8		Rolls back transaction
			Delete completes
9			Commits transaction

DURABILITY :: Changes made should be permanent.

• Data must be stored permanently on Disk.
• System must with stand any type of failure e.g Media / Power failure
• This can be achieved by use of Logs (Redo logs)
• Helps to recover in case of any failure this can be rollback or commit
• Setting up Data Recovery Strategies are part of DBA job. 

<< BACK HOME >>