Suppose that I have a database with Read Committed Snapshot Isolation already enabled. Is there any reason at all to not also enable Snapshot Isolation? Intuitively, you would think that the row versions would be kept around from longer. [The documentation dismisses this](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver17#behavior-when-reading-data) . > Even though READ COMMITTED transactions using row versioning provides a transactionally consistent view of the data at a statement level, row versions generated or accessed by this type of transaction are maintained until the transaction completes. So I am left without any ideas. Assume SQL Server 2022. SQL Server 2025 brought with it Optimized Locking, which creates just enough uncertainity in my mind that I don't want to ask about it here.

We've all read [the documentation for Snapshot isolation](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver16#modify-data-without-optimized-locking) and know about Update Conflict Detection and when you should _theoretically_ use Snapshot isolation for writes. However, I have never found anyone who does their writes under Snapshot isolation. Paul White has [a post on how it can go wrong](https://www.sql.kiwi/2014/06/the-snapshot-isolation-level/) , but I have never seen anyone discuss what it looks like when it goes _right_. I haven't found it in any textbooks, blogs, or production servers. **How can I design an experiment to show the benefits of writing while under Snapshot isolation?** I am particularly interested in comparing write performance. However, what should I vary to test when writing under Snapshot is a good idea? Furthermore, what is a fair comparison to it? Read Committed, RCSI, or something more extreme like Serializable? I am **not** asking about Read Committed Snapshot or [using Snapshot for reads](https://dba.stackexchange.com/questions/346376/why-not-use-snapshot-isolation-for-everything-read-only)· ; They're both awesome.

I am currently working with SQL Server and exploring transaction management, specifically the dm_tran_commit_table view. I have a couple of questions regarding the commit_time and change tracking. Database has Delay Durability = Forced and Is Read Committed Snapshot = true 1. Does the commit_time represent the exact moment a record is inserted into a table or inserted to change tracking table, or does it indicate the time the transaction was committed, which may be different? 2. If the transaction is committed, what timezone does the commit_time reference? Is it based on the server’s local time, UTC, or another timezone? The reason I am asking is that I have a creation date in my system from which we are replicating data, and I noticed that the date from the system is about 25 minutes earlier than the date taken from commit_time. According to the statistics I checked, there should be a maximum latency of about 5 minutes. This discrepancy is concerning, and I’d like to understand if the commit_time could be contributing to this issue. I would greatly appreciate any insights or references to documentation that can clarify these points. Thank you for your assistance!

[The documentation](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver16#update) points out that you can use UPDLOCK hints while running a transaction under the SNAPSHOT isolation level. [This more obscure documentation](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server?redirectedfrom=MSDN#using-lock-hints-with-snapshot-isolation) shows an example. However, I cannot think of any use case! This is my question: Why would you ever mix the SNAPSHOT isolation level with an UPDLOCK? The two big alternatives that come to my mind are simply using a pessimistic isolation level or using the READCOMMITTEDLOCK hint.

Suppose that: - I already have Snapshot isolation enabled - I do not have Read Committed Snapshot Isolation enabled - I have been strict with my READCOMMITTEDLOCK hints wherever I truly need them then is there any reason at all to not use the Snapshot isolation level **for all read-only queries?** It seems that all of the costs from Snapshot isolation are paid at the moment that you enable the database-level setting rather than when you run queries under it.

On SQL Server 2019 with ADR disabled:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
WAITFOR DELAY '00:00:20'; -- insert of one record to dbo.MARA_CT in different session
SELECT
    *
FROM dbo.MARA_CT AS mc;
WAITFOR DELAY '00:00:20'; -- insert of one record to dbo.Album in different session
SELECT
    *
FROM dbo.Album AS a;
WAITFOR DELAY '00:00:20';
SELECT
    *
FROM dbo.MARA_CT AS mc;
SELECT
    *
FROM dbo.Album AS a;
COMMIT;

I inserted one row into dbo.MARA_CT and during the second WAITFOR DELAY, I inserted a record into dbo.Album. According to Microsoft documentation: > The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. The results from my query: 1. dbo.MARA_CT **contains** the record inserted during the first WAITFOR DELAY '00:00:20'. 2. dbo.Album **does not contain** the record inserted during the second WAITFOR DELAY '00:00:20'. Does the first touch of any table in the query create a "snapshot" for the entire transaction rather than capturing the state of the database at the moment the transaction begins? Does SNAPSHOT ISOLATION store all versions from the beginning of the transaction for all rows in the database, or only for the tables used in the query? How does this work? If RCSI is enabled and we subsequently enable SNAPSHOT ISOLATION, does SQL Server store any new values/versions in tempdb? My use of SNAPSHOT ISOLATION is to test new versions of procedures and views (new schemas). Both the new and old procedures need to return the same number of records and utilize the same data, even as the tables involved experience constant inserts, updates, and deletes. Would SNAPSHOT ISOLATION be more advantageous than a database snapshot since RCSI is already enabled on the databases?

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 help prevent some deadlocks we have seen. Performance decreased a LOT! Lock waits per seconds increased, and CPU went from 25% to 50% usage. We decided to set ALLOW_SNAPSHOT_ISOLATION back to OFF. The problem is this had **no effect**; we still have decreased performance. We restarted the SQL Server and related services, but it did not resolve the issue. Any suggestions? Indexes have been rebuilt using Ola Hallengren's MaintenanceSolution.sql script we run weekly. This is the execution command: EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30;

Suppose that I have an Enterprise Edition database that has completely default settings and nothing but normal rowstore tables. No RCSI, no snapshot isolation and no triggers. Assume nothing that would require [row versioning](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver16#space-used-in-data-rows) . Clearly, these assumptions mean that I will not be paying the extra 14-bytes-per-row cost of row versioning. However, readable AG secondaries run under SNAPSHOT isolation and SNAPSHOT isolation requires row versioning. So if I put my database in an Availability Group and add a readable secondary, what copies of my database (if any) will start paying the 14-bytes-per-row cost of row versioning? Does this change if I have a non-readable secondary in addition to the readable secondary? What if I fail over and fail back? The relevant documentation, linked earlier, makes no comment on this. I plan to run experiments myself, but AGs are notoriously easy to make mistakes with and I don't want to draw incorrect conclusions.

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 that when I add/delete/alter indexes, the queries which are accessing the underlying table are rolled back and I get this exception: > Snapshot isolation transaction failed in database 'foo' because the > object accessed by the statement has been modified by a DDL statement > in another concurrent transaction since the start of this transaction. > It is disallowed because the metadata is not versioned. A concurrent > update to metadata can lead to inconsistency if mixed with snapshot > isolation ...which is exactly what has happened. According to https://msdn.microsoft.com/en-us/library/bb933783(v=sql.105).aspx "These statements are permitted when you are using snapshot isolation within implicit transactions." So I tried: set implicit_transactions on GO DROP INDEX blabla GO IF @@TRANCOUNT > 0 COMMIT TRAN set implicit_transactions off But the problem still persists. There must be a way to do this in SQL Server?

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 and a separate, more denormalized, reporting database. Assuming we're somewhat disciplined and most of our ad-hoc and reporting type queries actually go to the reporting database, does it sound appropriate that our OLTP database have a default isolation level of Read Committed (we won't need a more stringent isolation level for OLTP) and our reporting database be Snapshot Isolation (probably RCSI)? My thinking is that if our OLTP database is actually a true OLTP database and not serving double-duty as a reporting DB, we won't need snapshot isolation, and the associated overhead it entails. But snapshot isolation would be desirable on the reporting database so that readers are not blocked by the constant flow of data coming in, and reading the last saved version of a row would be acceptable.

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 valid when the SELECT started. Basically, it's like: Take a snapshot of a table, and don't allow updates to the table while the snapshot is being taken, and then deliver the snapshot, while updates are allowed again. If I understood isolation levels correctly, REPEATABLE READ is what I'm looking for, isn't it? Now my question is: How can I run a SELECT with REPEATABLE READ isolation level? Or am I missing something, and my approach is wrong? How would I solve this?

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 object accessed by the statement has been modified by a DDL > statement in another concurrent transaction since the start of this > transaction. It is disallowed because the metadata is not versioned. > A concurrent update to metadata can lead to inconsistency if mixed > with snapshot isolation.” In SS 2017 the error happens much more often. The transaction is being started in .NET specifically setting the Isolation Level to snapshot and it runs a single stored procedure within the transaction using ExecuteNonQuery(); so the only thing going on from the .NET side is the procedure call. I'm just flummoxed as to why the same code is producing many more errors in SS 2017 vs. SS 2008 R2. There are some temp tables created in the procedure using SELECT INTO #Table. I have recommended that this be changed to explicitly create the temp tables and then insert into them. I also don't believe that snapshot isolation is necessary in this case and was supposedly added to help avoid deadlocks. I have recommended using READ COMMITTED SNAPSHOT as I think that will solve the problem, but it requires more testing and changing the .NET code to remove the explicit use of snapshot isolation. The procedure can be run 100's of times per minute. I'm open to suggestions. I can't share the exact code but could post a simple mock up potentially. How do I stop the error? Example .NET code:

-c#
using (SqlConnection conn = Timeco.ConnectionMgr.GetConnection()) 
{ 
conn.Open(); 
{ 
	using (SqlTransaction transaction = conn.BeginTransaction(IsolationLevel.Snapshot)) 
	{ 
		using (SqlCommand cmd = new SqlCommand("proc_name", conn, transaction)) 
		{ 
			cmd.CommandTimeout = 120; // avoid timeouts on save 
			cmd.CommandType = CommandType.StoredProcedure; 

			SqlParameter param1 = cmd.Parameters.AddWithValue("@param_name", param_value]); 
			param1.SqlDbType = SqlDbType.Structured; 

			// adds a few more parameters
			cmd.Parameters.Add("@param_anem", SqlDbType.UniqueIdentifier).Value = param_value; 

			cmd.ExecuteNonQuery(); 
		} 
		
		transaction.Commit(); 
	}
}
}

I have been told that if your transaction consists of multiple statements, then the SNAPSHOT transaction isolation level is the only way to guarantee consistent point-in-time views of data. Why is the SERIALIZABLE transaction isolation level inadequate for this task? By design, the locks that SERIALIZABLE holds are very tight. I think the gap in my understanding is that I am unsure when SERIALIZABLE takes its very tight locks. A script like the below will likely be very helpful in finding what I am missing.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

SELECT TOP (1) * FROM [Hit_Me_First];

WAITFOR DELAY '00:02:00';  

SELECT TOP (1) * FROM [Hit_Me_Second];

COMMIT;

I'm trying to figure out a better intuition for what exactly are the sorts of consistency anomalies that snapshot isolation allows to happen. The description on Wikipedia says: > In databases, and transaction processing (transaction management), snapshot isolation is a guarantee that all reads made in a transaction will see a consistent snapshot of the database (in practice it reads the last committed values that existed at the time it started), and the transaction itself will successfully commit only if no updates it has made conflict with any concurrent updates made since that snapshot. > When the transaction concludes, it will successfully commit only if the values updated by the transaction have not been changed externally since the snapshot was taken. Such a write–write conflict will cause the transaction to abort. So my reasoning goes: - If two transactions have interleaved reads, that alone cannot violate serializability, because if nobody is changing the data then the end result will be the same regardless of what order the reads occur. - If two transactions have interleaved writes, then snapshot isolation will abort and retry at least one of them until it doesn't have interleaved writes, thus preventing that. - So then it seems like the only way that snapshot isolation could allow serializability to be violated would be interleaving writes with reads. So then I try to imagine a simple case where there's two transactions and three disjoint sets of records, kind of like a Venn diagram, but where there is only interleaving between tx 1's writes and tx 2's reads, not the other way around: - Set of records A is written by tx 1 - Set of records B is written by tx 1 and read by tx 2 - Set of records C is written by tx 2 And it seems like in this situation it's still not possible for snapshot isolation to violate serializability. Because I figure: - If tx 2 is considered to start before tx 1 commits, it will only read the versions of records in B from before tx 1 changed them, and then also tx 2 will not change any records in B because it is only reading them, so the execution will be serializable to the order [tx 2, tx 1]. - If not, then tx 2 is considered to start after tx 1 commits, in which case it seems obvious that the execution will be serializable to the order [tx 1, tx 2]. Since it seems like violating serializability with snapshot isolation in this case is still impossible, that makes it seem like snapshot isolation can only violate serializability if there exists _both_: - Some records which are written by tx 1 and read by tx 2 - Some records which are written by tx 2 and read by tx 1 Or, alternatively, some more indirect loop, e.g. tx 1 writes A and reads B, tx 2 writes B and reads C, and tx 3 writes C and reads A. And it seems conspicuous that the common example I see of an anomaly which snapshot isolation allows is exactly such a case: > As a concrete example, imagine V1 and V2 are two balances held by a single person, Phil. The bank will allow either V1 or V2 to run a deficit, provided the total held in both is never negative (i.e. V1 + V2 ≥ 0). Both balances are currently $100. Phil initiates two transactions concurrently, T1 withdrawing $200 from V1, and T2 withdrawing $200 from V2. > > If the database guaranteed serializable transactions, the simplest way of coding T1 is to deduct $200 from V1, and then verify that V1 + V2 ≥ 0 still holds, aborting if not. T2 similarly deducts $200 from V2 and then verifies V1 + V2 ≥ 0. Since the transactions must serialize, either T1 happens first, leaving V1 = −$100, V2 = $100, and preventing T2 from succeeding (since V1 + (V2 − $200) is now −$200), or T2 happens first and similarly prevents T1 from committing. > > If the database is under snapshot isolation(MVCC), however, T1 and T2 operate on private snapshots of the database: each deducts $200 from an account, and then verifies that the new total is zero, using the other account value that held when the snapshot was taken. Since neither update conflicts, both commit successfully, leaving V1 = V2 = −$100, and V1 + V2 = −$200. So I wanted to ask, is my formulation and understanding of the situation correct, the snapshot isolation can only violate serializability if there are "loops" between sets of records that transactions writes and read as such? Or are there possible ways for anomalies to other than that?

In MariaDB the default isolation level is repeatable read. I understand this means when I open a transaction I will not see any concurrent writes to the DB. I also have a situation where I need to ensure concurrent changes are not made to rows. For this, I can acquire an exclusive lock. My question is really about how the two work in conjunction. If I acquire the lock inside my update transaction it means it is possible I open the transaction and am blocked because another process has the exclusive lock, when I obtain the lock changes may have been made to the row which I would not see but might need to be aware of. I am hesitant to change the isolation level as this is a specific scenario. At the moment I have settled on acquiring the lock before I begin my update transaction but this makes releasing the lock more problematic as it would not be released once my transaction commits. Am I missing something obvious? So basically I open a transaction SELECT FOR UPDATE. Then open a second transaction to make the update. Semi-related: Does the above mean that optimistic locking when your transaction level is repeatable read is kind of a no-go? Optimistic locking involves checking a version/timestamp to ensure no other transaction has modified it since it was initially read. With repeatable reads, we wouldn't have visibility of other transactions until our transaction is complete which defeats the purpose. Or does it just mean I should be acquiring locks outside of the transactions?

I am running this query, SELECT name , snapshot_isolation_state , is_read_committed_snapshot_on FROM sys.databases s name snapshot_isolation_state is_read_committed_snapshot_on master 1 0 tempdb 0 0 model 0 0 msdb 1 0 MyDB1 1 1 MyDB2 1 1 MyDB1 and MyDB2 is enabled snapshot but still all my Entity Framework queries are using, select transaction_isolation_level from sys.dm_exec_requests All sessions values are transaction_isolation_level=2 which means Read Committed. What can I do?

I'm in a debate over whether you also need to set ALLOW SNAPSHOT ISOLATION on *tempdb* itself, or only on user databases for the functionality to kick in (in addition to code referencing this)? Most articles and examples seems to set the property on the user database itself only and leave *tempdb* to OFF as is. But then I found this one link Pro SQL Server 2005 Database Design and Optimization , which says: >(You can even do this on tempdb) alter database tempdb set allow_snapshot_isolation on Is it necessary to also set it on *tempdb* in addition to the user database, or just the user database? The main purpose is for an application that needs to have snapshot isolation on.

I am currently investigating a repeating error which occurs on the secondary Replica of our 2 node Alwasy on High Availability cluster. The Replica is set up with Read-Intent only because we use a separate Backup solution (Dell Networker). The Tempdb keeps growing in the secondary replica because the Version Store never gets cleared. I can fix it temporarly when i failover the Availability Groups, but after a couple of hours the error appears again on the repilca node. The error seems to follow one specific Availabilty Group, every node where its currently replicating gets the error after some time. So i guess it has to be a issue caused by a transaction and not from the sytem itself. I tried all suggestions on google to find the culprit but even if i recklessly kill all sessions with last_batch in the timeframe i get from the Perfmon "longest running transaction time" indicator (as advised here: https://www.sqlservercentral.com/articles/tempdb-growth-due-to-version-store-on-alwayson-secondary-server ), it won't start cleaning up the Versionstore. The shown elapsed seconds also match the output of the Query on the Secondary node:

* from sys.dm_tran_active_snapshot_database_transactions

details are sadly not usefull: [Output](https://i.sstatic.net/1AZrP.png) Here it shows Transaction ID 0 and Session ID 823 but the Session ID is long gone and keeps getting used by other processes alread. So i am stuck here. I tried to match the Transaction_sequence_num with anyting, but no luck so far. On the Primary Node it show no open transactions of any kind. Any help finding the cause of this open snapshot transaction is appreciated. I followed this guides already to find the issue: https://sqlundercover.com/2018/03/21/tempdb-filling-up-on-secondary-replicas/ https://sqlgeekspro.com/tempdb-growth-due-to-version-store-in-alwayson/ https://learn.microsoft.com/en-us/archive/blogs/docast/rapid-growth-of-tempdb-on-alwayson-secondary-replica-due-to-version-store https://www.sqlshack.com/how-to-detect-and-prevent-unexpected-growth-of-the-tempdb-database/ https://social.msdn.microsoft.com/Forums/sqlserver/en-US/635a2fc6-550b-4e08-b232-0652bd6ea17d/version-store-space-not-being-released?forum=sqldatabaseengine https://www.sqlservercentral.com/articles/tempdb-growth-due-to-version-store-on-alwayson-secondary-server **Update:** To show my claim that the session is long gone: Here the Pictures you see the output of sys.dm_tran_active_snapshot_database_transactions, sys.sysprocesses and sys.dm_exec_sessions: The first Picture shows currently 2 "open" snapshot database transactions. (normally it was always one in the past, but maybe the more the better) and it shows the now to this time running sessions on this ids. Than i proceeded to kill session 899 and 823 and checked again: Here you can see the active_snapshot_database_transaction is still showing the 2 Session_ids and the sysprocesses and dm_exec_sessions show now the 2 IDs are in use by a different Program, User, database etc. because i killed them and the ID number immediately got reused. If i check through the day sometimes they are even not in use at all. If i check the elapsed time and the perfmon longest running transaction i would be looking for a session with a logintime or batch at aroung 2023-02-03 00:00:56. But if i check all sleeping sessions or sessions with last batch in this range and even kill all of them ( like described in all of the links above) it still shows the "transaction" in sys.dm_tran_active_snapshot_database_transactions with ever growing numbers. **Update 2:** in the meantime we needed to resolve the issue with a failover because the tempdb ran out of space. Now the new "stuck" session id as shown in dm_tran_active_transaction has the session id 47 and is currently at around 30000sec rising. So the problem started at around 11.2.2023 00:00:20. Here is the Output of dm_tran_active_transaction:

The read committed snapshot and snapshot isolation levels in SQL Server do away with most locking except one: [A writer still locks out other writers](https://stackoverflow.com/questions/1044164/using-isolationlevel-snapshot-but-db-is-still-locking) . The documentation tiptoes around saying as much and subsequently doesn't document anything else which would be very interesting to know: Is it really ever just a modified row that gets exclusively locked? Or can it also be unrelated rows (eg. adjacent in an index) or pages? I did have a look at the locks in sys.dm_tran_locks and I only ever saw exclusive locks on modified rows during an uncommitted transaction - pages where merely locked as IX. I also tested whether two transactions could modify two different rows simultaneously during two uncommitted transactions in a really small table that likely fits into one page and that worked as well. If indeed only modified rows are exclusively locked, than this would give an application with exclusive access to the database the guarantee of lock-free writing if it makes sure that no two connections write simultaneously to the same row. This would be possible in the scenario I have in mind - but there's hardly a way to do something like that if page locks come into play as it's unpredictable what rows exactly would be affected.

I'm trying to understand SQL Server version store and related isolation levels. As I understand it, when a database enabled read committed snapshot option, this could happen: - An item (id = 1) has price $1000 in database - Session 1 starts an update statement: update products set price = price * 1.5. Since this touches all rows of the table, it takes long time. - While the update statement still in progress, Session 2 starts a query: select * from products where id = 1. Since the database is in read committed snapshot mode, writers do not block readers. So session 1 read the old version of the row from version store and thinks the product is of price $1000. - The user of session 1 thought the price was not bad, so he decided to buy it. But ... - Before the user add the product to his shopping cart, the aforementioned update statement is finished executing, and the new price for product (id = 1) is $1500. If the user knows the new price for the product, he would not buy it. In this case, what will happen? Is this scenario actually possible? If so, what's the norm to prevent this?