Defining a Transaction

You define a logical transaction by issuing a COMMIT or ROLLBACK command following the transaction's last data source command. (For simplicity, the remainder of this topic refers to COMMIT only, but unless stated otherwise, both commands are meant.) For example, the beginning of your application is the beginning of its first logical transaction. The data source commands that follow are part of the transaction. When the application issues its first COMMIT command, it marks the end of the first transaction.

The data source commands that follow the first COMMIT become part of the second logical transaction; the next COMMIT to be issued marks the end of the second transaction, and so on.

The COMMIT command defines the transaction's boundary. All data source commands issued between two COMMIT commands are in the same transaction. (This explanation describes the simplest case, in which a transaction exists entirely within a single procedure. When a transaction spans procedures, you have several options for deciding how to define a transaction's boundary, as described in When an Application Ends With an Open Transaction.)

For example, transferring money from a savings account to a checking account requires two update commands. If you want to define the transfer, including both updates, as one logical transaction, you could use the following function:

Reference: When Does a Data Source Command Cause a Transaction to Fail?

A data source command can fail for many reasons—for example, an UDPATE command might try to write to a record that never existed because a key was mistyped, or an INCLUDE command might try to add a record that has already been added by another user.

In some cases, when a command fails, you might want to keep the transaction open and simply resolve the problem that caused the command to fail. For example, in the first case—attempting to update a record that doesn't exist—you might wish to ask the end user to correctly re-enter the customer code (which is being used as the record's key). In other cases, you might wish to roll back the entire transaction.

If a data source command fails, it will only cause the logical transaction that contains it to be automatically rolled back in certain circumstances. The deciding factor is when a data source command fails. If a data source command fails when the transaction:

Is open (that is, when the application issues the data source command), the transaction remains open, and the failed data source command does not become part of the transaction. This means that, if the application later attempts to commit the transaction, because the failed data source command is not part of the transaction, it will not affect the transaction's success or failure.
You can evaluate the success of a data source command in an open transaction by testing the value of the FocError system variable immediately after issuing the command. If you wish the failure of the data source command to roll back the transaction, you can issue a ROLLBACK command.
Is being closed (that is, when the application tries to commit the transaction), the failure of the data source command to be written to the data source causes the transaction to fail, and the entire transaction is automatically rolled back.

Canceling a Transaction

A transaction that is ongoing and has not yet been committed is called an open transaction. If you ever need to cancel an open transaction, you can do so by issuing a ROLLBACK command. ROLLBACK voids any of the transaction's data source commands that have already been issued, so that none of them are written to the data source.

Transactions and Data Source Position

When a logical transaction is committed or rolled back, it resets all position markers in all the data sources that are accessed by the transaction's procedures. (Resetting a data source's position markers points them to the beginning of the data source's segment chains.)

How Large Should a Transaction Be?

A transaction is at its optimal size when it includes only those data source commands that are mutually dependent upon each other for validity. If you include "independent" commands in the transaction and one of the independent commands fails when you try to commit the transaction, the dependent group of commands will be needlessly rolled back.

For example, in the following banking transaction that transfers funds from a savings account to a checking account,

CASE TransferMoney
 UPDATE Savings FROM SourceAccts
 UPDATE Checking FROM TargetAccts
 COMMIT
ENDCASE

you should not add an INCLUDE command to create a new account, since the validity of transferring money from one account to another does not depend upon creating a new account.

Another reason for not extending transactions unnecessarily is that, in a multi-user environment, the longer a transaction takes, the more likely it is to compete for records with transactions submitted by other users. Transaction processing in a multi-user environment is described in Concurrent Transaction Processing.

Designing Transactions That Span Procedures

Logical transactions can span multiple Maintain procedures. If a Maintain procedure with an open transaction passes control to a non-Maintain procedure (for example, a report procedure), the open transaction is suspended; when control next passes to a Maintain procedure, the transaction picks up from where it had left off.

When a transaction spans several procedures, you will usually find it easier to define the transaction's boundaries if you commit it in the highest procedure in the transaction (that is, in the procedure closest to the root procedure). Committing a transaction in a descendant procedure of a complex application, where it is more difficult to track the flow of execution, makes it difficult to determine the transaction's boundaries (that is, to know which data source commands are being included in the transaction).

When a child procedure returns control to its parent procedure, and the child has an open logical transaction, you have two options:

You can continue the child's open transaction into the parent procedure when the child returns control to the parent. Simply specify the KEEP option when you return control with the GOTO END command.
You can close the child's open transaction automatically at the end of the child procedure. By default, Maintain issues an implied COMMIT command to close the open transaction. You can also specify this behavior explicitly by coding the RESET option when you return control with the GOTO END command.

RESET and KEEP are both described in Optimizing Performance: Data Continuity and Memory Management.

Top of page

Example: Moving a Transaction Boundary Using GOTO END KEEP

Consider a situation where procedure A calls procedure B, and procedure B then calls procedure C. The entire application contains no COMMIT commands, so the initial logical transaction continues from the root procedure (A) through the descendant procedures (B and C). C and B both return control to their parent procedure using a GOTO END command.

The table below shows how specifying or omitting the KEEP option when procedures B and C return control affects the application's transaction boundaries—that is, how the choice between KEEP and the implied COMMIT determines where the initial transaction ends, and how many transactions follow.

C returns to B with...	B returns to A with...	Transaction boundaries ( \|\| )
KEEP	KEEP	A-B-C-B-A one transaction
KEEP	implied COMMIT	A-B-C-B \|\| A two transactions
implied COMMIT	KEEP	A-B-C \|\| B-A two transactions
implied COMMIT	implied COMMIT	A-B-C \|\| B \|\| A three transactions

Designing Transactions That Span Data Source Types

If a transaction writes to multiple types of data sources, each database management system (DBMS) evaluates its part of the transaction independently. When a COMMIT command ends the transaction, the success of the COMMIT against each data source type is independent of the success of the COMMIT against the other data source types. This is known as a broadcast commit.

For example, if you issue a Maintain procedure against the FOCUS data sources Employee and JobFile and a DB2 data source named Salary, the success or failure of the COMMIT against Salary is independent of its success against Employee and JobFile. It is possible for COMMIT to be successful against Salary and write that part of the transaction, while being unsuccessful against Employee and JobFile and roll back those parts of the transaction.

When an Application Ends With an Open Transaction

If an application terminates while a logical transaction is still open, Maintain issues an implied COMMIT command to close the open transaction, ensuring that any data source commands issued after the last explicit COMMIT are accounted for. (The only exception is that if your FOCUS session abnormally terminates, Maintain does not issue the implied COMMIT, and any remaining uncommitted data source commands are rolled back.)

Determining Whether a Transaction Was Successful

When you close a transaction by issuing a COMMIT or ROLLBACK command, you must determine whether the command was successful. If a COMMIT command is successful, then the transaction it closes has been successfully written to the data source; if a ROLLBACK command is successful, the transaction it closes has been successfully rolled back.

The system variable FocCurrent provides the return code of the most recently issued COMMIT or ROLLBACK command. By testing the value of FocCurrent immediately following a COMMIT or ROLLBACK command, you can determine whether the transaction was successfully committed or rolled back. If the value of FocCurrent is:

Zero, the command was successful.
Not zero, the command was unsuccessful.

FocCurrent is global to all the procedures in a transaction, and so does not need to be passed as an argument between procedures.

Top of page

Example: Evaluating the Success of a Transaction

The following function commits a transaction to a data source. If the transaction is unsuccessful, the application invokes another function that writes to a log and then begins a new transaction. The line that evaluates the success of the transaction is shown in bold:

CASE TransferMoney
    UPDATE AcctBalance FROM SourceAccts
    UPDATE AcctBalance FROM TargetAccts
    COMMIT 
    IF FocCurrent NE 0 THEN PERFORM BadTransfer 
ENDCASE

Concurrent Transaction Processing

Several applications or users often need to share the same data source. This sharing can lead to problems if they try to access a record concurrently—that is, if they try to process the same data source record at the same time.

To ensure the integrity of a data source, concurrent transactions must run as if they were isolated from each other; one transaction's changes to a data source must be concealed from all other transactions until that transaction is committed. To do otherwise runs the risk of exposing open transactions to interim inconsistent images of the data source, and consequently corrupting the data source.

To prevent users from corrupting the data in this way, the database management system must coordinate concurrent access. There are many strategies for doing this. No matter which type of data source you use, Maintain respects your DBMS's concurrency strategy and lets it coordinate access to its own data sources.

For more information about how your DBMS handles concurrent access, see your DBMS vendor's documentation. For FOCUS data sources, this information is presented in Ensuring Transaction Integrity for FOCUS Data Sources. For DB2, you can find some suggested strategies for writing Maintain transactions to DB2 data sources in Ensuring Transaction Integrity for DB2 Data Sources. For many other types of data sources, you can also apply the strategies described in Ensuring Transaction Integrity for DB2 Data Sources, changing DBMS-specific details when necessary.

Top of page

Example: Why Concurrent Access to a Data Source Must Be Carefully Managed

Consider the following two applications that access the Employee data source:

The Promotion application reads a list of employees who have received promotions and updates their job codes to correspond to their new positions.
The Salary application, run once at the beginning of each year, checks every employee's job code and gives each employee an annual raise based on his or her job title. For example, assistant managers (job code A15) will earn $30,000 in the new year, and managers (A16) will earn $40,000.

Joan Irving is an assistant manager. Consider what happens when these two applications try to access and update the same record at the same time, without any coordination:

The Promotion application reads Irving's record and, based on information in a transaction file that she has been promoted to manager, computes her new job code (A16).
The Salary application reads Irving's record and, based on her job code in the data source (A15), computes her new salary ($30,000).
The Promotion application writes the new job code (A16) to the data source.
The Salary application writes the new salary ($30,000) to the data source.

Remember the earlier business rule (assistant managers earn $30,000, managers earn $40,000). Because two applications accessed the same record at the same time without any coordination, the rule has been broken (Joan Irving has a manager's job code but an assistant manager's salary). The data source has become internally inconsistent.