Force all queries within a batch to get data as at the start of the batch



  • In SQL Server, when executing a batch of SELECT queries, is it possible to force all queries run during the course of a batch to get data as it was at the start of the batch, to ensure consistency between results of data is changed while the queries are running?



  • You can accomplish this by executing the batch within a transaction running at the SNAPSHOT isolation level.

    This requires enabling the ALLOW_SNAPSHOT_ISOLATION database option, https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql for the current session to SNAPSHOT, then starting an explicit transaction.

    See the https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide#behavior-in-summary for details.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2