Best practice for data transaction handling

Main principles

Managing database transaction requires you to 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 done in a database. They will be 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 must not 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 will submit an error).
• adxlog can be tested to see if a transaction is already in progress.

Main consequences on the Sage X3 People code

• When creating code to handle a method of an entity, transactions must not be used. As a method is usually called in interactive mode within a context, creating a transaction that updates the database is not consistent. The AUPDATE, AINSERT, and ADELETE methods are running the transaction, but the code related to the 'begin' and 'end' transaction is completely embedded by the supervisor and does not need to be managed by the development partner.
• An operation performs updates within a transaction, and therefore opens a transaction, because it is called in a stateless mode. The development partner can use the parameters given at the operation call, manage the resources needed, perform a transaction, and return a result. For example, if you have an operation triggered from a link on a record in a query that updates the database, a transaction is executed.
• 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 updates in a transaction is to:
  • Check if adxlog is equal to 0.
  • If it is the case,
    • 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 done.
      • An error message can be set with the right error method.
      • The routine can conclude.
  • If it is not the case,
    • 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 by a method such as ASETERROR.
    • It is the responsibility of the calling operation to perform the Rollback if the operation failed.

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