Transactions: Handling transactions programmatically

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

This content has been translated automatically. Click here to view the French version.

Help / WLanguage / Managing databases / HFSQL / Managing transactions

Overview
Handling the transactions programmatically
Implementing the management of transactions
Summary table of WLanguage functions used (for an ISAM HFSQL or Client/Server database)
Handling the 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.
Canceling a transaction may occasionally fail because the cancellation of a transaction may trigger violations of integrity and/or duplicate constraints.
Errors specific to the management of transactions
Managing special cases
Power failure
Tip: restoring the consistency of the database
Error while using the program
Deleting the transaction log
Differences of behavior between the ISAM and Client/Server transactions
Advanced management
Transactions: The created files
Identifier of the computer that performs the transaction
Transactions and independent HFSQL context

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

Transactions: Handling transactions programmatically

Available only with these kinds of connection

Overview

This chapter presents the following topics:

The management of transactions is available for HFSQL Client/Server databases.

Handling the transactions programmatically

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. Please note: processing is relatively slower (since each operation is recorded in a specific data file).
Cancel (if necessary) the operations performed during the transaction (HTransactionCancel.
Specify the end of the transaction with HTransactionEnd: the transaction is validated.

Remarks:

The transaction isolation level used is "READ UNCOMMITED".
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)

Features	WLanguage function

Enable (or not) the management of transactions (the management of transactions is enabled by default).	HSetTransaction
Define the isolation level.	HTransactionIsolation
Start the transaction.	HTransactionStart or HTransaction
Commit transaction.	HTransactionEnd
Cancel the current transaction	HTransactionCancel
Roll back a transaction that failed (power outage).	HTransactionCancel HTransactionStart/HTransactionEnd WDTrans tool (or WDOptimizer). HFSQL Control Center.
Determine if a transaction has been interrupted (the transaction has not been committed or cancelled). Case of power outage.	HTransactionInterrupted
If a record in the specified data file is considered to be in a transaction, but does not belong to any current transaction, it is automatically freed.	HTransactionFree
Determine if a transaction is in progress.	HTransactionInProgress

Handling the 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 transaction operations must be carried out in the same process The HTransactionStart function (or the HTransaction function) and the HTransactionCancel function must be called from the same process or event: Button control click code, ...

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.

Canceling a transaction may occasionally fail because the cancellation of a transaction may trigger violations of integrity and/or duplicate constraints.

Example 1: Duplicate constraint violation

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, computer B adds a new record in the same data file with the same unique key value as the record deleted on computer A.
Canceling the transaction at this moment will trigger a duplicate error on the unique key.

Solution 1 Roll back a transaction 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 the HSetDuplicates function before rolling back a 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: Violation of the 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 Roll back a transaction 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 the HSetIntegrity function before rolling back a 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.

Board: Plan for such 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 problematic record, the HFSQL engine suggests the assisted management of errors: a window lets you correct 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 match the password of the initial file
The data file (in HFSQL Client/Server mode) and the transaction file do not use the same password.
70032: Insolation mode problem
This error can occur in the following cases:
- You are trying to use HTransactionIsolation on data files that are not in Client/Server format.
- You are trying to use an isolation level while the transaction is not performed on a connection.
- You are trying to use HTransactionIsolation while the transaction was already started by HTransactionStart (or HTransaction).

Managing special cases

Power failure

If a breakdown (power outage, 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:

During the first call to HTransactionStart (or to HTransaction).
With the HTransactionCancel function.
By WDTrans (or WDOptimizer) for an ISAM database.
By the HFSQL Control Center for an HFSQL Client/Server database.

Please note Restoring database consistency may take some time.

Note: To find out if you need to restore database consistency, test the result of the HTransactionInterrupted function in the project initialization code (for example).

Tip: restoring the consistency of the database

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:
- Call the HTransactionCancel function.
- Call HTransactionStart/HTransactionEnd.
- Start WDTrans (or WDOptimizer) for an ISAM database.
- Start the HFSQL Control Center for an HFSQL Client/Server database.

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

Alternative solution It is also possible to manage error 70034 in the project's "Initialization" event, using 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 failure, it is advisable to re-index the application data 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 data file created and present only for the duration of the 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.

Advanced management

Transactions: The created files

When transactions are set up, two types of HFSQL data files are created:

transaction log of operations Temporary file in HFSQL format containing the various operations performed on 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).
This file is named TRSOPERATION.TRS.
value log 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.

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.

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:

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.