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 ...


17

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 (...


17

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 attempts to modify a row that has been modified by another transaction that committed after the snapshot transaction began. The reason for the incorrect behaviour in ...


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 ...


14

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 ...


11

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 temp table before the snapshot transaction starts. Almost all DDL is prohibited within a SNAPSHOT transaction. ALTER TABLE and TRUNCATE TABLE are obviously not ...


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 ...


10

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 value? I don't see anything related in the query plan. I'm not sure I understand what's going on with the first point, and I find the difference in behavior ...


8

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 ...


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

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


7

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 ...


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 ...


7

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 ...


6

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 ...


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

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 ...


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

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 ...


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 ...


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

(Converting my comment to an answer. I don't know if this is definitive, but it makes sense to me. If you can prove it wrong, let me know in the comments.) The row versions aren't actually required to complete a write operation; they're only required by readers that come along and need those versions of the rows. If they need them. If tempdb runs out of ...


4

Read committed (especially section 13.2.1) is the default read level in PostgreSQL. This read level will give you a snapshot of what has been committed before your transaction starts. It will allow other transactions to read and write to your table, you just won't be able to see any writes made after the start of your transaction. Does this only apply ...


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 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 ...


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. ...


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