21
votes
Accepted
Why am I getting "Snapshot isolation transaction aborted due to update conflict"?
When deleting from the parent table, SQL Server must check for the existence of any FK child rows that refer to that row. When there is no suitable child index, this check performs a full scan of the ...
- 90.3k
18
votes
Accepted
Unclear update conflict
Why do I get update conflict in this situation instead of just blocking
It is a product defect, which is fixed in SQL Server 2019.
A snapshot write conflict occurs when a snapshot transaction ...
- 90.3k
14
votes
Accepted
Is it possible to set DEFAULT TRANSACTION ISOLATION for all connections in SQL Server?
It is not possible to configure Snapshot Isolation (SI) as the default isolation level.
To use SI, the database must be enabled for Snapshot Isolation:
ALTER DATABASE CURRENT
SET ...
- 90.3k
13
votes
Accepted
Is it possible to use Read Uncommitted isolation level on read-only Availability Group secondary?
bzzt
Read Uncommitted/NOLOCK doesn't mean your queries don't take locks, it means your queries ignore locks taken by other queries. The hints really should have been called NORESPECT instead. The ...
11
votes
Accepted
Why is it not possible to create indexes on temp tables in SNAPSHOT isolation?
In modern versions of SQL Server (2014+) you can create the indexes when you create the table, e.g.:
create table #t(id int primary key, a int, index ix_a nonclustered(a))
Also you can create the ...
- 47.6k
10
votes
Unclear update conflict
And the second theoretical question:
How does SQL Server handle include columns update?
I mean how does SQL Server update all nonclustered index which have an include columns when we update this ...
- 29.8k
9
votes
Accepted
Snapshot Isolation vs Read Committed - OLTP and Reporting Databases
Just to add to the other answer.
SQL Server supports two different flavors of READ COMMITTED, legacy locking READ COMMITTED and READ COMMITTED SNAPSHOT. If you've ever built and supported a high-...
- 47.6k
8
votes
Accepted
RCSI and index maintenance
However as long as we continue to rebuild online or reorganize we will retain the 14 bytes per row. Is that correct?
This is true. REBUILD WITH (ONLINE = ON) and REORGANIZE will leave the 14 bytes ...
- 29.8k
8
votes
Accepted
Why am I getting a snapshot isolation issue on INSERT?
Add an OPTION (LOOP JOIN) hint to the INSERT statement.
Or use a plan guide (or query store) to force the nested loops semi join plan shape.
You might find that OPTION (FAST 1) works as well.
The ...
- 90.3k
7
votes
Accepted
Setting READ_COMMITTED_SNAPSHOT to ON while ALLOW_SNAPSHOT_ISOLATION is OFF
I noticed that when enabling this, many queries got a lot faster. I'm still using the default READ COMMITTED isolation level to connect to the database.
Dan noted this:
The options are ...
- 266
7
votes
Accepted
Snapshot_isolation_state_desc on a Read only/Stand by Database
SQL Server always seems to set snapshot isolation (SI) on when a database is made read only. I have no idea why it does this, but it does.
You can try to turn it off, and the ALTER DATABASE statement ...
- 90.3k
6
votes
ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON takes forever
I ran into this exact situation and can confirm there was no blocking in my case as the setup basically altered the snapshot isolation immediately after creating the database.
In my case the wait ...
- 15.7k
6
votes
Read Committed Snapshop Isolation Level - Effect of NOLOCK
If I understand correctly, a query that uses WITH (NOLOCK) changes the
isolation level. When this is used in a query, is it now possible for
a reader to block a process attempting to update the ...
- 11.2k
6
votes
Accepted
Why does the SERIALIZABLE transaction isolation level not guarantee point-in-time views of data?
People always seem to want to understand isolation levels in terms of locks.
This is somewhat strange because isolation levels are generally not defined by locking, rather by concurrency phenomena* ...
- 90.3k
5
votes
Accepted
Is it possible to override a ReadCommittedSnapshot isolation level with ReadCommitted isolation level for a particular transaction?
There is no way to override the database-level setting of read committed snapshot (RCSI) for a whole transaction.
When RCSI is set, all read committed transactions will use row versions, delivering ...
- 90.3k
5
votes
Accepted
How do writers not block reads in PostgreSql
That's the whole idea of MVCC - Multi-Version-Concurrency-Control.
Whenever data is being modified while there are other active transactions reading it, or have read it previously (depending on the ...
- 4,098
5
votes
Snapshot Isolation vs Read Committed - OLTP and Reporting Databases
I wouldn't assume that snapshot or READ COMMITTED SNAPSHOT necessarily will increase your overhead that might lead to performance degradation -- the reduced wait times may increase throughput.
The ...
- 1,324
5
votes
Snapshot isolation transaction aborted due to update conflict when removing rows from the database
The delete execution plans show merge joins with full scans so all rows are touched. The culprit is the table variable in the dbo.spPurge procedure:
declare @RedundantInsertionIDs table (InsertionID ...
- 28.5k
5
votes
Accepted
The finer points of writers locking each other out under the snapshot isolation levels
General rules about locks and lock escalation:
Row level locks can get escalated to Table level (or Partition level) locks. So, it is possible for unrelated rows to be locked, under certain ...
4
votes
Why am I getting "Snapshot isolation transaction aborted due to update conflict"?
I came across this reply by a guy at Microsoft on a thread asking a similar question, and, I thought it was quite insightful:
Without a supporting index on CustomerContactPerson, the statement
DELETE ...
- 899
4
votes
How do writers not block reads in PostgreSql
Now what I find difficult is, say while the actual commit process
occurs for T1, say T2 reads (while the write by T1 has started but not
ended). How is this case handled?
It doesn't have to be ...
- 32.3k
4
votes
Is it acceptable to use deadlocks as a strategy?
Snapshot and Read Committed Snapshot isolation levels do not prevent deadlocks from happening. They make the engine using row versioning, so readers do not block writers and writers do not block ...
- 9,030
4
votes
Accepted
Trying to understand an RCSI example-The Potential Dangers of the Read Committed Snapshot Isolation Level
Under locking read committed it's very likely (but not guaranteed) that when two sessions run this code
BEGIN TRANSACTION
DECLARE @QtyRequired int, @QtyRemain int
SELECT @QtyRequired = 4
SELECT @...
- 47.6k
4
votes
Accepted
How does the read committed snapshot isolation level relate (link) the row in tempdb to the original row?
SQL Server adds this 14 byte overhead as internal information. You need to use special command to see the overhead, as noted by MBuschi.
Note that the overhead is not added until you modify a row, ...
- 18k
4
votes
Understanding of SQL Server version store
"Before the user add the product to his shopping cart...If the user knows the new price for the product, he would not buy it." - These are things that happen on the application side and are ...
- 39.5k
4
votes
Accepted
Understanding of SQL Server version store
The issue you are referring to is known as write skew, and happens when optimistic concurrency is used to read and write data.
Yes, this is absolutely possible under SNAPSHOT, because the versioned ...
- 14.5k
3
votes
Why is it not possible to create indexes on temp tables in SNAPSHOT isolation?
This is still not supported as of SQL Server 2019, but there is an open feedback forum issue for this that is worth up-voting to allow CREATE INDEX on temp tables to be allow-listed as david-browne-...
- 131
3
votes
Truncate Table locking sys views
Locking for truncate and delete is different. As for locking TRUNCATE behave more like 'ALTER TABLE' than DELETE.
From Microsoft documentation:
TRUNCATE TABLE (Transact-SQL)
"When the DELETE ...
- 1,591
3
votes
Accepted
Can you create a primary xml index in the CREATE TABLE statement (SQL Server)?
I just tried using the XML index creation commands in-lined with a table creation, and it didn't work.
I stumbled upon this article that makes it look like trying to create non-relational indexes (XML,...
- 379
3
votes
RCSI & ETL processing compatibility
With Snapshot or RCSI isolation you get optimistic locking model where Readers don't block Writers and Writers don't block Readers. Beware that with optimistic locking query results for Readers may ...
- 5,964
Only top scored, non community-wiki answers of a minimum length are eligible