A transaction is a logical unit of work. InterSystems IRIS® data platform transaction processing helps maintain the logical integrity of your database.
For example, when transferring money from one account to another, a bank may need to subtract an amount from a field in one table and add the same amount to a field in another table. By specifying that both updates form a single transaction, you ensure that either both operations are performed or neither is performed, which means that one cannot be executed without the other.
Within your application, a single SQL INSERT, UPDATE, or DELETE statement, or a single global SET or KILL, may not in itself constitute a complete transaction. In such cases, you use transaction processing commands to define the sequence of operations that forms a complete transaction. One command marks the beginning of the transaction; after a sequence of possibly many commands, another command marks the end of the transaction.
Under normal circumstances, the transaction executes in its entirety. If a program error or system malfunction leads to an incomplete transaction, then the part of the transaction that was completed is rolled back.
Application developers should handle transaction rollback within their applications. InterSystems IRIS also handles transaction rollback automatically in the event of a system failure and at various junctures, such as recovery and during HALT or ResJob.
InterSystems IRIS records rollbacks in the messages.log file if the LogRollback configuration option is set. You can use the Management Portal, System Operation, System Logs, Messages Log option to view messages.log.
Managing Transactions Within Applications
In InterSystems IRIS, you define transactions within applications using either
Both techniques work, regardless of whether the database modifications that constitute the transactions are performed with SQL INSERT, UPDATE, and DELETE statements or ObjectScript SET and KILL commands.
These techniques are not fully interchangeable; you should manage any given transaction entirely within ObjectScript or entirely within SQL. For example, if you start a transaction in ObjectScript, then use ObjectScript commands to handle the rest of that transaction, matching TSTART to TCOMMIT, and so on. If you use the SQL commands, then you should use the SQL commands and match START TRANSACTION to COMMIT, and so on. You can, however, have these pieces of code call each other, and these pieces of code can be nested.
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.
||Set transaction parameters without starting a transaction.
||Marks the beginning of a transaction.
||Detects whether a transaction is currently in progress:
<0 used by %INTRANS to mean in a transaction, but journaling disabled. Not used by $TLEVEL.
0 means not in a transaction.
>0 means in a transaction.
||Mark a point within a transaction. Can be used for partial rollback to a savepoint.
||Signals a successful end of transaction.
||Signals an unsuccessful end of transaction; all the database updates performed since the beginning of transaction should be rolled back or undone.
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 the “Lock Management” chapter of this book.
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.
$INCREMENT may be incremented by one process within a transaction and, while that transaction is still processing, be incremented by another process in a parallel transaction. If the first transaction rolls back, there may be a “skipped” increment, “wasting” a number.
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
When you roll back a transaction, the IDKey for any default class is not decremented. Rather, the value of the IDKey is automatically modified by the $INCREMENT function.
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"
##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?
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
SET A.Account.Balance = A.Account.Balance - :amount
WHERE A.Account.AccountNum = :from)
If SQLCODE TRollBack Quit "Cannot withdraw, SQLCODE = "_SQLCODE
SET A.Account.Balance = A.Account.Balance + :amount
WHERE A.Account.AccountNum = :to)
If SQLCODE TROLLBACK QUIT "Cannot deposit, SQLCODE = "_SQLCODE
QUIT "Transfer succeeded"
Automatic Transaction Rollback
Transaction rollback occurs automatically during:
InterSystems IRIS startup, if recovery is needed. When you start InterSystems IRIS and it determines that recovery is needed, any transaction on the computer that was incomplete will be rolled back.
Process termination using the HALT command (for the current process) or the ^RESJOB utility (for other processes). Halting a background job (non-interactive process) automatically rolls back the changes made in the current transaction-in-progress. Halting an interactive process prompts you whether to commit or roll back the changes made in the current transaction-in-progress. If you issue a ^RESJOB on a programmer mode user process, the system displays a message to the user, asking whether they want the current transaction committed or rolled back.
In addition, system managers can roll back incomplete transactions in cluster-specific databases by running the ^JOURNAL utility. When you select the Restore Globals From Journal option from the ^JOURNAL utility main menu, the journal file is restored and all incomplete transactions are rolled back.
System-Wide Issues with Transaction Processing
This section describes various system-wide issues related to transaction processing. For more information on issues related to backups, see the chapter Backup and Restore in the Data Integrity Guide; for more information on issues related to ECP, see the appendix ECP Recovery Process, Guarantees, and Limitations in the “Horizontally Scaling for User Volume with Distributed Caching” chapter of the Scalability Guide.
Backups and Journaling with Transaction Processing
Consider the following backup and journaling procedures when you implement transaction processing.
Each instance of InterSystems IRIS keeps a journal. The journal is a set of files that keeps a time-sequenced log of changes that have been made to the database since the last backup. InterSystems IRIS transaction processing works with journaling to maintain the logical integrity of data.
The journal contains SET and KILL operations for globals in transactions regardless of the journal setting of the databases in which the affected globals reside, as well as all SET and KILL operations for globals in databases whose Global Journal State you set to “Yes.”
Backups can be performed during transaction processing; however, the resulting backup file may contain partial transactions. In the event of a disaster that requires restoring from a backup, first restore the backup file, and then apply journal files to the restored copy of the database. Applying journal files restores all journaled updates from the time of the backup, up to the time of the disaster. Applying journals is necessary to restore the transactional integrity of your database by completing partial transactions and rolling back uncommitted transactions, since the databases may have contained partial transactions at the time of the backup. For detailed information, see:
“Journaling” chapter of the Data Integrity Guide
Importance of Journals section of the “Backup and Recovery” chapter of the Data Integrity Guide
Asynchronous Error Notifications
You can specify whether a job can be interrupted by asynchronous errors using the AsynchError()Opens in a new tab method of the %SYSTEM.ProcessOpens in a new tab class:
The AsynchErrorOpens in a new tab property of the Config.MiscellaneousOpens in a new tab class sets a system-wide default for new processes for whether processes are willing to be interrupted by asynchronous errors. It defaults to 1, meaning “YES.”
If multiple asynchronous errors are detected for a particular job, the system triggers at least one such error. However, there is no guarantee which error will be triggered.
The asynchronous errors currently implemented include:
<LOCKLOST> — Some locks once owned by this job have been reset.
<DATALOST> — Some data modifications performed by this job have received an error from the server.
<TRANLOST> — A distributed transaction initiated by this job has been asynchronously rolled back by the server.
Even if you disable a job receiving asynchronous errors, the next time the job performs a ZSync command, the asynchronous error is triggered.
At each TStart, TCommit, or LOCK operation, and at each network global reference, InterSystems IRIS checks for pending asynchronous errors. Since SET and KILL operations across the network are asynchronous, an arbitrary number of other instructions may interpose between when the SET is generated and when the asynchronous error is reported.
Suspending All Current Transactions
You can use the TransactionsSuspended()Opens in a new tab method of the %SYSTEM.ProcessOpens in a new tab class to suspend all current transactions for the current process. This is a boolean method: 1 = suspend all current transactions, 0 = resume all current transactions. The default is 0.
While transactions are suspended changes are not logged to the transaction log and therefore cannot be rolled back. Change made in the current transaction before or after an interval when transactions were suspended can be rolled back.
If you change a global variable in a transaction, and then change it again while that transaction is suspended may result in an error when rollback is attempted.
Invoking TransactionsSuspended() without specifying a boolean parameter returns the current boolean setting without changing that setting.