Best Practices - Data Transaction Handling

The purpose of this document is to provide best practices for data transaction handling.

Main principles

Managing database transactions requires that you know the main principles associated with this function in the SAFE X3 engine. The main principles are as follows:

• A database transaction groups a set of consistent updates performed in a database. They are either completely applied to the data, or not at all.
• During a database transaction, the database layer locks the resources. Therefore, a transaction must be short, and it must never be interrupted by a user interaction.
• A database transaction starts with Trbegin, and ends with Commit or Rollback.
• A unique level of transaction is possible in the SAFE X3 engine. Nesting a Trbegin in a Subprog called by a script already running a transaction generates an error.
• adxlog can be tested to see if a transaction is already in progress.

Main consequences on the Sage X3 code

• Do not use transactions when creating code to handle the method of an entity. As methods are usually called interactively within a context, creating a transaction that updates the database is not consistent. The AUPDATE, AINSERT, and ADELETE methods are running the transaction. However, the code related to the "begin" and "end" transactions is completely embedded by the supervisor, and does not need to be managed by the development partner.
• An operation performs updates within a transaction. The development partner can use the parameters given during the operation call, manage the resources needed, perform a transaction, and return a result. For example, in a query that updates the database, an operation triggered from a link on a record executes a transaction.
• A subprogram declared by Subprog or Funprog can also manage a transaction if it executes updates triggered from another routine that performs the updates.
• To avoid issues with nested transactions when an operation or a subprog needs to handle transaction updates, make sure adxlog is equal to 0.
  
  • If it is:
    
    • The transaction must be completely handled by the subprogram. Trbegin, Commit, and Rollback must be at the same level.
    • If an error occurs:
      
      • The Rollback must be performed.
      • An error message can be set with the right error method.
      • The routine can conclude.
  • If it is not:
    
    • The transaction is embedded within another transaction (for example, if an operation on an entity calls operations on child entities).
    • The development partner must perform the following:
      
      • Skip the Trbegin step.
      • Perform the database operation.
      • Return a status, usually with a method such as ASETERROR.
    • It is the responsibility of the calling operation to perform the Rollback if the operation fails.

Example

# Example of a transaction on a table (ACCOUNT)
Subprog CREDIT_DEBIT(ACCOUNT1,ACCOUNT2,AMOUNT,RET_STATUS,RET_MESSAGE)
Value Char ACCOUNT1, ACCOUNT2
Value Integer AMOUNT
Variable Integer RET_STATUS
Variable Char RET_MESSAGE()

Local Integer TRANS_OPEN
Local Char ACCOUNT_CODE
Local File ACCOUNT [ACCOUNT]

# Open a transaction if not opened
  If adxlog=0
    TRANS_OPEN=0
    Trbegin [ACCOUNT]
  Endif

# Perform a credit / debit movement
# CODE is a unique code so adxuprec can only return 0 or 1
  Update [ACCOUNT] Where CODE=ACCOUNT1 With [ACCOUNT]BALANCE-AMOUNT
  If adxuprec=1
    Update [ACCOUNT] Where CODE=ACCOUNT2 With [ACCOUNT]BALANCE+AMOUNT
  Else
    ACCOUNT_CODE=ACCOUNT1
  Endif

  If adxuprec=0
    ACCOUNT_CODE+=string(ACCOUNT_CODE="",ACCOUNT2) : # Contains the first account not found
    RET_STATUS=[V]CST_AERROR
    RET_MESSAGE="Account"-ACCOUNT_CODE-"not found"
    If TRANS_OPEN=0
      Rollback
    Endif
  Else
    Raz RET_MESSAGE
    RET_STATUS=[V]CST_AOK
    If TRANS_OPEN=0
      Commit
    Endif
  Endif
End