Which Isolation Level is used commonly?

Question

Which Isolation Level is used commonly?

Suresh Arumugam

Hall of Fame

Points: 3370
More actions
May 5, 2010 at 8:18 am

#144483

Hi All,
I am wokring on customizing Learning Management System which was implemented to use "Read Uncommitted" isolation level. May be because it's NOT a mission critical application.
How ever, I am wondering which Isolation levels are more common in the mission critical applications? As I understand, choosing Isolation level requires closer look into application performance. Though I understand different isolation levels (both Pessimistic, Optimistic), I don't have exposure to different type of applications and hence NOT sure ON what are industry standards in choosing the Isolation level.
Please help me..
Suresh
Regards,
Suresh Arumugam

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

The most common is "Read committed", it is also the default. What you are using is what is commonly referred to as "dirty reads". The isolation levels you are referencing seem to be what used to be refered to in Access circles. My experience is that most systems do not change the default isolation level at the server level. They sometimes change it at a process or sproc level to handle specific cases. This is also the model I have used in the past.

CEWII

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Read uncommitted is pretty dangerous for any number of reasons.

Most systems I work with are Read_Committed for all the details provided above. Some of the newer systems under development in 2008 are using read_committed_snapshot to take advantage of the reduced locking offered by snapshot isolation.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 3

Thanks guys for the valuable replies.

Elliott: In your reply, you have mentioned as below.

They sometimes change it at a process or sproc level to handle specific cases.

Does it mean changing the lsolation level in Sproc level? If so, what Isolation levels you generally prefer? Is sql 2005 Snapshot (Optimistic options) isolation level commonly used now?

Regards,
Suresh Arumugam

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Basically what I am getting at is that for specific conditions you might issue a:

SET TRANSACTION ISOLATION LEVEL [isolation levlel]

within the sproc. I usually only change the level when I have a very specific need. With that said I have never used READ UNCOMMITTED at the sproc level, I usually only perform that at the query level using the "(NOLOCK)" syntax. And only when I have thought through the ramifications of it.

I'm sorry I can't be much clearer, but the basic answer is, it depends..

CEWII

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

And the developers can change the isolation level with connection properties as well.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 6

Thanks for your thoughts.

How ever, I am wondering how come "Pantom Reads", "Non Repeatable Reads", "Lost Updates" are handled if we commonly use "Read Commited" Isolation levels.

Looks like we generally bother only about "Dirty reads" even in mission-critical applications? Or do we follow some additional logics while using default Isoloation level?

Thanks,

Suresh

Regards,
Suresh Arumugam