Remedy - Server - Can Remedy AR System utilize the READ_COMMITTED_SNAPSHOT isolation level?

Version 1
    Share:|

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy AR System Server


    COMPONENT:

    AR System


    APPLIES TO:

    BMC Remedy AR System Server



    QUESTION:

     
       Can Remedy AR System utilize the READ_COMMITTED_SNAPSHOT isolation level?
    Are there any known issues with changing the Database Snapshot Isolation Level to READ_COMMITTED_SNAPSHOT?
     


    ANSWER:

     

    BMC is not aware of any defects or issues using the READ_COMMITTED_SNAPSHOT option on Microsoft SQL Server database.

    Vendor link:
    (http://msdn.microsoft.com/en-us/library/ms173763.aspx)

    READ COMMITTED specifies that statements cannot read data that has been modified but not committed by other transactions.  This prevents dirty reads.  Data can be changed by other transactions between individual statements within the current transaction, resulting in non-repeatable reads or phantom data. This option is the SQL Server default.

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

    When the READ_COMMITTED_SNAPSHOT database option is ON, you can use the READCOMMITTEDLOCK table hint to request shared locking instead of row versioning for individual statements in transactions running at the READ COMMITTED isolation level.

    Common issues that could happen on Remedy9 if READ_COMMITTED_SNAPSHOT is off is that tables will hit contention locks preventing any future queries to run on specific tables like ft_pending, and configuration tables. 

    We recommend that READ_COMMITTED_SNAPSHOT be turned on.

     


    Article Number:

    000030307


    Article Type:

    FAQ/Procedural



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles