Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [snapshot-isolation]

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.

Filter by
Sorted by
Tagged with
1 vote
2 answers
803 views

Why does the SERIALIZABLE transaction isolation level not guarantee point-in-time views of data?

I have been told that if your transaction consists of multiple statements, then the SNAPSHOT transaction isolation level is the only way to guarantee consistent point-in-time views of data. Why is the ...
J. Mini's user avatar
  • 431
0 votes
1 answer
36 views

Is snapshot isolation only potentially different from serializability if there are "cycles" between transactions with reads and writes?

I'm trying to figure out a better intuition for what exactly are the sorts of consistency anomalies that snapshot isolation allows to happen. The description on Wikipedia says: In databases, and ...
Phoenix's user avatar
  • 101
0 votes
0 answers
67 views

Redshift Serializable isolation violation error

Our setup: We have a Redshift cluster as our data warehouse. We have a Postgres OLTP DB from which a number of write-heavy tables are getting replicated into Redshift using DMS. We use dbt to then ...
Steve Robinson's user avatar
1 vote
2 answers
301 views

Locking/Isolation - MariaDB

In MariaDB the default isolation level is repeatable read. I understand this means when I open a transaction I will not see any concurrent writes to the DB. I also have a situation where I need to ...
Daniel Benzie's user avatar
0 votes
1 answer
101 views

EF Queries Using Read Commited Though snapshot_isolation_state and is_read_committed_snapshot_on are ON

I am running this query, SELECT name , snapshot_isolation_state , is_read_committed_snapshot_on FROM sys.databases s name snapshot_isolation_state is_read_committed_snapshot_on master ...
Imran Qadir Baksh - Baloch's user avatar
2 votes
1 answer
290 views

Does ALLOW SNAPSHOT ISOLATION need to be set on Tempdb itself?

I'm in a debate over whether you also need to set ALLOW SNAPSHOT ISOLATION on tempdb itself, or only on user databases for the functionality to kick in (in addition to code referencing this)? Most ...
stack_henk's user avatar
1 vote
2 answers
461 views

SQL HA Cluster TempDB Version Store blocking on secondary Replica due to open transaction?

I am currently investigating a repeating error which occurs on the secondary Replica of our 2 node Alwasy on High Availability cluster. The Replica is set up with Read-Intent only because we use a ...
Vizchris's user avatar
3 votes
1 answer
469 views

The finer points of writers locking each other out under the snapshot isolation levels

The read committed snapshot and snapshot isolation levels in SQL Server do away with most locking except one: A writer still locks out other writers. The documentation tiptoes around saying as much ...
John's user avatar
  • 765
5 votes
2 answers
2k views

Understanding of SQL Server version store

I'm trying to understand SQL Server version store and related isolation levels. As I understand it, when a database enabled read committed snapshot option, this could happen: An item (id = 1) has ...
Fajela Tajkiya's user avatar
-1 votes
2 answers
118 views

How does the read committed snapshot isolation level relate (link) the row in tempdb to the original row?

I understand that the read committed snapshot isolation level holds the row versions in the tempdb. I enabled this feature on a test database and I was expecting to see a new column (guid) get ...
variable's user avatar
  • 3,634
6 votes
1 answer
1k views

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 ...
Mark's user avatar
  • 899
4 votes
1 answer
422 views

Snapshot isolation transaction aborted due to update conflict when removing rows from the database

I have spent some time on this issue and finally have an example which reproduces the issue (even with the appropriate non-clustered indexes on all FKs). This a brief overview of the database ...
Sputnik's user avatar
  • 41
3 votes
2 answers
2k views

Read-only transaction anomaly

I've learned about this anomaly from these papers: https://www.cs.umb.edu/~poneil/ROAnom.pdf (original) https://johann.schleier-smith.com/blog/2016/01/06/analyzing-a-read-only-transaction-anomaly-...
Max's user avatar
  • 41
1 vote
1 answer
104 views

RCSI & ETL processing compatibility

The Situation: I have a write-heavy, read-light ETL workload with the basic process of: get chunk of raw data (generally 1000-5000 records) from an import landing table. pass the chunk of data from ...
SqlNightOwl's user avatar
1 vote
1 answer
108 views

Using SQL Server Snapshot Isolation is it possible to determine a row being read is not the latest?

I understand that starting a transaction under the SNAPSHOT isolation level means that any subsequent SELECT statement will provide data as it was when the transaction was started. The following ...
Dan Def's user avatar
  • 163
2 votes
2 answers
523 views

Trying to understand an RCSI example-The Potential Dangers of the Read Committed Snapshot Isolation Level

There's this article The Potential Dangers of the Read Committed Snapshot Isolation Level which demonstrates RC vs RCSI isolation levels. I get the RC example but not the RCSI one.In particular how ...
microwth's user avatar
  • 163
1 vote
1 answer
474 views

intra-query parallel thread deadlocks with only Page Locks with snapshot isolation and change tracking

I've been trying to optimise a data loading process. This is on Sqlserver 2016 (13.0.5850.14 - latest SP&CU). I've switched to change tracking for internal database changes and set tablocks on all ...
Brett's user avatar
  • 111
14 votes
2 answers
2k views

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] ...
Pavel Zv's user avatar
  • 183
3 votes
1 answer
174 views

Can you create a primary xml index in the CREATE TABLE statement (SQL Server)?

I'm creating a temporary table and would like to create a primary and secondary XML index on a contained xml column within the CREATE TABLE statement since that is the only way I can create an index ...
mwigdahl's user avatar
  • 263
1 vote
0 answers
735 views

Avoid update/insert conflict in foreign key using SNAPSHOT isolation

Question is: Is there any way to avoid conflict in SNAPSHOT isolation when setting foreign key to table updated/inserted in another transaction, having in mind I never use delete? I mean something ...
Sylwester Zarębski's user avatar
2 votes
0 answers
405 views

Performance drop after change tracking & isolation snapshot

Several days ago, I enabled change tracking on a db and some of its tables. A few days ago, I also SET ALLOW_SNAPSHOT_ISOLATION ON. (Probably important note: I never SET READ_COMMITTED_SNAPSHOT ON, ...
George Menoutis's user avatar
12 votes
1 answer
2k views

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 ...
joshschreuder's user avatar
6 votes
1 answer
2k views

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?...
Jamie Marshall's user avatar
0 votes
2 answers
3k views

How do writers not block reads in PostgreSql

Since readers do not block writers and writers do not block readers is what MVCC is all about, how does postgresql prevent writers who are in the process of committing their changes to blocks/pages ...
paiakshay998's user avatar
2 votes
1 answer
1k views

InnoDB undo logs vs history list

I was reading about InnoDB undo logs, and in the process, the articles i was reading ended up confusing me on what undo logs are, and how they are different from the history list, or rather the same. ...
giogio's user avatar
  • 23
3 votes
2 answers
940 views

Is it possible to override a ReadCommittedSnapshot isolation level with ReadCommitted isolation level for a particular transaction?

The default isolation level of my database is "read committed snapshot". Please see the below screenshot. I have the table "Tickets' with the following data in my database. Now in connection 1, I ...
Unnikrishnan R's user avatar
5 votes
1 answer
320 views

RCSI and index maintenance

I have a database that is RCSI enabled and I've been rebuilding my indexes online (SQL 2014 Enterprise) for as long as I can remember. My understanding is that if we were to rebuild the indexes ...
nkdbajoe's user avatar
  • 194
0 votes
1 answer
219 views

What happen when a transaction is committed and SNAPSHOT isolation is enabled?

I am actually considering using SNAPSHOT isolation for one WCF service. Before doing so, I would like to know how it works. AFAIK, when it's enabled, updated rows are maintained in tempDB (instead of ...
tigrou's user avatar
  • 123
4 votes
1 answer
555 views

Is it best practice to use surrogate keys when creating foreign key constraints in SQL Server?

I'm trying my best not to ask a leading question, but after a lot of searching I'm unable to find a similar answer or recommendation. First, take a look at this question I asked on Stack Overflow ...
Sam Rueby's user avatar
  • 213
7 votes
1 answer
4k views

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's user avatar
4 votes
1 answer
2k views

Version store blowing up, but responsible sessions have no open transactions

Tempdb is growing and it's all version store data. When I run: select hostname,elapsed_time_seconds,session_id, transaction_id, is_snapshot, blocked, lastwaittype, cpu, physical_io, open_tran, cmd ...
Tony Hinkle's user avatar
  • 7,954
3 votes
1 answer
230 views

Is it acceptable to use deadlocks as a strategy?

We recently upgraded our SQL production environment from 2008R2 to 2017. I am finally at a point where I want to try optimistic locking in the form of using snapshot isolation. I read Brent Ozar's ...
JohnH's user avatar
  • 433
0 votes
2 answers
2k views

Performance problems after setting ALLOW_SNAPSHOT_ISOLATION on

I'm running a 15GB database on SQL Server 12.0.5207. Server is a virtualized Windows Server 2012 R2 with 16GB RAM and 4 CPUs. Yesterday, we set ALLOW_SNAPSHOT_ISOLATION to ON, to check if it would ...
Davide Gironi's user avatar
4 votes
1 answer
2k views

Snapshot isolation transaction failed in database 'tempdb'

Upgraded from SQL Server 2008 R2 to SQL Server 2017. In SS 2008 R2 the application would occasionally get this error: “Snapshot isolation transaction failed in database 'tempdb' because the ...
Jack Corbett's user avatar
8 votes
2 answers
2k views

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 ...
Tom Pažourek's user avatar
6 votes
1 answer
3k views

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 ...
Tom Pažourek's user avatar
1 vote
1 answer
704 views

SELECT blocks UPDATE in Snapshot Isolation

We have a database running on SQL Server 2016 SP1 Enterprise and on which Allow Snapshot Isolation is enabled. In this scenario readers do not block writers and writers do not block readers. Today we ...
Frederik Vanderhaegen's user avatar
7 votes
1 answer
275 views

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 ...
SQL_NoExpert's user avatar
  • 1,061
6 votes
2 answers
3k views

ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON takes forever

One of vendor software needed to install on a test server for POC. It takes forever when the setup.exe is trying to alter one of the databases with SET ALLOW_SNAPSHOT_ISOLATION ON. The test server ...
donger's user avatar
  • 128
2 votes
1 answer
948 views

Read Committed Snapshop Isolation Level - Effect of NOLOCK

As I understand it, with Read Committed Snapshot, readers don't block writers and writers don't block readers. The only blocking that can occur is writers blocking writers. By default our database is ...
Randy Minder's user avatar
  • 2,002
1 vote
1 answer
267 views

Snapshot and RCSI.When does SQL Server go to VersionStore?

When SQL Server reads data with SNAPSHOT or RCSI on, how does it know it has to look a record up in the version store vs the table? Let’s pretend session A modifies a few records of a table and ...
Borka's user avatar
  • 11
2 votes
2 answers
1k views

Snapshot Isolation vs Read Committed - OLTP and Reporting Databases

I just finished reading an excellent article on isolation levels here. Our company will soon start development on a rewrite and expansion of our current product. My desire is to have an OLTP database ...
Randy Minder's user avatar
  • 2,002
13 votes
5 answers
20k views

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 ...
Mark's user avatar
  • 899
1 vote
1 answer
172 views

Turning on RCSI on on-prem CRM 2015

We have crm-2015 on-prem, inplace and running and causing lot of deadlocks when someone tries to update anything in batch and same time if multiple users are reading those activities. We are ...
user2040021's user avatar
2 votes
1 answer
788 views

Both READ_COMMITTED_SNAPSHOT ON and ALLOW_SNAPSHOT_ISOLATION ON

I inherited a SQL Server 2012 instance that hosts Hyperion databases. I believe the Hyperion techs configured the system and they have both READ_COMMITTED_SNAPSHOT ON and ALLOW_SNAPSHOT_ISOLATION ON ...
Charles Pyle's user avatar
0 votes
1 answer
1k views

Read Committed Snapshot Isolation blocking during table modification

This is using SQL server 2014 SP2 (it was true in SP1 as well). The database is configured to have Snapshot Isolation on and Is Read Committed Snapshot Isolation is set to true. The processes ...
Yishai's user avatar
  • 369
7 votes
4 answers
9k views

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 ...
knocte's user avatar
  • 251
3 votes
2 answers
2k views

Blocking continues after enabling snapshot isolation

On database hosted on a SQL Server 2012 instance, I have enabled ALLOW_SNAPSHOT_ISOLATION and verified the state as ON using SELECT snapshot_isolation_state_desc,name from sys.databases However, in ...
Akash's user avatar
  • 1,032
3 votes
2 answers
779 views

Force everyone to use snapshot isolation

I have a scenario in which I don't want any readers to block writers. I don't want users using table hints or isolation levels to issue shared locks, which might block writers. Is there a way for ...
processadd's user avatar
3 votes
2 answers
2k views

How do I set the isolation level for SELECT statement?

Using PostgreSQL 9.5 I would like to make sure that when I run a SELECT statement I get consistent results, i.e. if the table is modified while the SELECT is running, I want to get the state that was ...
Golo Roden's user avatar