1

I'd like to use READ COMMITTED SNAPSHOT in a SQL Server database, which I understand is different from SNAPSHOT ISOLATION.

I want to enable READ COMMITTED SNAPSHOT because I'm having problems with deadlocks. I'm using EF and I think this will prevent many of the deadlocks.

Reading some other questions I understand that:

READ COMMITTED SNAPSHOT implies optimistic reads and pesimistic writes while SNAPSHOT ISOLATION implies optimistic reads and optimistic writes

READ COMMITTED SNAPSHOT works for the entire database, while SNAPSHOT ISOLATION is transaction level.

If i want to enable READ COMMITTED SNAPSHOT only is the next sentence enough? ALTER DATABASE [DB] SET READ_COMMITTED_SNAPSHOT ON;

or should I also need this sentence? ALTER DATABASE [DB] SET ALLOW_SNAPSHOT_ISOLATION ON;

I'm asking this because I'm not completely sure if the mechanism SQL Server uses for READ COMMITTED SNAPSHOT will be enabled without the last sentence

Thank you

Alfin E. R.
  • 741
  • 1
  • 7
  • 24
xrodas
  • 466
  • 2
  • 10
  • Why do you want to change the isolation level? Is there a problem you think will be solved by changing isolation? The isolation level is ont a magic bullet to fix concurrency problems. If you don't clearly understand the options, you will create far bigger problems. – Panagiotis Kanavos Jul 24 '13 at 08:33
  • In other words, fix your EF code. No tweaking of the database will fix buggy ORM code – Panagiotis Kanavos Jul 24 '13 at 08:33
  • 2
    I want to change READ COMMITTED default isolation level which is a pessimistic concurrency model to READ COMMITTED SNAPSHOT which is optimistic in reads and pessimistic in writes. I understand the risks, I'm only trying to know exactly what I need to enable this isolation level. Your assumption that my code is buggy is not a very constructive answer. – xrodas Jul 24 '13 at 08:51
  • 1
    It's a standard problem with ORMs, that doesn't require modifying the database to fix. If you encounter blocking it means you keep connections and transactions open for too long, especially after update operations that cause locking. Close the transactions/connections (ie Dispose your context) and your problems will go away. – Panagiotis Kanavos Jul 24 '13 at 08:55

0 Answers0