|
|
Transactions: Secure processes on HFSQL data files
  Available only with these kinds of connection
This chapter presents the following topics: Handling the transactions through programming Implementing the management of transactions - If your data files are password protected, open all the data files used during the transaction before the start of transaction or specify the different passwords with HPass.
If your data files are not password protected, the data files handled after HTransactionStart (or HTransaction) will be automatically put in transaction. - Start the transaction with HTransactionStart or HTransaction.
- Perform your operations. All the write operations performed on the data files in transaction are automatically saved in the transaction file. Caution: the processes performed are slower (because each operation is saved in a specific data file).
- Cancel (if necessary) the operations performed during the transaction (HTransactionCancel.
- Specify the end of transaction with HTransactionEnd: the transaction is validated.
Remarks: - The transaction isolation mode used is "READ UNCOMMITED".
- The transaction isolation mode is defined with HTransactionIsolation. The default isolation mode is the "READ UNCOMMITED" mode.
- Transactions are not available for Hyper File 5.5 data files.
Summary table of WLanguage functions used (for an HFSQL ISAM or Client/Server database) Handling records during a transaction: the rules to follow Using the short transactions The records used during the transaction are automatically locked in write mode to prevent the other computers from modifying the affected data and therefore to secure the transaction. In a network application, if another user tries to modify a record in transaction, the automatic management of locks will allow this user to cancel or retry the operation. Therefore, the transaction must be as short as possible to avoid locking the users. Locking the records in read mode All the modifications performed during a transaction are visible from all the computers (on a network for example) before the end of transaction. The other computers can read the data with a limited lifespan (if the transaction is canceled by HTransactionCancel for example). Therefore, we strongly advise you to lock the relevant records in read mode. Performing one transaction at a time An application must perform a single transaction at a time. No transaction must be performed in simultaneous threads or in independent HFSQL contexts. No user interface (window, report, page, ...) must be used between the start and the end of a transaction. To cancel a transaction via a button, use an "Automatic validation" Button control over a short period of time. This allows you to avoid potential interactions with the data handled from other computers of the network. The canceling of a transaction may occasionally fail due to integrity constraint violations and/or duplicate constraint violations. Example 1: Violation of the duplicate constraints - A data file handled by a transaction contains a unique key.
- A computer A performs a transaction during which a record is deleted from this data file.
- At the same time, a computer B adds a new record into the same data file with the same value of unique key as the record deleted by the computer A.
- Canceling the transaction at this moment will trigger a duplicate error on the unique key.
Solution 1: Try to cancel the transaction again with WDTrans (or WDOptimizer). This tool allows you to ignore the duplicate errors and/or the integrity errors ("Disable the management of integrity" and "Disable the management of duplicates" in the wizard for canceling the transactions). Solution 2: Use HSetDuplicates before canceling the transaction. This function allows you to temporarily ignore the management of duplicates. In this case, don't forget to re-enable the management of duplicates by positioning the parameter to True after the cancelation of the transaction. Then, you must check the incorrect records and modify them accordingly Example 2: Violation of integrity constraints - A CUSTOMER data file is linked to an ORDERS data file (linked on a key)
- A computer A performs a transaction during which a record is added into the CUSTOMER file.
- At the same time, a computer B adds a new record into the ORDERS data file linked to the record added into the CUSTOMER file.
- Canceling the transaction at this precise moment will trigger an integrity error because the record added into the ORDERS data file will have no link with the record deleted from the CUSTOMER data file (the addition into the CUSTOMER file will have been canceled).
Solution 1: Try to cancel the transaction again with WDTrans (or WDOptimizer). This tool allows you to ignore the duplicate and/or integrity errors (check "Disable the integrity management" and "Disable the duplicate management" in the wizard for canceling the transactions). Solution 2: Use HSetIntegrity before canceling the transaction. This function is used to temporarily ignore the integrity errors (by positioning the parameter to False). In this case, don't forget to re-enable the management of integrity by positioning the parameter to True after the cancelation of the transaction. Then, you must check the incorrect records and modify them accordingly. Tip: Plan for this type of conflicts in your programs and when creating data files in the data model editor. - The duplicate errors will not occur if you are using unique keys whose type is automatic identifier.
- If you manage unique keys manually (without automatic identifiers), you should set a unique value for all computers when adding or changing records (HAdd or HModify).
Reminder: By default, for each record that presents a problem, the HFSQL engine proposes the assisted management of errors: a window used to fix the duplicate conflicts.
Errors specific to the management of transactions - 70031: Operation not allowed in transaction
You are using a function that is not allowed during a transaction. For example, HTransactionStart (or HTransaction) is used in the middle of a transaction. - 70034: The last transaction failed
You are trying to use a record that belongs to a failed transaction (power outage, ...). The program is restarted but the transaction is not canceled. In this case, we recommend that you cancel the transaction that failed (see below). - 74020: The password of the transaction file does not correspond to the password of the source file
The data file (in HFSQL Client/Server mode) and the transaction file do not use the same password. - 70032: Problem of isolation mode
This error can occur in the following cases:
If a breakdown (power failure, reboot, ...) occurs during a transaction, the data files may become corrupted: the transaction was neither validated, nor canceled. The transaction file is still found on the computer. In this case, the database consistency will be restored: Caution: Restoring the database integrity may take a while. Remark: To find out whether the database consistency must be restored, check the result of HTransactionInterrupted in the project initialization code (for example). Tip: restoring the database consistency To restore the database consistency, we advise you to perform the following operations: - Check the result of HTransactionInterrupted in the project initialization code, for example.
- If the transaction was interrupted, perform one of the following operations to restore the database consistency:
Example:
IF HTransactionInterrupted() = True THEN
IF Confirm("The transaction performed by computer " + H.TrsPost + ...
" was interrupted. " + ...
"Do you want to restore the consistency of data files?") = True THEN
// Cancels the interrupted transactions
IF HTransactionCancel() = False THEN
Error("Unable to cancel the transaction")
END
END
END
Other solution: The 70034 error can also be handled in the "Initialization" event of the project using the WHEN EXCEPTION keyword. Therefore, when the error 70034 occurs, the database consistency will be restored either by HTransactionCancel, or by HTransactionStart/ HTransactionEnd. Remark: After a power outage, we recommend that you reindex the application files. Error while using the program When the application stops because of a programming error (division by zero for example), the current transaction is automatically canceled. Deleting the transaction log The transaction log is an HFSQL file created and present only during the time of transaction. You should not delete this file for fear of database integrity problems. Differences of behavior between the ISAM and Client/Server transactions | | | | Behavior in HFSQL Classic | Behavior in HFSQL Client/Server |
|---|
| Creating a data file during a transaction (HCreation). | | If the transaction is canceled, the data file becomes empty again. | | Closing a data file during a transaction (HClose). | The transaction is interrupted. | The transaction is not canceled. | | Unlocking a record or a data file during a transaction. | The lock of transactions is canceled.
The transaction is interrupted. | Unlocking a record or a data file
The transaction is not canceled. | | Running an INSERT/UPDATE query. | The transaction is interrupted. | The transaction is not canceled. |
Transaction: The created files Two types of HFSQL data files are created when implementing the transactions: - the log of operations in transaction: Temporary file in HFSQL format containing the different operations performed on the application files taken into account by the transaction. This file is created with HTransactionStart (or with HTransaction). By default, it is named <Project name>_$TRS_OPERATION.TRS. This name can be modified with HTransactionStart (or HTransaction).
This file is named TRSOPERATION.TRS. - the log of values: Temporary file associated with each data file taken into account by the transaction. This file is named <Data file name>_$$_TRSVAL.TRX. For each operation performed in the transaction, this file contains:
- the content of the record after the operation (during a deletion for example)
- the content of the record after the operation (during an addition for example)
This file is named <Data file name>.TRX.
These files can be handled by WDTRANS or WDOptimizer.
Identifier of the computer that performs the transaction By default, the computer is identified by a unique number and by the computer name (defined in Windows). To easily identify the computer that performs the operations in transaction, HComputer is used to define an identifier specific to the computer. This identifier replaces the computer name. This identifier is saved in the log of operations in transaction and it can be viewed with WDTRANS. Transactions and independent HFSQL context When copying a context, if a transaction is in progress on the first context, the new context is not in transaction. You must call HTransactionStart (or HTransaction) to start a transaction in the new context.
Related Examples:
|
Training (WINDEV): WD Transaction
[ + ] This program, powered by WINDEV, is based on a simplified analysis (ORDERS, ORDERLINE and STOCK). It illustrates the operating mode of the transactions when placing an order.
|
This page is also available for…
|
|
|
|
|
|
|