Transaction Management.

Transaction management  is define as change in state of database from one consistent state to another consistent state i.e Transaction is unit of work which is always legal. 

For transaction to be legal, it has to satisfy a property called as ACID property where:

Atomicity : A transaction should execute in its entirety or non. To achieve atomicity in transaction, database   vendor has provided the commands such as commit and rollback where commit is used to finalize the transaction if it is complete, otherwise rollback is used which reverse the transaction to the point from where it started.

Consistency : At the end of the transaction all the value must be legal in totality. To achieve consistency again you use commit and rollback.

Isolation : No two or more transaction could be visible to each other i.e no transaction should interface i.e a transaction should execute in a way such that is only the transaction that is running at particular instance.

To achieve isolation in transaction, database implement mode of locks and level of locks.

Mode of lock: There are two modes of lock.

1. Shared Mode: This mode of lock is less privilege mode in which multiple transaction can read the data at same time. 

2. Exclusive Mode : This is the strict level of locking in which transaction will  perform a write operation over the data and hence only one transaction can  reserve the data at the particular instance, which means in this mode of locking the data is not allowed for multiple read and write by various transaction.                                                                                                                             

Level of Lock: There are 6 levels of lock.

1. Database Level : Database level of locking is the biggest, where the whole database is lock for single transaction , this level of lock are very-very rare which will be used only for major database update. 

2. Table Level : Table level lock is second biggest level of lock which have also rare and will be used to perform major updates in part of database.  

3. Page Level      

4. Row Level                                                                        

5. Column Level

Note:- Other levels of lock such as page level, row level and column level are going to be implemented frequently as each transaction required very few data out of the whole.

6. Cell Level : Cell level lock is smallest level of lock which is define theoretically but practically it is not possible, it will require lots of overheads to be maintain.

Durability : Database values must be persistent i.e it should not be volatile and values will change only if it is change explicitly. To achieve durability, we use commit and rollback.