Transaction Processing
DataFlex supports transaction processing. A transaction is a group of database operations that must all complete, or fail, together to maintain database integrity. If one of them can't be done for some reason, then none of the operations can be allowed to complete.
Let's use the process of creating an invoice as an example. First, you would get the last invoice number from a system table, increment it, and write it back. Then you would write a record in the invoice header table. Next, for each line item, you would find the appropriate record in the part-number table, reduce the part's quantity on hand to reflect the quantity being sold on this line item, and then write a record in the invoice line-item table. Finally, you would update the customer's balance in the customer table. All of the above operations make up one transaction.
Let's assume that while processing one of the invoice's line items, we discovered that the part specified for the line item was not in stock. The customer had said he needed all the items and could not wait for a back order to be filled. Therefore, we must cancel the invoice and tell the customer that he will have to find someone else to fill his order.
Without transaction processing, for each line-item record that had already been processed, we would have to look up the corresponding record in the part table, increase the part's quantity on hand to reflect the quantity being placed back in stock, and then delete the line-item record. We would then delete the invoice header record. We can't, however, in a multi-processing environment, give back the invoice number because someone else may already have used the next number (there are well-known ways to overcome this problem, but for the sake of our example, we will assume that the problem does not exist). This process is called "backout." The code to do it might look like this:
Procedure DoTransaction
number nAmount
integer iItem iBackoutItem
boolean bVoid
// Assign invoice number for this new invoice
reread sysfile
move sysfile.next_invoice to invoice.invoice_number
saverecord invoice
unlock
// For each line item in the invoice ...
for iItemNum from 1 to iNumItems
// Create the item in the item file
clear item
: (move item fields to item database)
saverecord item
// Update our stock file to reflect quantity sold
move item.stock# to stock.stock#
if not (Err) begin
find eq stock.stock#
move (stock.available + item.amount) to stock.available
saverecord stock
end
// Update our invoice total to reflect line item amount
if not (Err) begin
move (nAmount + item.amount) to nAmount
end
// If something went wrong, backout this invoice
if (Err) get DoBackout iBackoutItems iItemNum to bVoid
if not (Err) loop
// If nothing went wrong, update the invoice total
if (iItemNum = iNumItems) begin
reread invoice
move (invoice.amount + nAmount) to invoice.amount
save invoice
unlock
end
End_Procedure
Function DoBackout integer iBackoutItems integer iItemNum returns Boolean
// For each item already saved for this invoice ...
for iBackoutItems from 1 to iItemNum
// Find the previously saved line item
clear item
move invoice.invoice_number to item.invoice_number
move backout_item to item.item#
lock
find eq item.item#
// Update stock to put back the quantity sold from this line item
clear stock
move item.stock# to stock.stock#
find eq stock.stock#
move (stock.available + item.amount) to stock.available
save stock
unlock
// Delete the saved line item
delete item
loop
// Delete the saved invoice header
reread invoice
delete invoice
unlock
Function_Return True
End_Function
As you can see, you would have to go to some trouble to back out the invoice. However, using transaction processing, there is an easier way. Before we write the first record, we mark the start of the transaction by executing the begin_transaction command. Now all database operations against tables that allow transaction processing will be considered as one transaction. When we have successfully finished the last operation in the transaction, we commit the transaction by executing the end_transaction command. If we wish to abort the transaction before it is finished, we can execute the abort_transaction command. Following is the same example using transaction processing:
// Note that this example is not a complete DataFlex program.
procedure Create_Invoice
integer iInvoiceNum, iItem, iCustomerNum
number nAmount
move 0 to nAmount
// Mark start of transaction
Begin_Transaction
// Get the next invoice number
Move (Get_Invoice_Num(Self)) to iInvoiceNum
// Write the invoice header record
Send Write_Invoice_Header iInvoiceNum
// Update each item's part record and write to line item record
For iItem From 0 To (Item_Count(Self))
If (Update_Part(iInvoiceNum, item)) = True ;
move (nAmount + (Write_Line_Item(InvoiceNum, iItem))) to nAmount
Else ;
// Backout transaction
Abort_Transaction
Loop
// If we have all the parts requested, update customer balance
If (iItem = (Item_Count(Self))) ;
Send Update_Customer iCustomerNum nAmount
// Commit transaction
End_Transaction
End_Procedure
Note that, except for the abort_transaction command, we no longer need any logic to back out the transaction. When abort_transaction is executed, all tables, including the system table that gave us the invoice number, are restored to the state that existed before the begin_transaction function was executed.
After a transaction is aborted, the buffers of all the open tables with a DF_FILE_TRANSACTION attribute setting of DF_FILE_TRANSACTION_NONE will be cleared. In addition, if any of these are system tables, after being cleared, the first record will be read into the buffer.
Although transaction processing looks pretty good, there is a disadvantage to using it. When begin_transaction is executed, a lock is automatically executed. The lock is released after executing end_transaction or abort_transaction. This means a lock will be in effect for the full length of the transaction. This could cause response problems. For instance, in our example, no other invoice could be started until ours was done. However, since no other invoice could be started, the invoice number of an aborted invoice could be restored to the system table for reuse.
Keep in mind that each table can be marked to allow or not allow transaction processing. Therefore, you can control which tables are part of a transaction. The only tables that should have transaction processing turned off are tables that will not be written to (e.g., lookup tables).
If you use transaction-processing commands on tables that have it turned off, a begin_transaction command is equivalent to a lock or reread, and an end_transaction command is equivalent to an unlock.