A MECHANISM TO CONTROL THE DEGREE OF DEPENDANCY BETWEEN TRANSACTIONS.
EVERY TRANSACTION HAS A UNIQUE AUTO ASSIGNED LSN: LOG SEQUENCE NUMBER.
TYPES OF ISOLATION LEVELS:
1. READ UNCOMITTED ISOLATION LEVEL
2. READ COMITTED ISOLATION LEVEL
3. SNAPSHOT ISOLATION LEVEL
4. SERIALIZABLE ISOLATION LEVEL
5. REPEATABLE READ ISOLATION LEVEL
6. READ COMMITTED SNAPSHOT ISOLATION LEVEL
-- 1. READ UNCOMMITTED ISOLATION LEVEL : used to read table data even though there are
-- uncommited / open transaction
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select * from DBA_DETAILS -- DIRTY READS
-- 2. READ COMMITTED ISOLATION LEVEL : used to read table IF there are
-- NO uncommited / open transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select * from DBA_DETAILS -- BLOCKING
-- 3. SNAPSHOT ISOLATION LEVEL : Used to read table data even though there are
-- uncommited / open transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select * from DBA_DETAILS -- NO BLOCKING & NO DIRTY READS
-- 4. SERIALIZABLE ISOLATION LEVEL : used to read table IF there are
-- NO uncommited / open transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
select * from DBA_DETAILS -- THERE WILL BE BLOCKING
-- 5. REPEATABLE READ ISOLATION LEVEL : used to read table IF there are
-- NO uncommited / open transaction
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
select * from DBA_DETAILS -- THERE WILL BE BLOCKING
-- 6. READ COMMITTED SNAPSHOT ISOLATION LEVEL : ABLE to read table data even
-- Though there are uncommited / open transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED SNAPSHOT
select * from DBA_DETAILS -- NO BLOCKING & NO DIRTY READS
0 comments: