Transaction isolation level

We should be aware of some information about Isolation level. As the transaction is depends on isolation level the level.

Sql server has following Isolation levels:

1. Read Uncommitted (No Lock)

2. Read Committed (Blocking)

3. Repeatable Read (Phantom)

4. Snapshot  (No blocking)

5. Serializable (No blocking)

more information about it  here.

Dirty reads:Data might be changed by other process but we might retrieve old unchanged data – wrong data.

Non-Repeatable reads: while reading the data if you read the data in a block it could be possible that your first and second output of the same query would be different, when at the same time other process changed the data.

Phantom Read: in this situation, if two sessions are running and first session query returns some records, during the same time other session inserts the record that extra record will show in the second session.

1. READ UNCOMMITTED :

In this isolation level, sql server don’t maintain any Shared locks, so while retrieving of the data you will you could get Dirty reads.

 

2. READ COMMITED:

Here it will have shared lock but it will be exist till command completes, that way you don’t get dirty reads but still Phantom and non repeatable read.

 

3. REPEATABLE READ:

It prevents the other reads but still other inserts is allowed makes Phantom read exists.

  1. SERIALIZABLE :

This is the most restricted isolation level but it also impact performance as transaction is isolated until it completes.

Isolation Level Phantom Read Non Repeatable read Dirty Read
Read Uncommitted

X

X

X

Read Committed

X

X

0

Repeatable Read

X

0

0

Serializable

0

0

0

Now the more strong our isolation is more security we will get but it comes with performance, as more secure makes system more Locks and other transaction has to wait to successfully completion of first one.

Sql server 2005 introduces Snapshot isolation level (Versioning) .

To check what is the current version use below command.

DBCC USEROPTIONS

 Great link:

 
 
 
Advertisements
This entry was posted in Basic, Computers and Internet, Isolation Level, Lock/Blocking and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s