How Do I Initiate a Database Transaction? (Magic xpa 2.x)

Magic xpa has built-in transaction processing, which is initiated automatically by default. Using it is mainly a matter of understanding how it works so you can use it most effectively.

Here we will look at the basics of how to set up a database transaction in Magic xpa. There are more details included in the F1 Help and elsewhere in this chapter. Also, if you are unclear about how transaction processing is working in your program, check the Magic xpa log files, or the native database log files, to see exactly how transactions are being handled.

How to Define the Transaction Type

Within each task, the transaction mode is set in the Task Properties dialog box (Ctrl+P) on the Data tab. For on Online or Bbatch task, you will have the four choices shown above. For Browser tasks, you have the choice of “None” instead of “Physical”.

However, there are basically three different basic types of transactions.

1. Physical transactions rely on the underlying DBMS to do all the transaction handling.

2. Deferred transactions are handled by Magic xpa. Magic xpa caches the data manipulation statements, and does the rollback if needed. Once the data is committed, Magic xpa sends the data manipulation statements to the server in one batch.

3. No transactions: Or, you can opt for no transactions at all. (See How Do I Refrain from Opening a Transaction? for how to do this).

The other two transaction types are extensions of Physical or Deferred.

• Nested deferred is a special type of Deferred transaction, which is explained in How Do I Implement a Nested Transaction?.

• Within active trans will either initiate a Physical transaction or a Deferred transaction, whichever was used by the parent task.

So, your first decision when setting up a Magic xpa task is whether you want Physical, Deferred, or No transactions. Deferred transactions give you somewhat more flexibility, and there are some Magic xpa features (such as nested transactions) that will only work with Deferred transactions. Also, if you are working with a Browser task, Physical transactions aren’t available.

The Transaction Tree

When deciding which kind of transaction processing to use, you need to keep in mind the tree structure of your programs. How a transaction works in a child task depends on the transaction settings of the parent task.

Suppose we have an Order Entry screen, showing one header record and three child records. We change the header record, then we change all three child records. While parked on the last child record, we do a rollback at the child level. Then we repeat the experiment, doing the rollback at the parent level. Here are the results for each of the settings. (All transactions are set to Before Record Prefix).

Parent	Child	Result	Parent Rollback	Child Rollback
Deferred	Deferred	The child changes are considered part of the parent changes. All of them are committed or rolled back as a unit.	Rolls back parent change and all child changes.	Rolls back parent change and all child changes.
Deferred	Within Active	Same as Deferred-Deferred
Deferred	Nested Deferred	Each child change and each parent change is considered independent. Each record is committed when the user leaves that record.	Rolls back the parent changes (which are uncommitted since we are still on that record).	Rolls back only the last (uncommitted) child change.
Deferred	Physical	Each child change and each parent change is considered independent. Each record is committed when the user leaves that record.	Rolls back the parent changes (which are uncommitted since we are still on that record).	Rolls back only the last (uncommitted) child change.
Physical	Physical	The child changes are considered part of the parent changes. All of them are committed or rolled back as a unit.	Rolls back parent change and all child changes.	Rolls back parent change and all child changes.
Physical	Within Active	Same as Physical -Physical
Physical	Deferred	ERROR

What happens is that usually, if a transaction is opened in the parent task, the child task shares in the same transaction rather than opening its own. The exceptions to this are if a Deferred transaction parent opens a Physical transaction child, or if the child is Nested deferred.

Also, if Physical transaction parent calls a child task set to use Deferred, this will generate an error. If you are not sure of the transaction mode of the parent program (as when a task might be called from several different programs), the safest bet is to set the Child task to use Within active. Then it will work with parents that use either Physical or Deferred transactions.

How to define where a transaction starts and ends

The Transaction begin property determines when a transaction will be opened. For Physical transactions you have the six choices shown above; for Deferred transactions the only choices are Before task prefix and Before record prefix.

The transaction will end at the same level at which it began. If the transaction began at the task level, then it will end when the task ends. If the transaction began at the record level, then it will end when the user is done with that record.

However, as shown in the previous section, the current task may be sharing in the parent’s transaction, in which case the transaction is open for the life of the child task regardless of the Transaction begin setting.

How to define the tables involved

When you are setting up the parent task of the task tree, you should make sure all the tables that will participate in the transaction are declared in the parent task. Declaring the table in the parent will open the table at the highest level, which in addition to making the transactions work properly, will also make the data access faster.