Stack Overflow
Stack Overflow

Questions tagged [read-committed-snapshot]

34 questions
143
votes
3 answers

How to detect READ_COMMITTED_SNAPSHOT is enabled?

In Microsoft SQL Server, is there a way to detect whether a database has had its isolation level set via the T-SQL command ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON;? I cannot find a simple way to detect this in either T-SQL or via…
Chris Driver
  • 2,245
  • 2
  • 19
  • 14
91
votes
10 answers

How long should SET READ_COMMITTED_SNAPSHOT ON take?

How long should it take to run ALTER DATABASE [MySite] SET READ_COMMITTED_SNAPSHOT ON I just ran it and it's taken 10 minutes. How can I check if it is applied?
Simon_Weaver
  • 140,023
  • 84
  • 646
  • 689
91
votes
18 answers

SELECT FOR UPDATE with SQL Server

I'm using a Microsoft SQL Server 2005 database with isolation level READ_COMMITTED and READ_COMMITTED_SNAPSHOT=ON. Now I want to use: SELECT * FROM FOR UPDATE ...so that other database connections block when trying to access the same…
tangens
  • 39,095
  • 19
  • 120
  • 139
84
votes
5 answers

Read committed Snapshot VS Snapshot Isolation Level

Could some one please help me understand when to use SNAPSHOT isolation level over READ COMMITTED SNAPSHOT in SQL Server? I understand that in most cases READ COMMITTED SNAPSHOT works, but not sure when go for SNAPSHOT isolation. Thanks
user173552
  • 1,169
  • 2
  • 12
  • 13
18
votes
3 answers

Why is READ_COMMITTED_SNAPSHOT not on by default?

Simple question? Why is READ_COMMITTED_SNAPSHOT not on by default? I'm guessing either backwards compatibility, performance, or both? [Edit] Note that I'm interested in the effect relating to the READ_COMMITTED isolation level, and not the snapshot…
MattH
  • 4,166
  • 2
  • 29
  • 33
16
votes
2 answers

How programmatically enable READ COMMITTED SNAPSHOT in SQL Server?

I need to programmatically enable READ COMMITTED SNAPSHOT in SQL Server. How can I do that?
João Vieira
  • 1,102
  • 1
  • 13
  • 17
7
votes
1 answer

Read Committed Isolation level in SQL server for a single statement

Say, I have a person table and it has only 1 row - id = 1, name = 'foo' On one connection select p1.id, p1.name, p2.name from person p1 join person p2 on p1.id = p2.id On another connection at the same time: update person set name = 'bar' where…
Raghu Dodda
  • 1,505
  • 1
  • 21
  • 28
6
votes
0 answers

SQLServer. Unable to make ALLOW_SNAPSHOT_ISOLATION work in c# code (it works on Management Studio)

Recently I had to resolve lock problems such as Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. After reading several articles and analyzing on the context of my…
matias.g.rodriguez
  • 849
  • 11
  • 14
6
votes
2 answers

Oracle equivalent of SQL Server Snapshot isolation

In Microsoft SQL Server, I use the READ_COMMITTED_SNAPSHOT ISOLATION ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON In Session 1,update the Principal from 4000 to 5000 BEGIN…
VenVig
  • 645
  • 1
  • 10
  • 14
5
votes
1 answer

SQL Azure and READ_COMMITTED_SNAPSHOT

I would like to set READ_COMMITTED_SNAPSHOT to ON on my SQL Azure database, but the following code, which works with other versions of SQL Server, is not supported in Azure: ALTER DATABASE [database_name] SET READ_COMMITTED_SNAPSHOT ON GO First…
Thomas Amar
  • 352
  • 10
  • 20
4
votes
1 answer

Do DB locks require transactions?

Is it true that "Every statement (select/insert/delete/update) has an isolation level regardless of transactions"? I have a scenario in which I have set update of statements inside a transaction (ReadCommitted). And another set not in a transaction…
Buzz
  • 295
  • 2
  • 13
3
votes
0 answers

Is there any way to set default isolation level of EF Code First(earlier versions than 6) to READ_COMMITTED_SNAPSHOT?

According this link in Entity Framework web site, default transaction isolation level of EF6 changes to READ_COMMITTED_SNAPSHOT. Default transaction isolation level is changed to READ_COMMITTED_SNAPSHOT for databases created using Code First,…
Masoud
  • 8,020
  • 12
  • 62
  • 123
2
votes
3 answers

Tuning SQL Server 2008 for web applications

In one of the Stackoverflow podcasts, I remember Jeff Atwood saying that there was a configuration option in SQL Server 2008 which cuts down on locking, and was kind of an alternative to using "with (nolock)" in all your queries. Does anybody know…
Kibbee
  • 65,369
  • 27
  • 142
  • 182
2
votes
2 answers

Is READ UNCOMMITTED / NOLOCK safe in this situation?

I know that snapshot isolation would fix this problem, but I'm wondering if NOLOCK is safe in this specific case so that I can avoid the overhead. I have a table that looks something like this: drop table Data create table Data ( Id BIGINT NOT…
Ben Challenor
  • 3,365
  • 1
  • 35
  • 35
2
votes
4 answers

How to figure the read/write ratio in Sql Server?

How can I query the read/write ratio in Sql Server 2005? Are there any caveats I should be aware of? Perhaps it can be found in a DMV query, a standard report, a custom report (i.e the Performance Dashboard), or examining a Sql Profiler trace. I'm…
Bill Paetzke
  • 13,332
  • 6
  • 44
  • 46
1
2 3