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, ACCOUNT2Value Integer AMOUNTVariable Integer RET_STATUSVariable Char RET_MESSAGE()Local Integer TRANS_OPENLocal Char ACCOUNT_CODELocal File ACCOUNT [ACCOUNT]# Open a transaction if not openedIf adxlog=0TRANS_OPEN=0Trbegin [ACCOUNT]Endif# Perform a credit / debit movement# CODE is a unique code so adxuprec can only return 0 or 1Update [ACCOUNT] Where CODE=ACCOUNT1 With [ACCOUNT]BALANCE-AMOUNTIf adxuprec=1Update [ACCOUNT] Where CODE=ACCOUNT2 With [ACCOUNT]BALANCE+AMOUNTElseACCOUNT_CODE=ACCOUNT1EndifIf adxuprec=0ACCOUNT_CODE+=string(ACCOUNT_CODE="",ACCOUNT2) : # Contains the first account not foundRET_STATUS=[V]CST_AERRORRET_MESSAGE="Account"-ACCOUNT_CODE-"not found"If TRANS_OPEN=0RollbackEndifElseRaz RET_MESSAGERET_STATUS=[V]CST_AOKIf TRANS_OPEN=0CommitEndifEndifEnd