Transactions and database locking support is built directly into Data Dictionaries. Database updates are, therefore, always multi-user safe. Managing transactions, locking and multi-user access is automatic requiring no programming effort from the developer.
All database updates within a Data Dictionary occur within a transaction. When a save occurs an entire hierarchy of records may be created, updated and saved. When a delete occurs it is possible that many records, from many child tables will be deleted and that many parent table records will be updated. Both of these operations occur within a single transaction. If for any reason a transaction cannot be completed, the entire transaction is rolled back and no changes are committed to the database.
Locks will be placed on your database during a transaction. The tables that participate in the transaction will be write-locked. This allows other users to read data from the tables but not allow them to write data until the locks are released. Locks are released when a transaction has ended – either as a successful commit or a failed rollback.
The granularity of a lock depends on the database. Some databases such as the embedded DataFlex database will place locks on an entire table; other databases, such as Microsoft SQL and Pervasive.SQL will place locks on the records involved in the transaction. In either case, because transactions involve locking you want to make transactions as short as possible.
Data Dictionaries are designed to keep transactions short and small and there is little you have to do to assist in this process. You should be careful to never require user input during a transaction. Don’t ask questions once a save or delete has started.
If an error is encountered during a transaction, the transaction is stopped. When an error occurs in a DDO during a transaction, two things happen:
The DDO process is stopped and the transaction is rolled back
The error message is not actually generated until after the rollback and the tables are all unlocked.
The deferral of the error message ensures that the error will not be reported while tables are locked.
The error can be handled (it is expected and generated by the programmer) or unhandled (it is unexpected and generated by the runtime or the database). In either case, the results are the same. If the error is generated by the programmer, it must be generated with the UserError message or the Error command. Only the error command triggers the proper transaction abort sequence and the proper deferral of error reporting.
Internally the UserError message generates a handled error using the Error command and therefore it properly defers error reporting. The UserError message was created to simplify handled error reporting.
When an error occurs, all processing of code stops. The runtime internally rolls back the transaction, cleans up its call stack and jumps to the end of the transaction. The remainder of the DDO messages are not sent and the remainder of any procedure or function code is not executed. In the following example, the code following the UserError message in Validate_Save will never get executed:
Function Validate_Save returns integer
Send UserError "We have an error"
// these two statements will never get executed.
Send DoSomething
Function_Return 1
End_Function
If you generate an error inside Validate_Save or Validate_Delete, there is no need to return a non-zero value. The process has already been halted.
Any Error anywhere within a transaction halts the transaction. This means that you can generate an error and stop the transaction inside of any of the save events (e.g. Validate_Save, Creating, Update, Backout, Save_Main_File) or delete events (e.g. Validate_Delete, Deleting, Backout, Delete_Main_File).
Multiple save and delete operations can be grouped within a single transaction. This is done by placing the transactions within a Begin_Transaction / End_Transaction command block. The saves and deletes within the transaction block may refer to different DDOs or perform multiple operations within the same DDO.
// Save changes in two different DDOs (handles of DDOs are passed)
Procedure SaveTransactions handle hoDDO1 handle hoDDO2
Begin_Transaction
Send Request_Save of hoDDO1
Send Request_Save of hoDDO2
End_Transaction
End_Procedure
// Delete all records for passed array of RowIds
Procedure DeleteRecords RowId[] DeleteIDs
Integer iRec iRecs iFile
Get Main_File of oSome_DD to iFile
Begin_Transaction
Move (SizeOfArray(DeleteIDs)) to iRecs
For iRec from 0 to (iRecs-1)
Send FindByRowId of oSome_DD iFile DeleteIds[iRec]
if (Found) ;
Send Request_Delete of oSome_DD
Loop
End_Transaction
End_Procedure
The purpose of grouping transactions is to make sure that the entire transaction is treated as a single entity. Therefore:
Either the entire transaction is committed or the entire transaction is rolled back.
Locks applied during the grouped transaction will remain in effect until the transaction is complete.
If any DDO save or delete within the transaction fails, the entire transaction is rolled back and program control is returned to the line following the End_Transaction command.
Grouped transactions should be used only when required. You always want to make keep transaction durations as short as possible. DDOs are designed to generate short transactions. Combining many short transactions into a group somewhat defeats the purpose. The above delete example, could create a very long transaction, which could interfere with other user’s database operations. Common sense needs to be applied when grouping transactions.