What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 127 Stack Exchange communities.

I read Jeff Atwood's article on Sql Server deadlocks. He said he switched the isolation level to use snapshots. I've seen two types of snapshot commands. I'm wondering--what command(s) did he run?

Only Read_Committed_Snapshot?

ALTER DATABASE <dbname>  SET READ_COMMITTED_SNAPSHOT ON

Or only Allow_Snapshot_Isolation?

ALTER DATABASE <dbname>  SET ALLOW_SNAPSHOT_ISOLATION ON

Or Both?

ALTER DATABASE <dbname>  SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE <dbname>  SET READ_COMMITTED_SNAPSHOT ON
share|improve this question
    
If one is good, both is better –  jmfsg May 6 '10 at 23:48
add comment

1 Answer

up vote 2 down vote accepted

Looks like both are required. It is documented here

http://msdn.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx

Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. This activates the mechanism for storing row versions in the temporary database (tempdb).

Setting the READ_COMMITTED_SNAPSHOT ON option allows access to versioned rows under the default READ COMMITTED isolation level. If the READ_COMMITTED_SNAPSHOT option is set to OFF, you must explicitly set the Snapshot isolation level for each session in order to access versioned rows.

share|improve this answer
    
Jeff, both are not required. READ_COMMITTED_SNAPSHOT just makes snapshots the default; otherwise you explicitly have to SET TRANSACTION ISOLATION LEVEL SNAPSHOT before every BEGIN TRAN where you want that particular mode. –  Aarobot May 7 '10 at 0:06
    
@aaro I still don't understand. what happens if you issue R_C_S ON without issuing A_S_I ON? In other words, we're turning on an option that.. isn't yet allowed? –  Jeff Atwood May 7 '10 at 0:18
    
I don't think you're allowed to turn RCS on without ASI. But the other way around is acceptable. You can use ASI with explicit snapshot transactions, and other transactions will simply use the default READ COMMITTED isolation (no snapshot). –  Aarobot May 7 '10 at 0:28
add comment

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .