ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / HFSQL / Managing transactions
  • Overview
  • Handling the transactions through programming
  • Implementing the management of transactions
  • Summary table of WLanguage functions used (for an ISAM HFSQL or Client/Server database)
  • Handling records during a transaction: the rules to follow
  • Using the short transactions
  • Locking the records in read mode
  • Performing one transaction at a time
  • No user interface (window, report, page, ...) must be used between the start and the end of a transaction.
  • The canceling of a transaction may occasionally fail due to integrity constraint violations and/or duplicate constraint violations.
  • Errors specific to the management of transactions
  • Managing special cases
  • Power failure
  • Tip: restoring the database consistency
  • Error while using the program
  • Deleting the transaction log
  • Differences of behavior between the ISAM and Client/Server transactions
  • Advanced management
  • Transaction: The created files
  • Identifier of the computer that performs the transaction
  • Transactions and independent HFSQL context
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Transactions: Secure processes on HFSQL data files
HFSQL ClassicHFSQL Client/ServerAvailable only with these kinds of connection
Handling the transactions through programming

Implementing the management of transactions

  1. 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.
  2. Start the transaction with HTransactionStart or HTransaction.
  3. 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).
  4. Cancel (if necessary) the operations performed during the transaction (HTransactionCancel.
  5. Specify the end of transaction with HTransactionEnd: the transaction is validated.
Remarks:
  • HFSQL Classic The transaction isolation level used is "READ UNCOMMITED".
  • HFSQL Client/Server The transaction isolation level is defined with HTransactionIsolation. The default isolation level is the "READ UNCOMMITED" mode.
  • Transactions are not available for Hyper File 5.5 data files.

Summary table of WLanguage functions used (for an ISAM HFSQL or Client/Server database)

FeaturesWLanguage function
Enable (or not) the management of transactions
(the management of transactions is enabled by default).
HSetTransaction
HFSQL Client/Server Define the isolation mode.
HTransactionIsolation
Start the transaction.HTransactionStart or HTransaction
Validate the transaction.HTransactionEnd
Cancel the current transactionHTransactionCancel
Cancel a transaction that failed (power outage).
Determine if a transaction has been interrupted (the transaction has not been committed or cancelled). Case of power outage.HTransactionInterrupted
If a record found in the specified data file is considered as being in transaction but does not belong to a transaction in progress, it is automatically freed.HTransactionFree
Determine if a transaction is in progress.HTransactionInProgress
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.

All the transaction operations must be performed in the same process: HTransactionStart (or HTransaction) and HTransactionCancel must be called from the same process or event: click code of a Button control, etc.
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 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 cancellation of the transaction.
Then, you must check the incorrect records and modify them accordingly
Example 2: Integrity constraint violation
  • 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 cancellation 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 the 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 and the password of the source file do not match
    The data file (in HFSQL Client/Server mode) and the transaction file do not use the same password.
  • 70032: Problem of isolation level
    This error can occur in the following cases:
Managing special cases

Power failure

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:
  1. Check the result of HTransactionInterrupted in the project initialization code, for example.
  2. 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 ClassicBehavior 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.
Advanced management

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 HTransaction). By default, it is named <Project name>_$TRS_OPERATION.TRS. This name can be modified with HTransactionStart (or HTransaction).
    HFSQL Client/Server 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)
      HFSQL Client/Server This file is named <Data file name>.TRX.
These files can be handled by WDTRANS or WDOptimizer.
HFSQL Client/Server In HFSQL Client/Server, an additional file is created: TRSOperationInfoClient.TRS.
This file contains the unique information used to identify the transaction (client name, computer, ...).

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.
HFSQL Client/Server The computer identifier includes:
  • Name and IP address of computer.
  • Name of application, which means ExecutableName(ProjectName).

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:
WD Transaction 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.
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 09/08/2023

Send a report | Local help