Progress
Programming
Handbook
Understanding Where Transactions Begin and End
How does Progress know where to start the transaction and how much work to undo or back out? The following transaction blocks start a transaction if one is not already active:
- Any block that uses the TRANSACTION keyword on the block statement (DO, FOR EACH, or REPEAT).
- A procedure block, trigger block, and each iteration of a DO ON ERROR, FOR EACH, or REPEAT block that directly updates the database or directly reads records with EXCLUSIVE–LOCK. You use EXCLUSIVE–LOCK to read records in multi-user applications. See "Locks" for more information on locking.
Directly updating the database means that the block contains at least one statement that can change the database. CREATE, DELETE, and UPDATE are examples of such statements.
If a block contains FIND or FOR EACH statements that specify EXCLUSIVE–LOCK, and at least one of the FIND or FOR EACH statements is not embedded within inner transaction blocks, then the block is directly reading records with EXCLUSIVE–LOCK.
Note that DO blocks do not automatically have the transaction property. Also, if the procedure or transaction you are looking at is run by another procedure, you must check the calling procedure to determine whether it starts a transaction before the RUN statement.
Once a transaction is started, all database changes are part of that transaction, until it ends. Each user of the database can have just one active transaction at a time:
This procedure has two blocks: the procedure block and the REPEAT block. The procedure block has no statements directly in it that are not contained within the REPEAT block. The REPEAT block contains an INSERT statement that lets you add order records to the database. Because the REPEAT block is the outermost block that contains direct updates to the database, it is the transaction block.
At the start of an iteration of the REPEAT block, Progress starts a transaction. If any errors occur before the END statement, Progress backs out any work done during that transaction.
Note that data-handling statements that cause Progress to automatically start a transaction for a regular table will not cause Progress to automatically start a transaction for a work table or temporary table.
Consider another example:
This procedure has four blocks:
- Procedure block — There are no statements in this block, so Progress does not start a transaction at the start of the procedure.
- Outer REPEAT block — The outermost block that directly updates the database (INSERT order WITH 2 COLUMNS). Therefore, it is a transaction block. On each iteration of this block, Progress starts a transaction. If an error occurs before the end of the block, all work done in that iteration is undone.
- Inner REPEAT block — Directly updates the database but it is not the outermost block to do so. Therefore, it is not a transaction block. It is, however, a subtransaction block. Subtransactions are discussed later in this chapter.
- FOR EACH block — An outermost block that directly updates the database (UPDATE region). Therefore, it is a transaction block. On each iteration of this block, Progress starts a transaction. If an error occurs before the end of the block, all work done in that iteration is undone.
Go ahead and run this procedure. Enter the data shown in the following display:
![]()
After you enter the order information, press GO, then enter the data for line number 1 of the order and press GO. The procedure prompts you for the next order–line. Press END–ERROR to tell the procedure that you are finished entering order–lines for order 1005. The procedure prompts you for information on the next order.
Enter the data shown on the following screen:
![]()
After you enter the quantity and are prompted for the price, press STOP. Progress returns you to the Procedure Editor.
Remember that either a system failure or pressing the STOP key causes Progress to back out the current transaction. Let’s retrace our steps to determine which of the order and order–line data you entered should be in the database.
- On the first iteration of the REPEAT block, you entered data for order number. Because Progress automatically starts a transaction for a REPEAT block if it is an outermost block containing database updates, a transaction was started at the start of that iteration.
- In the inner REPEAT block, you entered a single order–line for order 1005. Even though this is a REPEAT block and does directly update the database, a transaction is already active. Therefore, Progress does not start a transaction for this inner REPEAT block. All of the updates done in this inner block are part of the transaction that is already active.
- When you finished entering that first order–line and pressed
GO
to add the next order, Progress reached the end of the iteration of the transaction block and ended the transaction. Therefore, the order and order–line data you entered were written to the database. It should still be there, right? Runp-check.p
to see if order 1005 and its order–line record exist.
This procedure displays order 1005 and its single order–line, proving that the data is in the database.
The following steps trace the second iteration of the REPEAT block.
- On the second iteration of the REPEAT block, you entered data for order 1010. Because Progress automatically starts a transaction for a REPEAT block if it is the outermost block containing database updates, a transaction was started at the start of that iteration.
- In the inner REPEAT block, you started entering an order–line for order 32. Even though this is a REPEAT block and does directly update the database, a transaction is already active. Therefore, Progress does not start a transaction for this inner REPEAT block and you were still working within the initial transaction. In the middle of the transaction, you pressed
STOP
, which backed out the current transaction.Run the
p-check.p
procedure again, this time supplying 1010 as the order number. Progress displays the message “order record not on file.”This message confirms that the data for order 1010 was not stored in the database. But remember the rule about all-or-nothing processing. When working with multiple tables in a single transaction, it is important that either all the changes to all the tables are completed or none of the changes to any of the tables are completed.
When you pressed STOP, you were in the middle of adding an order–line record. Just to be sure that an order–line record corresponding to order 1010 was not stored in the database, run this procedure (supply 1010 as the order number and 1 as the order–line number).
The Data Dictionary definition of the order–line field specifies a validation of CAN–FIND(order OF order–line). This means that when you supply an order–number, Progress checks to be sure that the order exists. Since you already know the order does not exist (we checked by running the
p-check.p
procedure), you can use the NO–VALIDATE Frame phrase option in the PROMPT–FOR statement. This option tells Progress to ignore any validation criteria defined in the Dictionary.When you run the
p-check2.p
procedure and supply 1010 as the order number and 1 as the order–line number, Progress displays the message “order–line record not on file”.Not only did Progress undo the order information you had entered for order 1010 but it also undid the partial order–line information you had entered.
If you did not press STOP but your system crashed, you would have seen exactly the same behavior.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |