Managing Transactions Within Applications

Transaction Commands

InterSystems IRIS supports the ANSI SQL operations COMMIT WORK and ROLLBACK WORK (in InterSystems SQL the keyword WORK is optional). It also supports the InterSystems SQL extensions SET TRANSACTION, START TRANSACTION, SAVEPOINT, and %INTRANS. In addition, InterSystems IRIS implements some of the transaction commands that are part of the M Type A standard.

These SQL and ObjectScript commands are summarized in the following table.

These ObjectScript and SQL commands are fully compatible and interchangeable, with the following exception:

ObjectScript TSTART and SQL START TRANSACTION both start a transaction if no transaction is current. However, START TRANSACTION does not support nested transactions. Therefore, if you need (or may need) nested transactions, it is preferable to start the transaction with TSTART. If you need compatibility with the SQL standard, use START TRANSACTION.

Using LOCK in Transactions

Whenever you access a global which might be accessed by more than one process, you need to protect the integrity of the database by using the LOCK command on that global. You issue a lock corresponding to the global variable, change the value of the global, then unlock the lock. The LOCK command is used to both lock and unlock a specified lock. Other processes wishing to change the value of the global request a lock which waits until the first process releases the lock.

There are three important considerations when using locks in transactions:

• Lock/unlock operations do not roll back.

• Within a transaction, when you unlock a lock held by the process, one of two things may occur:

  • The lock is immediately unlocked. The lock can be immediately acquired by another process.

  • The lock is placed in a delock state. The lock is unlocked, but cannot be acquired by another process until the end of the current transaction.

  If the lock is in a delock state, InterSystems IRIS defers the unlock until the transaction is committed or rolled back. Within the transaction, the lock appears to be unlocked, permitting a subsequent lock of the same value. Outside of the transaction, however, the lock remains locked. For further details, refer to Lock Management.

• Lock operations that time out set $TEST. A value set in $TEST during a transaction does not roll back.

Using $INCREMENT and $SEQUENCE in Transactions

A call to the $INCREMENT or $SEQUENCE function is not considered part of a transaction. It is not rolled back as part of transaction rollback. These functions can be used to get an index value without using the LOCK command. This is advantageous for transactions where you may not want to lock the counter global for the duration of the transaction.

$INCREMENT allocates individual integer values in the order that increment requests are received from one or more processes. $SEQUENCE provides a fast way for multiple processes to obtain unique (non-duplicate) integers for the same global variable by allocating a sequence (range) of integer values to each incrementing process.

Transaction Rollback within an Application

If you encounter an error during a transaction, you can roll it back in three ways:

• Issue the SQL rollback command, ROLLBACK WORK

• Issue the ObjectScript rollback command, TROLLBACK

• Make a call to %ETN

Issue an SQL or ObjectScript Rollback Command

Application developers can use two types of rollback commands to designate the unsuccessful end of a transaction and automatically roll back incomplete transactions:

• Use ##sql(ROLLBACK WORK), in the macro source routine.

• Use the ObjectScript TROLLBACK command, in macro or intermediate source code.

The rollback command must cooperate with an error trap, as in the following example:

ROU        ##sql(START TRANSACTION) set $ZT="ERROR" 
            SET ^ZGLO(1)=100 
            SET ^ZGLO=error 
            SET ^ZGLO(1,1)=200 
            ##sql(COMMIT WORK) Write !,"Transaction Committed" Quit
ERROR        ##sql(ROLLBACK WORK) 
            Write !,"Transaction failed." Quit

In the example code, $ZT is set to run the subroutine ERROR if a program error occurs before the transaction is committed. Line ROU begins the transaction and sets the error trap. Lines ROU+1 and ROU+3 set the nodes of the global ^ZGLO. However, if the variable error is undefined, ROU+2 causes a program error and line ROU+3 does not execute. Program execution goes to the subroutine ERROR and the set of ^ZGLO(1) is undone. If line ROU+2 were deleted, ^ZGLO would have its value set both times, the transaction would be committed, and the message “Transaction committed” would be written.

Make a Call To %ETN

If you have not handled transaction rollback with a rollback command, the error trap utility %ETN detects incomplete transactions and prompts the user to either commit or rollback the transaction. You should handle rollback within your application, since committing an incomplete transaction usually leads to degradation of logical database integrity.

If you run %ETN after an error when a transaction is in progress, the following rollback prompt is displayed:

You have an open transaction.
Do you want to perform a Commit or Rollback?
Rollback =>

If there is no response within a 10-second timeout period, the system defaults to rollback. In a jobbed job or an application mode job, the transaction is rolled back with no message.

%ETN itself does not do anything to trigger transaction rollback, but it typically ends by halting out of InterSystems IRIS. Transaction rollback occurs when you halt out of ObjectScript and the system runs %HALT to perform InterSystems IRIS process cleanup. There is an entry point into %ETN, called BACK^%ETN, which ends with a quit, rather than a halt. If a routine calls BACK^%ETN, rather than ^%ETN or FORE^%ETN, it will not perform transaction rollback as part of the error handling process.

Examples of Transaction Processing Within Applications

The following example shows how transactions are handled in macro source routines. It performs database modifications with SQL code. The SQL statements transfer funds from one account to another:

Transfer(from,to,amount)   // Transfer funds from one account to another
{
   TSTART
    &SQL(UPDATE A.Account
        SET A.Account.Balance = A.Account.Balance - :amount
        WHERE A.Account.AccountNum = :from)
    If SQLCODE TRollBack  Quit "Cannot withdraw, SQLCODE = "_SQLCODE
    &SQL(UPDATE A.Account
        SET A.Account.Balance = A.Account.Balance + :amount
        WHERE A.Account.AccountNum = :to)
    If SQLCODE TROLLBACK  QUIT "Cannot deposit, SQLCODE = "_SQLCODE
    TCOMMIT
    QUIT "Transfer succeeded"
}