PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Overview
  • Handling the transactions by programming
  • Implementing the management of transactions
  • Summary table of WLanguage functions used (for a HFSQL ISAM 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/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Transactions: Secure your processes on HFSQL files
HFSQLHFSQL Client/ServerAvailable only with these kinds of connection
Overview
This chapter presents the following topics:
Versions 17 and later
iPhone/iPad This feature is now available for the iPhone/iPad applications.
New in version 17
iPhone/iPad This feature is now available for the iPhone/iPad applications.
iPhone/iPad This feature is now available for the iPhone/iPad applications.
AndroidAndroid Widget iPhone/iPadWindows MobileJava The management of transactions is available for the HFSQL Client/Server databases.
Note: From version 19, HFSQL is the new name of HyperFileSQL.
Handling the transactions by 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 will be automatically put in transaction.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.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.
Notes:
  • The transactions follow the SQL 92 standard "READ UNCOMMITED". To ensure the consistency of your data, this operating mode must be taken into account.
  • The transactions are not available for the Hyper File 5.5 data files.
Notes:
  • HFSQL The isolation mode of transactions is the "READ UNCOMMITED" mode.
  • HFSQL Client/Server The isolation mode of transactions is defined by HTransactionIsolation. The default isolation mode is the "READ UNCOMMITED" mode.
  • The transactions are not available for the Hyper File 5.5 data files.

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

FeaturesWLanguage function
Enable (or not) the management of transactions
(the management of transactions is enabled by default).
HSetTransaction
Versions 19 and later
HFSQL Client/Server Define the isolation mode.
New in version 19
HFSQL Client/Server Define the isolation mode.
HFSQL Client/Server Define the isolation mode.
Versions 19 and later
HTransactionIsolation
New in version 19
HTransactionIsolation
HTransactionIsolation
Start the transaction.HTransactionStartHTransactionStart or HTransaction
Validate the transaction.HTransactionEnd
Cancel the current transactionHTransactionCancel
Cancel a transaction that failed (power outage).
Used to find out whether a transaction was interrupted (the transaction was neither validated nor canceled). 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
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. Therefore, the other computers can read the data whose lifespan is limited (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 and HTransactionCancel must be called from the same process: click code of button, ...All the transaction operations must be performed in the same process: HTransactionStart (or HTransaction) and HTransactionCancel must be called from the same process: click code of button, ...
To cancel a transaction via a button, use an "Automatic validation" button 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 are handling the unique keys yourself (you are not using any automatic identifiers), you must define a unique value for all the computers when adding records (HAdd) or when modifying records (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 is used in the middle of a transaction.
    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.
  • Versions 19 and later
    70032: Problem of isolation mode
    This error can occur in the following cases:
    New in version 19
    70032: Problem of isolation mode
    This error can occur in the following cases:
    70032: Problem of isolation mode
    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.
Note: To find out whether the database consistency must be restored, check the result of HTransactionInterrupted in the initialization code of project (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 initialization code of project 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: You also have the ability to manage the error 70034 in the initialization code of project via the WHEN EXCEPTION keyword. Therefore, when the error 70034 occurs, the database consistency will be restored either by HTransactionCancel, or by HTransactionStart/HTransactionEnd.
Note: 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 a 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 file during a transaction (HCreation).If the transaction is canceled, the file becomes empty.
Closing a file during a transaction (HClose).The transaction is interrupted.The transaction is not canceled.
Unlocking a record or a file during a transaction.The lock of transactions is canceled.
The transaction is interrupted.
Unlocking a record or a 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 by HTransactionStart. By default, it is named <Project Name>_$TRS_OPERATION.TRS. This name can be modified by HTransactionStart.HFSQL Client/Server This file is named TRSOPERATION.TRS. 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 by HTransactionStart (or HTransaction). By default, it is named <Project Name>_$TRS_OPERATION.TRS. This name can be modified by 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 <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 <FileName>.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, HPost 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 to start a transaction in the new 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 required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment