Transaction Isolation Levels
1. Dirty Read:
when uncommitted data reads.
Let’s say tran-1 updates a row within transaction but it’s not committed and another tran-2 reads the updated row after that tran-1 rollback but tran-2 already read that is dirty read.
2. Non Repeatable read:
when same transaction reads same row twice, and get a different value of each time.
Like suppose tran-1 reads data and another tran-2 update and commit, Now if tran-1 rereads the same data, it will retrieve a different value.
3. Phantoms:
when two same queries are executed, but retrieved different rows.
Like suppose tran-1 reads a set of rows that satisfy some search criteria and tran-2 insert new rows that matches the search criteria for tran-1. If tran-1 re-executes the statement that reads the rows, it gets a different set of rows.
Types of Transaction levels:
1. READ UNCOMMITTED
2. READ COMMITTED
3. REPEATABLE READ
4. SNAPSHOT
5. SERIALIZABLE
1. READ UNCOMMITTED:
Specifies that statements can read rows that have been modified by other transactions but not yet committed.
— Tran-1
BEGIN tran
insert into temp1 (id,name) values(2,’bb’)
— Tran-2
set tran isolation level read uncommitted
select * from temp1
There tran-1 inserting1 row but it’s not commit yet but tran-2 read it using read uncommitted transaction isolation level
2. READ COMMITTED:
Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads.
— Tran-1
BEGIN tran
insert into temp1 (id,name) values(2,’bb’)
— Tran-2
set tran isolation level read committed
select * from temp1
Tran-1 inserts 1 row but yet transaction not completed so Tran-2 waiting for Tran-1’s commit or rollback command, because READ COMMITED not allow dirty read.
3. REPEATABLE READ:
cannot read data modified by another transaction that has not yet committed and also other transactions can’t modify (delete & update only) data being read by the current transaction until it completes, but we can insert new rows.
Means repeatable read prevents dirty reads and nonrepeatable reads.
tran-1 insert 1 row in transaction but it’s not complete
Tran-1 yet not completed so Tran-2 can’t read also marked lock so other transactions can’t modify (delete & update only) data being read by the current transaction until it completes.
Tran-2 mark locked so tran-3 can’t update (modify)
Tran-2 mark locked so tran-4 can’t delete (modify)
Note:
- Tran-1 yet not completed so it will create 3types of lock
i) RID (X), ii). PAGE (IX) and iii). OBJECT(IX)
- Tran-2 also mark 3 locks
i) RID (S), ii). PAGE (IS) and iii). OBJECT(IS)
- Tran-3 mark 4 types lock
i) RID(U), ii) RID(X), iii) PAGE(IX), iv) OBJECT(IX)
- Tran-4 mark 3 types lock
i) RID(U), ii), ii) PAGE(IU), iii) OBJECT(IX)
4. SERIALIZABLE:
cannot read uncommitted data, other transaction can’t modify or insert new rows in that search criteria until it completes. Means Serializable prevents dirty reads, non-repeatable reads and phantom reads.
5. SNAPSHOT:
Snapshot isolation avoids most locking and blocking by using row versioning and it is set on database level.
it’s maintaining row versioning in temdb for committed data. So when data updated in current transaction and yet not completed but another transaction try to read that time last committed data will return.
Means it wills not waiting for transaction completion and not reading dirty data.
create database db1
go
select snapshot_isolation_state, snapshot_isolation_state_desc, is_read_committed_snapshot_on, recovery_model
from sys.databases where name=’db1′;
ALTER DATABASE db1 SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE db1 SET ALLOW_SNAPSHOT_ISOLATION ON;
select snapshot_isolation_state,snapshot_isolation_state_desc,is_read_committed_snapshot_on,recovery_model
from sys.databases where name=’db1′
go
use db1
go
CREATE TABLE tmp01 (ID int,name varchar(100));
— Tran-1
BEGIN tran
insert into tmp01 (id,name) values(2,’bb’)
— Tran-2
set tran isolation level SNAPSHOT
select * from tmp01
Recent Comments