Changing isolation level

Question

Changing isolation level

ramana3327

SSCoach

Points: 19722
More actions
April 30, 2015 at 6:02 am

#297751

Hi,
We are using sql 2008r2 standard edition.
One of our Production database is using default isolation Readcommitted.
The transactions also using readcommitted. But we want change isolation level to read comitted snapshot isolation and test it to avoid deadlocks.
Is it possible to set in the transaction level for some queries or do we need to change entire database isolation level by using alter database "ALTER DATABASE AdventureWorks2008R2 SET READ_COMMITTED_SNAPSHOT ON"

Viewing 9 posts - 1 through 8 (of 8 total)

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

MadAdmin SSChampion Points: 11261 More actions · Answer 1

http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

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

YOu can do either, but I'd recommend setting it for the database.

"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

MadAdmin SSChampion Points: 11261 More actions · Answer 3

ramana3327 (4/30/2015)
Hi,
One of our Production database is using default isolation Readcommitted.
The transactions also using readcommitted. But we want change isolation level to read comitted snapshot isolation and test it to avoid deadlocks.
Is it possible to set in the transaction level for some queries or do we need to change entire database isolation level by using alter database "ALTER DATABASE AdventureWorks2008R2 SET READ_COMMITTED_SNAPSHOT ON"

To be able to use snapshot isolation, you have to

ALTER DATABASE AdventureWorks2014 SET ALLOW_SNAPSHOT_ISOLATION ON

then, for the queries, sessions, or procedures you choose to use snapshot, you will have to

set transaction isolation level snapshot within the proc or before the query batch.

If you choose to ALTER DATABASE AdventureWorks2014 SET READ_COMMITTED_SNAPSHOT ON; then all queries will inherit the default isolation level and you won't have to manually set it in each proc or query.

I will leave it to the tattooed ScaryDBA or GilaMonster to let us know about the gotcha's when trying to apply the changes on a production DB.

Since it is a DB alter, you probably cannot have transactions or connections to the db when this change is done.

Not sure how intrusive the allowsnapshot is.

I think versioning in Tempdb is something to consider when changing the default isolation level at the DB level.

Just as an aside, if you want to stop deadlocks from occurring, I have had great success by just doing index optimization, i.e. dropping duplicates or indexes which are subsets of other indexes, factorizing many indexes with similar Keys into one supreme index.

If after index optimization there are still deadlock issues, then you could look at fixing the procs which have this issue.

If you still are getting deadlocks, then you can consider returning the error code to the application and consider coding the application to retry when it gets a deadlock error code from the DB.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

ramana3327 SSCoach Points: 19722 More actions · Answer 4

To use RCSI for individual query

we have to use

ALTER DATABASE AdventureWorks2014 SET READ_COMMITTED_SNAPSHOT ON

for each transaction if it is not enabled in the db level

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

ramana3327 (5/1/2015)
To use RCSI for individual query
we have to use
ALTER DATABASE AdventureWorks2014 SET READ_COMMITTED_SNAPSHOT ON
for each transaction if it is not enabled in the db level

No.

You can't use read committed snapshot for individual queries.

Read committed snapshot is turned on for the *entire database* with ALTER DATABASE <db name> SET READ_COMMITTED_SNAPSHOT ON

Alternately, you can allow snapshot isolation with ALTER DATABASE <db name> SET ALLOW_SNAPSHOT_ISOLATION ON and then use SET TRANSACTION ISOLATION LEVEL before every query that you want to use snapshot isolation level.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramana3327 SSCoach Points: 19722 More actions · Answer 6

Thank you. I am clear about that now but

Is the snapshot isolation can be applied to any type of queries. i.e. functions also or is there limit?

Is the serializable is also db level or just transactional level?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

With the SOLE exception of read committed snapshot, isolation levels are effective from the SET TRANSACTION ISOLATION LEVEL statement until the end of the batch or until the isolation level is changed.

As for limitations, please, please, please do some reading on these before you just go and turn them on.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 8

GilaMonster (5/2/2015)
With the SOLE exception of read committed snapshot, isolation levels are effective from the SET TRANSACTION ISOLATION LEVEL statement until the end of the batch or until the isolation level is changed.
As for limitations, please, please, please do some reading on these before you just go and turn them on.

Further on Gail's response, this book, Understanding SQL Server Concurrency is a good place to start!

😎