Sql server Isolation levels

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Sql server Isolation levels

Sql server Isolation levels

Rate Topic

Display Mode

Topic Options

Author

Message

sql_2005_fan

Posted Thursday, February 07, 2013 5:31 PM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:06 PM
Points: 86, Visits: 66

HI,

Which is the most preferred isolation level in T-SQL? Does it change from case to case? Say does it differ from a OLAP,OLTP,web application or is it the same? what is the reason?

Thanks.

Post #1417401

anthony.green

Posted Friday, February 08, 2013 3:23 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Default is READ COMMITTED, typically only change it if you are having problems, and I always stay away from READ UNCOMMITTED

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1417562

opc.three

Posted Wednesday, February 13, 2013 1:30 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 6,737, Visits: 11,791

As was stated READ COMMITTED is the default iso level and the default iso level cannot be changed to something else. Once connected anyone can explicitly change the iso level for their session using the SET TRANSACTION ISOLATION LEVEL command.

READ_COMMITTED_SNAPSHOT isolation (RCSI) is an alternate behavioral mode of READ COMMITTED that is worth looking into if you're experiencing blocking in a database where both reads and writes occur simultaneously, e.g. in an OLTP system also used for some real-time reporting. I would strongly recommend RCSI over READ UNCOMMITTED in that scenario. There are very few use cases where READ UNCOMMITTED is safe.

It's not really a question of the type of system, e.g. OLTP, OLAP, etc., it's a question of how much concurrency do you want to support versus how much data-integrity protection you need.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1419732

« Prev Topic | Next Topic »

Permissions