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.
65 questions
2
votes
2
answers
1k
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 ...
- 855
0
votes
1
answer
46
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 ...
- 101
1
vote
2
answers
637
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 ...
- 113
0
votes
1
answer
202
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 ...
2
votes
1
answer
414
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 ...
- 65
1
vote
2
answers
605
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 ...
- 11
3
votes
1
answer
551
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 ...
- 775
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 ...
- 1,219
-1
votes
2
answers
128
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 ...
- 3,489
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 ...
- 899
4
votes
1
answer
465
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 ...
- 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-...
- 41
1
vote
1
answer
108
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 ...
- 45
1
vote
1
answer
115
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 ...
- 165
2
votes
2
answers
559
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 ...
- 163
1
vote
1
answer
550
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 ...
- 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] ...
- 183
3
votes
1
answer
204
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 ...
- 263
1
vote
0
answers
767
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 ...
2
votes
0
answers
432
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, ...
- 447
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 ...
- 173
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?...
- 307
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 ...
- 31
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.
...
- 23
3
votes
2
answers
1k
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 ...
- 199
5
votes
1
answer
346
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 ...
- 194
0
votes
1
answer
242
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 ...
- 123
4
votes
1
answer
570
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 ...
- 223
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?
- 213
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
...
- 7,984
3
votes
1
answer
236
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 ...
- 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 ...
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 ...
- 101
9
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 ...
- 493
7
votes
1
answer
4k
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 ...
- 493
1
vote
1
answer
717
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 ...
- 2,124
7
votes
1
answer
306
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 ...
- 1,063
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 ...
- 128
2
votes
1
answer
992
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 ...
- 2,012
1
vote
1
answer
284
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 ...
- 11
2
votes
2
answers
2k
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 ...
- 2,012
13
votes
5
answers
21k
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 ...
- 899
1
vote
1
answer
174
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 ...
- 217
2
votes
1
answer
841
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 ...
- 21
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 ...
- 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 ...
- 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 ...
- 1,032
3
votes
2
answers
793
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 ...
- 31
3
votes
2
answers
3k
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 ...
- 422
2
votes
1
answer
818
views
Online index maintenance in SQL Server using SNAPSHOT isolation level
I have recently altered our SQL Server 2008 R2 database to enable SNAPSHOT isolation level and made appropriate changes to hibernate to run all transactions in SNAPSHOT mode. I have however noticed ...
- 66