Transactions and Locking Using the Command Language
The DataFlex programming language supports a number of commands to define transactions and use locking. These commands are:
- Begin_Transaction to define the beginning of a transaction
- End_Transaction to commit a transaction
- Abort_Transaction to rollback a transaction
- Lock to lock the database
- Reread to lock the database and refresh all active buffers
- Unlock to unlock the database
Transactions support the ACID properties, making it simpler to program a system that supports transactions. For example, suppose we have a restriction in our application that we can only order items that are in stock. In a non-transaction system, this condition would have to be checked before trying to save the order. In a transaction system, however, one can start the transaction, then begin saving the rows that make up the order. If, along the way, the condition is not met, an error can be issued, which will return the database to the state it had before starting the transaction. If the condition is met for every row, an End_Transaction can be issued, thus committing the transaction. Using the transaction commands to define transactions is referred to as doing explicit transactions.
Errors in a Transaction
Because transactions must support Atomicity, errors that occur during a transaction will force a rollback of the transaction. When an error occurs during an explicit transaction, control will jump to the next line of the End_Transaction command. This behavior results in minimal use of the Abort_Transaction command. Transactions are usually rolled back because some error occurred; since the occurrence of an error results in a rollback, there is no need to explicitly use the Abort_Transaction command in such cases.
The only error that is an exception to this rule is the DFERR_LOCK_TIMEOUT (4106) error. If this error occurs, the program will check the retry setting, and if a retry must be done, it will jump to the beginning of the (implicit or explicit) transaction. The number of retries can be set by using the Set_Transaction_Retry command. If the object-oriented programming style is used, the message Verify_Retry will be sent to the object where the transaction is defined. This function can return zero, in which case the transaction will be retried; a non-zero return value will stop the retry. If the logic loops through all its retries and still encounters a timeout error, the timeout error will be treated as any other error would.
Implicit transactions will not be aborted when an error occurs. Instead, the flow of the program will continue on the next line. Since implicit transactions were designed for compatibility reasons, their behavior is the same as it was before transactions were introduced to DataFlex. Some database drivers may not be able to support implicit transactions.
Please note that explicit transactions will always be aborted when an error occurs, regardless of how the DF_TRANABORT_ONERROR attribute is set. Data sets and Data dictionaries automatically use explicit transactions. Setting the DF_TRANABORT_ONERROR attribute has no effect on Data Set / Data Dictionary based applications; they always use explicit transactions.
Lock Granularity
The Embedded Database uses a DataFlex-specific method for locking, which is not supported by other database systems. Issuing a lock can therefore result in different behavior depending on the database system in use.
The Embedded Database supports table-level locking only. This means that one and only one transaction can have a table locked at any given time. Most other database systems support row-level locking only, allowing multiple concurrent transactions to have rows in one table locked at a given time. A further difference in table and row locking is the moment the actual lock is done. When using table locking, the table is locked the moment the lock is issued by the program. In contrast, row or page locking can only lock the row or page after it, or a row on it, has been found. Thus, row or page locking systems lock while rows are being found in a transaction. This is a conceptual difference.
Some legacy DataFlex programs misuse the lock behavior of DataFlex to guarantee exclusive access to certain resources. For example, if we want to write to an ASCII disk file from a DataFlex program and (mis)use the DataFlex lock mechanism to ensure that only one DataFlex program writes to the file at any given time, this logic will no longer work if the underlying database changes to a row-locking database.