Stack Overflow
Stack Overflow

Questions tagged [snapshot-isolation]

53 questions
1
vote
1 answer

Snapshot Isolation Transaction aborted due to update conflict, but no transaction begins

I have encountered an error on executing UPDATE statement. Error: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Companies' directly or indirectly in database 'myDatabase'…
user5466334
1
vote
2 answers

Using Snapshot Isolation level in a SQL Server View

After doing some research, I can't really find a way to use a Snapshot Isolation level in a SQL Server view. Is there some way to achieve it, or if it is indeed impossible, what is the best way to approach a situation, when I'd like my views to not…
Alex Shelemin
  • 3,157
  • 1
  • 16
  • 16
1
vote
3 answers

SQL Server 2012 unable to turn off Snapshot Isolation

Using the code below I'm unable to turn off snapshot isolation. I'm using a SQL Server 2012 box. I can create brand new empty db, turn snapshot isolation on, but I can't turn it back off. The "allow_snapshot_isolation OFF" line just spins it's…
user3461662
  • 11
  • 1
  • 2
1
vote
1 answer

Relation between Paxos and Snapshot Isolation for concurrency control

I was wondering what is the actual relationship between Paxos based replication schemes and different concurrency models like snapshot isolations. Could anybody kindly able to explain these two in regards to their relationship and with few practical…
Joarder Kamal
  • 1,387
  • 1
  • 20
  • 28
1
vote
2 answers

Shared lock in READ_COMMITTED_SNAPSHOT and SNAPSHOT isolation

I've read on Microsoft's site http://msdn.microsoft.com/en-us/library/ms173763.aspx that Sql Server doesn't request locks when reading data, except when a database is being recovered. Does it mean that Sql Server using …
Marka
  • 377
  • 1
  • 4
  • 17
1
vote
2 answers

How are clustered indexes updated when Snapshot Isolation is ON in SQL Server?

I have a SQL Server 2008 database with SET ALLOW_SNAPSHOT_ISOLATION ON and a Person table with columns ID (primary key), and SSN (unique non-clustered index). One of the rows in the database is ID = 1, SSN = 776-56-4453. One one connection, this…
Raghu Dodda
  • 1,505
  • 1
  • 21
  • 28
0
votes
1 answer

Why update transaction block delete operation in snapshot isolation sql server database?

I have 2 tables in my database. They are Item and Field. Item is a self referenced table to implement a tree. That means in Item table, I have ID and ParentId columns. In this way, we can store a tree in the Item table. Field is a table to store…
Coofucoo
  • 119
  • 7
0
votes
0 answers

Write skew in a snapshot isolation level

Let's consider a scenario of a write skew below. In an MVCC protocol for snapshot isolation, let's assume the initial state is this: Item Version Value on_call 3 [Alice, Bob] Then transition TA started and given a unique timestamp of 5…
Sankit Acharya
  • 45
  • 2
  • 11
0
votes
1 answer

Snapshot isolation transaction aborted due to update conflict in SQL Server when removing rows

I now have a simple example of this issue following on from: Snapshot isolation transaction aborted due to update conflict in SQL Server This is the script to create the database tables: CREATE TABLE [dbo].[tblPPObjectChildObjectList]( …
Sputnik
  • 39
  • 4
0
votes
2 answers

With TSQL SNAPSHOT ISOLATION, is it possible to update only non-locked rows?

A large SQL Server 2008 table is normally being updated in (relatively) small chunks using a SNAPSHOT ISOLATION transaction. Snapshot works very well for those updates since the chunks never overlap. These updates aren't a single long running…
Djof
  • 603
  • 1
  • 7
  • 20
0
votes
1 answer

Why check TRANCOUNT before setting TRANSACTION ISOLATION LEVEL SNAPSHOT

When using snapshot isolation, why do I often see IF @@TRANCOUNT = 0 before setting the transaction level? I.e., in a stored proc: IF @@TRANCOUNT = 0 BEGIN SET TRANSACTION ISOLATION LEVEL SNAPSHOT; END
Hoppe
  • 6,508
  • 17
  • 60
  • 114
0
votes
1 answer

Insert if not exist under RCSI

I have a database with READ_COMMITTED_SNAPSHOT_ISOLATION set ON (cannot change that). I insert new rows into a table on many parallel sessions, but only if they don't exist there yet (classic left join check). The inserting code looks like…
Avi
  • 1,066
  • 1
  • 15
  • 37
0
votes
0 answers

Avoid deadlock among data-read in SQL Server

We have an issue with "deadlocks" in SQL Server, where there are no explicit locks involved and would like to know, how to get around them. Some relevant background info: Our application is quite old and large. We recently began to remove some…
Jonathan
  • 2,698
  • 24
  • 37
0
votes
0 answers

Using database snapshot vs snapshot issolation level transaction

I maintain an an MVC application which incorporates some long running batch processes for sending newsletters, generating reports etc. I had previously encountered a lot of issues with deadlocks, where one of these long running queries might be…
user1751825
  • 4,029
  • 1
  • 28
  • 58
0
votes
1 answer

How does SNAPSHOT isolation read snapshot data for tempdb?

I'm trying to understand how SNAPSHOT isolation pulls data into tempdb. I understand that there is transactional consistency due to row versioning in tempdb, but I'm more curious about how that data is copied into tempdb in the first place. The…
Chris H
  • 76
  • 1
  • 9
1 2
3
4