Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [snapshot-isolation]

The tag has no usage guidance.

5
votes
1answer
63 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 ...
0
votes
1answer
29 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 ...
2
votes
0answers
49 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 ...
2
votes
1answer
198 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?
0
votes
0answers
33 views

Moving from universal NOLOCK to Snapshot or RCSI

First off, I am a developer, not a DBA. Our shop uses SQL server with stored procedures for all of our CRUD from our application layer. Before my time, the blanket decision was made to require WITH (...
1
vote
0answers
33 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 ...
3
votes
1answer
192 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 ...
0
votes
0answers
24 views

how can snapshot isolation automatically prevent lost updates?

I'm reading the book Designing Data-Intensive Applications and there's a part of it that I don't really understand (below). The concept of "lost updates" that it's referring to is when two ...
-2
votes
2answers
190 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 ...
3
votes
0answers
143 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 ...
5
votes
1answer
332 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 ...
4
votes
1answer
573 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 ...
1
vote
1answer
122 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 ...
6
votes
1answer
110 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 ...
5
votes
2answers
328 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 ...
0
votes
0answers
108 views

Disconnections in SQL Server

I'm looking for some suggestions regarding a disconnection issue, I'm basically looking for a way to track why the connections are getting dropped that suddenly. The application is using tomcat and ...
0
votes
1answer
204 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 ...
1
vote
1answer
84 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 ...
2
votes
2answers
472 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 ...
3
votes
5answers
4k 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 ...
1
vote
1answer
75 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 ...
2
votes
1answer
295 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 ...
0
votes
1answer
531 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 ...
5
votes
4answers
4k 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 ...
2
votes
2answers
983 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 ...
3
votes
2answers
351 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 ...
2
votes
2answers
783 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 ...
2
votes
1answer
477 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 ...
0
votes
2answers
528 views

sql server transactional - replication - the internal procedures often deadlock with user processes

Very often I have deadlocks on my subscription server, involving the replication internal stored procedures and user processes either ad hoc or procedure queries. For example: Deadlock of the ...
3
votes
1answer
171 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 ...
1
vote
1answer
481 views

READ_COMMITTED_SNAPSHOT locking on Non-Clustered Index

I'm in big trouble with one database instance of SQL Server 2008 R2. I have this structure that I've created it to simulate my real problem: USE [sor] GO ALTER DATABASE [sor] SET ...
5
votes
1answer
965 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://...
10
votes
1answer
3k 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 = ...
11
votes
1answer
2k 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 ...
8
votes
3answers
2k 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 ...
4
votes
1answer
233 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": ...
6
votes
1answer
220 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 ...
2
votes
1answer
83 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 ...
0
votes
0answers
439 views

What are the possible reasons for SQL Server errors on reading operations upon switching ALLOW_SNAPSHOT_ISOLATION` and `READ_COMMITTED_SNAPSHOT ON?

I have a client-server app running in Windows Server 2008R2 using/interacting SQL Server 2012. After reading app's documentation as well as feedbacks on internet from those who already accomplished ...
5
votes
1answer
701 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?
4
votes
1answer
1k 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 ...
2
votes
1answer
270 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 ...
9
votes
1answer
938 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 ...
5
votes
1answer
895 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 ...