Versions Compared

Old Version 1

changes.mady.by.user admin

Saved on

compared with

New Version 2

changes.mady.by.user admin

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Clients who have very high transaction volume might encounter some SQL deadlock issues. In these cases, Aptify recommends using snapshot isolation to significantly reduce or eliminate database blocking issues in high-volume situations. The purpose of this topic is to provide information about how to configure the SQL Server transaction isolation level.  

 

Please read thoroughly

Icon

It’s important to understand the implications of using this technique, which utilizes row-level isolation of data being updated in a transaction. One specific situation is inventory, where potentially stale inventory counts could be read if another transaction is in the process of updating that count. In the case of two transactions updating the same row at the same time, the second should fail and rollback, which would cause the transaction to have to be resubmitted. This technique should be tested thoroughly before being applied.

 
Anchor
_USIL
_USIL
Understanding Snapshot Isolation Levels

READ_COMMITTED_SNAPSHOT vs ALLOW_SNAPSHOT_ISOLATION

Icon

We recommend setting the READ_COMMITTED_SNAPSHOT database option ON. If you set the READ_COMMITTED_SNAPSHOT database option to ON, the database engine uses row versioning and snapshot isolation as the default, instead of using locks to protect the data. This topic also discusses setting ALLOW_SNAPSHOT_ISOLATION database option ON. This isolation state can be used but is not required as per Aptify's analysis and testing. 

...

Icon

Refer to the following Microsoft Development Network article for more information on understanding isolation:

https://msdn.microsoft.com/en-us/library/ms345124.aspx#sql2k5snapshotisol_topic05

In particular, review the section on Administrative Best Practices.

 
Anchor
_CSQL
_CSQL
Configuring SQL Server Snapshot Isolation

Perform this procedure to enable SQL Server snapshot isolation. Ensure that you understand the information in the Understanding SQL Server Snapshot Isolation Levels before going ahead and enabling snapshot isolation. 

  1. Log into SQL server management studio with an a sa-level user account.
  2. Open a new query.

  3. Run the following query to enable the READ_COMMITTED_SNAPSHOT option:

     

    /** Run this statement to change the isolation level of the database.
    When the READ_COMMITTED_SNAPSHOT database option is set ON, the mechanisms used to support the option are activated immediately. When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. There must be no other open connection in the database until ALTER DATABASE is complete. The database does not have to be in single-user mode.
    **/
     
    ALTER DATABASE APTIFY SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE Aptify SET READ_COMMITTED_SNAPSHOT ON; 
    ALTER DATABASE APTIFY SET MULTI_USER;

     

     

     

  4. Run the following query to enable the ALLOW_SNAPSHOT_ISOLATION option:

     

    /** Statement below can be used to turn on the snapshot isolation, so that transactions can set the snapshot isolation level. Per our analysis, there is no Aptify code that currently does this, therefore this is not required.**/
     
    ALTER DATABASE Aptify SET ALLOW_SNAPSHOT_ISOLATION ON; 

     



  5. Run the following query to verify the values of these options:

     

    /** Query to determine snapshot states **/
    SELECT
    Name
    , snapshot_isolation_state
    , snapshot_isolation_state_desc
    , is_read_committed_snapshot_on
    FROM sys.databases
    WHERE name = 'APTIFY';