19

The list of 'DDL' operations listed is not comprehensive (and TRUNCATE TABLE is not the only omission from that list). Whether TRUNCATE TABLE is DML or DDL is a fraught question in SQL Server, with persuasive examples on both sides of the debate, and entries both ways in Books Online. From the point of view of a snapshot isolation transaction, truncate has ...


14

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 child table: If the scan encounters a row that has been modified since the delete command's snapshot transaction started, it will fail with an update conflict (...


14

A connection from the pool will have the isolation level set by the last client to use that connection. Yes, it really is that scary. The long and the short of it is that if you change the isolation level of a connection you must explicitly set it back to READ COMMITTED before closing. Better is to explicitly declare your required isolation level at the ...


12

How can I be seeing shared locks? Is it because of foreign keys? Yes. SQL Server reverts to the locking implementation of the read committed isolation level when accessing a table for the purpose of validating foreign key constraints. This is required for correctness, and cannot be disabled. The behaviour applies only to data-modification statements. ...


11

In case of multiple updates of the same record in a single transaction, how many versions are stored? Just one. The first update to the row generates a row version and exclusively locks the row. Later updates to the same row within the same transaction do not generate new row versions. A linked list of row versions can arise as follows: Transaction T1 in ...


10

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 ALLOW_SNAPSHOT_ISOLATION ON; Then each connection must explicitly request SI, using for example: SET TRANSACTION ISOLATION LEVEL SNAPSHOT; It is possible to set the default ...


9

In modern versions of SQL Server (2014+) you can create the indexes when you create the table. EG create table #t(id int primary key, a int, index ix_a nonclustered(a)) Also you can create the temp table before the snapshot transaction starts.


8

The exact behaviour of concurrent modifications depends, in part, on the access path chosen by SQL Server to locate records to change. If SQL Server uses the clustered index to locate data to change, locks will generally be taken on clustered index keys (and/or pages, etc.). If SQL Server locates rows to change using a nonclustered index, locks will be ...


8

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-volume OLTP application on locking READ COMMITTED you know why RCSI was introduced (in addition to making it easy to port Oracle applications to SQL Server). ...


8

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 as-is. A normal REBUILD will clear it. If you disable RCSI, the 14 byte version tag will be cleared by any of the three operations you mentioned: REBUILD ...


7

If your intention is to avoid readers from blocking writers and visa-versa in the default READ_COMMITTED isolation level, turn on the READ_COMMITTED_SNAPSHOT database option. This will cause row versioning instead of locking to be used to implement statement-level read consistency. Although often confused, the ALLOW_SNAPSHOT_ISOLATION option is not related ...


7

Yes RCSI is supported for use with distributed transactions as documented in Books Online: Choosing Row Versioning-based Isolation Levels


7

...will it block for a period of time proportional to the record count, or will the data be updated asynchronously. Neither. The additional 14 bytes are used to store two elements: The transaction sequence number that modified the row. A pointer to the previous version of the row in the version store (in tempdb) if one exists. These are only added to ...


7

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 will succeed, but snapshot isolation remains enabled while the database is read only. One theory is that setting SI prevents unexpected errors for people ...


6

Knowing this, why does sql server need to issue U locks (when using RCSI)? It seems to me that sql server could simply read the rows, and request a X lock directly if an update must be performed. Unlike SI, RCSI does not detect update conflicts. As documented in Books Online, modifying data under RCSI reads currently-committed data, not a possibly out-of ...


6

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 type was ENABLE_VERSIONING. When investigating I found this feedback item Enabling snapshot isolation in 2016 SP1 CU6 and CU7 hangs which is still under ...


5

Enabling ALLOW_SNAPSHOT_ISOLATION does not alter the behaviour of your code. Instead it lays the groundwork for you to be able to use row versioning if you wish. Once snapshot isolation has been applied to the database a query can succesfully use row versioning if the following statement is used: SET TRANSACTION ISOLATION LEVEL SNAPSHOT This post by Kendra ...


5

First of all, categorizing the backup into the four mentioned categories is not accurate. You can categorize into (hot, warm, cold), and into (physical, logical). In other words, the backup can be cold and physical, or, hot and physical, etc. It cannot be (physical and logical). Notice please that physical backup is better called "Raw backup", which is a ...


5

Isn't it self-contradictory paragraph ("until" vs. "retained")? Not to me, but I can see there are other ways to read it. If you want the documentation updated to make it clearer, the proper place to request this is on Microsoft Connect. The people here do not maintain Microsoft documentation. that the default isolation level will have arbitrary value (...


5

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 the last committed version of a row. A session-level override option has been asked for. You can vote for it on the product feedback site at Add SET TRANSACTION ...


5

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 point is to avoid a merge semi join, where many (potentially all) of the referenced tables' rows are touched by the current transaction. If any parent row with a ...


4

You don't need to do anything to get statement level consistency A query always sees a consistent state of the database regardless of the isolation level you use. Quote from the manual: This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. See ...


4

Read committed snapshot isolation (statement-level snapshot isolation) potentially uses less storage than snapshot isolation (transaction-level snapshot isolation) because usually the row versions aren't required for as long a period of time after they've been generated. For transaction-level snapshot isolation, the versions have to stick around for the ...


4

From here, the complete command for a hot backup of a MySQL database using only InnoDB tables is: mysqldump -u user -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql Note the gap between -u and user and the lack of a gap between -p and pass. This is correct - a quirk of MySQL which has caught me on more than one occasion. ...


4

There are doubtless many blogs that discuss this, but this post by Kendra Little (at BrentOzar.com) discussed the issues that concern you. See: http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/ Kendra discusses some of the problems that arise and how you can test for potential ...


4

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 danger with RCS and SNAPSHOT has more to do with how the app is coded -- if your programmers assume the blocking semantics of READ COMMITTED when the queries ...


4

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 data being read? No, you think wrong. Both in RCSI and Read Uncommitted readers don't block writers. The mechanism is different: RCSI uses last committed copy ...


4

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 independent and both do not need to be turned on, although many mistakenly do so. Turning on either incurs the row-versioning overhead. READ_COMMITTED_SNAPSHOT ON causes ...


4

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 readers as SQLServer does by default. That improves concurrency , but requires changes to the applications that rely on "readers block writers and writers block ...


3

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 FROM ContactPerson WHERE ID = @ID; Will require a "current" read of all the rows in CustomerContactPerson to ensure that there are no ...


Only top voted, non community-wiki answers of a minimum length are eligible