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
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
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
12
votes
1
answer
6k
views
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 ...
- 1,392
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
10
votes
3
answers
4k
views
"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 ...
- 1,392
10
votes
1
answer
4k
views
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 = ...
- 2,191
9
votes
1
answer
2k
views
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 ...
- 477
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
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
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
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
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
7
votes
1
answer
478
views
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 ...
- 1,392
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
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
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
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
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
5
votes
1
answer
2k
views
Locking in "Read Committed Snapshot"
If an update is run on a table with "Read Committed Snapshot" isolation level and a commit is pending
eg:
update table1 set col1 = col1 + 1 where PKcol < 3
update table1 set col1 = col1 + 1 ...
- 3,131
5
votes
1
answer
1k
views
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 http://...
- 546
5
votes
1
answer
949
views
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?
- 153
5
votes
1
answer
1k
views
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 ...
- 5,373
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
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
4
votes
1
answer
364
views
Why do row-versioning isolations use a linked list of versions of record? [closed]
Trying to better understand row-versioning based transaction isolations in SQL Server...
According to Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation":
...
- 1,392
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
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
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
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
3
votes
1
answer
552
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
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
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
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
3
votes
1
answer
254
views
enabling snapshot isolation level on a database - what to monitor?
I am having problems with frequent deadlocks on a subscription database.
I am considering enabling the snapshot isolation level for that database.
I am aware that there is an overhead on the tempDb ...
- 16.6k
3
votes
1
answer
206
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
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
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
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 ...
- 853
2
votes
1
answer
431
views
ALLOW_SNAPSHOT_ISOLATION Duration
I would like to enable ALLOW_SNAPSHOT_ISOLATION on a production database that has ~400 million total records. I understand that 14 bytes will need to be added to each record.
If I set ...
- 785
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
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
2
votes
1
answer
125
views
Why - read but not update ops fail in case of lacking space in tempdb database?
MSDN online article "Choosing Row Versioning-based Isolation Levels" states:
"tempdb must have enough disk space for the version store. If there are very long-running transactions, all the versions ...
- 1,392
2
votes
1
answer
416
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
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
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
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
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
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
2
answers
639
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
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