A SQL Server isolation level using MVCC to provide a point-in-time view of the database for the life of the transaction. Not to be confused with RCSI, which provides the same view at a statement-level.
Questions tagged [snapshot-isolation]
58 questions
13
votes
2 answers
Unclear update conflict
I have two questions:
1. Why do I get update conflict in this situation instead of just blocking:
-- prepare
drop database if exists [TestSI];
go
create database [TestSI];
go
alter database [TestSI] set READ_COMMITTED_SNAPSHOT ON;
alter database…
Pavel Zv
- 173
- 8
12
votes
1 answer
In case of multiple updates of the same record in a single transaction, how many versions are being stored?
According to MSDN article by Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation
"... All the earlier versions of a particular record are chained in a linked list; and in the case of long-running row…
Fulproof
- 1,362
- 2
- 25
- 33
12
votes
1 answer
Why am I getting a snapshot isolation issue on INSERT?
Given two tables
Parent
KeyID GroupID Name Active
Child
KeyID ParentID Name
Child.ParentID is FKed to Parent.KeyID
We insert both Parent and Child in a single transaction.
If a different Parent row gets updated (eg. Active 1 -> 0) while…
joshschreuder
- 173
- 8
10
votes
1 answer
TRANSACTION ISOLATION LEVEL SNAPSHOT vs. TRUNCATE?
I'm hoping someone can shed some light on this behavior that I was not expecting regarding SNAPSHOT isolation vs. TRUNCATE.
Database: Allow Snapshot Isolation = True; Is Read Committed Snapshot On = False.
Procedure1 (Replaces content of table foo…
Mark Freeman
- 1,938
- 5
- 24
- 43
10
votes
5 answers
Why am I getting "Snapshot isolation transaction aborted due to update conflict"?
We have two tables
Parent (Id int identity, Date datetime, Name nvarchar)
Child (Id int identity, ParentId int, Date datetime, Name nvarchar)
The Child having a foreign key relationship to the Parent.
We have enabled database level read…
Mark
- 799
- 1
- 6
- 17
9
votes
3 answers
"When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained"?
The MSDN online article "Snapshot Isolation in SQL Server" states:
"An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, it remains in effect until the connection is closed…
Fulproof
- 1,362
- 2
- 25
- 33
9
votes
1 answer
Shared locks taken under Read Committed Snapshot Isolation (RCSI)
I am seeing waits for shared locks (LCK_M_S) when using RCSI. My understanding is that this isn't supposed to happen since SELECTs do not require shared locks when using RCSI.
How can I be seeing shared locks? Is it because of foreign keys?
grassbl8d
- 467
- 2
- 5
- 14
7
votes
1 answer
Does RCSI use less or same space than SNAPSHOT? Why?
Trying to better understand row-versioning based transaction isolations - RCSI (Read Committed Snapshot Isolation and SNAPSHOT) - in SQL Server...
MSDN online article "Choosing Row Versioning-based Isolation Levels" states:
*"For most…
Fulproof
- 1,362
- 2
- 25
- 33
7
votes
2 answers
Why is it not possible to create indexes on temp tables in SNAPSHOT isolation?
When I try to create indexes on a temporary table in SQL Server when using the SNAPSHOT transaction isolation, I get this error:
Transaction failed because this DDL statement is not allowed inside a snapshot isolation transaction. Since metadata is…
Tom Pažourek
- 333
- 2
- 12
7
votes
1 answer
Snapshot_isolation_state_desc on a Read only/Stand by Database
I have configured Log Shipping where the primary server database has snapshot_isolation_state_desc reported as off. I tried to turn it off on the secondary which is a standby read only by using the GUI and by using the T-SQL statement to…
SQL_NoExpert
- 1,005
- 10
- 29
7
votes
4 answers
Is mysqldump a "hot" backup kind?
I see in the documentation that the database backup tools are divided into four categories: hot backups, cold backups, physical backups and logical backups.
I understand the the most important difference between cold and hot backup is that the…
knocte
- 231
- 2
- 13
6
votes
1 answer
Setting READ_COMMITTED_SNAPSHOT to ON while ALLOW_SNAPSHOT_ISOLATION is OFF
I experimented with various configurations of SQL Server databases and ended up setting READ_COMMITTED_SNAPSHOT to ON while ALLOW_SNAPSHOT_ISOLATION is OFF.
I noticed that when enabling this, many queries got a lot faster. I'm still using the…
Tom Pažourek
- 333
- 2
- 12
5
votes
1 answer
Why are U locks required when using Read Committed Snapshot Isolation
I consider myself a beginner at Sql Server locking.
My understanding is that when using RCSI, Sql Server doesn't need to issue S locks because it uses row versioning (in most cases). From…
Clement
- 446
- 1
- 4
- 13
5
votes
1 answer
Why is 'Allow Snapshot Isolation' set to TRUE on the master and msdb databases?
Why is the database property Allow Snapshot Isolation set to TRUE on the master and msdb databases, but FALSE on other system databases? The default option for this property is also FALSE when creating user databases.
jrara
- 5,153
- 19
- 52
- 65
5
votes
1 answer
Distributed transaction and Read Committed Snapshot
Distributed transactions are not supported for snapshot isolation level in SQL Server 2008 R2. But what about read committed snapshot?
Rolf
- 153
- 2