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]
70 questions
16
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
- 941
- 1
- 8
- 20
14
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
- 183
- 9
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,392
- 2
- 26
- 36
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
- 9
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
- 2,293
- 5
- 32
- 54
10
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,392
- 2
- 26
- 36
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
- 477
- 2
- 5
- 14
9
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
- 553
- 7
- 19
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,392
- 2
- 26
- 36
7
votes
1 answer
Is it possible to set DEFAULT TRANSACTION ISOLATION for all connections in SQL Server?
I want the default Transaction Isolation level to be SNAPSHOT.
How do I set this on a SQL Server 2016 database?
Inquisitor Shm
- 213
- 2
- 6
7
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
- 553
- 7
- 19
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,107
- 1
- 21
- 37
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
- 251
- 3
- 13
6
votes
1 answer
Is it possible to use Read Uncommitted isolation level on read-only Availability Group secondary?
We are using availability groups in SQL 2019 Enterprise edition. We are using the enterprise feature to allow the AG secondary nodes to be in Read-only mode, and then running reporting queries against the secondary by connecting to the listener with…
Mark
- 941
- 1
- 8
- 20
6
votes
1 answer
Truncate Table locking sys views
We have a proc that runs Truncate Table inside an Snapshot transaction. This seems to be causing a LOCK_M_S lock that blocks the sys view sys.partitions.
Is there a convenient work around for this? I like the efficiency of not taking excess logs…
Jamie Marshall
- 297
- 2
- 8