Snapshot Isolation

Unknown

Snapshot Isolation

Unknown

I want to try Snapshot level isolation as I think that it will fix a lot of the contention issues we are seeing.

[url=https://www.databasejournal.com/features/mssql/snapshot-isolation-level-in-sql-server-what-why-and-how-part-1.html]https://www.databasejournal.com/features/mssql/snapshot-isolation-level-in-sql-server-what-why-and-how-part-1.html[/url]

To do this, you need to enable it in the database:

[url=https://support.microsoft.com/en-au/help/919160/how-to-enable-the-snapshot-transaction-isolation-level-in-sql-server-2]https://support.microsoft.com/en-au/help/919160/how-to-enable-the-snapshot-transaction-isolation-level-in-sql-server-2[/url]

The Decisions documentation is not clear whether the platform itself manages setting these parameters or whether the DBA needs to do it. Please can you confirm if we need to do this on each of our environments or whether Decisions itself sets the values?

Also, please can you confirm whether your ‘Snapshot’ mode transactions use SQL Server Read Committed Snapshot Isolation (RCSI) or Snapshot Isolation (SI). The latter requires additional changes to the SET command to set the transaction.

Unknown

The Isolation Level parameter for the Start Transaction step can be set by the user to any of the following in the screenshot:
[img]att1[/img]

More information regarding the Transaction step can be found [url=https://documentation.decisions.com/docs/using-transaction-steps]here[/url].

The "Snapshot" value for the Isolation Level parameter in the Decisions Start Transaction step uses Snapshot Isolation IF the ALLOW_SNAPSHOT_ISOLATION ON database option has been set (by a DBA) in the SQL Server database.

Via this MS Doc ([url=https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15]https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15[/url]):
"The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:

If READ_COMMITTED_SNAPSHOT is set to OFF (the default on SQL Server), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.

If READ_COMMITTED_SNAPSHOT is set to ON (the default on Azure SQL Database), the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions."

Therefore, it would require your DBA to set the READ_COMMITTED_SNAPSHOT flag in the database to get the behavior you require.