ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / HFSQL / Managing transactions
  • Overview
  • READ UNCOMMITTED isolation mode
  • Principle
  • Example: Stock management
  • Example: Modifying a customer
  • Implementing the READ UNCOMMITTED isolation mode
  • READ COMMITTED isolation mode
  • Principle
  • Example: Stock management
  • Example: Modifying a customer
  • Implementing the READ COMMITTED isolation mode
  • REPEATABLE READ isolation mode
  • Principle
  • Example: Stock management
  • Example: Modifying a customer
  • Implementing the REPEATABLE READ isolation mode
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
Client/Server transactions: Available isolation modes
HFSQL Client/ServerAvailable only with this kind of connection
Overview
The HFSQL engine proposes to isolate the transactions: the modifications performed in a transaction in progress are isolated from the ones performed in the other transactions run simultaneously, until it is committed.
Several isolation modes are available:
This help page presents the different isolation modes and their implementation.
READ UNCOMMITTED isolation mode

Principle

When a computer starts a transaction, the other computers see the modified data as soon as the modification has been made (inserts, updates, deletes), and not only when the transaction has been committed.
Even if the transaction has not been committed (COMMIT), the other computers read the new data. Meanwhile, if the transaction is undone (ROLLBACK), invalid data will be read by the other computers.

Example: Stock management

Let's take a PRODUCT file in which there are 10 "Item01" in stock.
  • Computer A starts a transaction (HTransactionStart or HTransaction). Computer A modifies "Item01" and subtracts 2 from the quantity (HModify). The stock quantity changes to 8 but the transaction has not been committed yet.
  • Computer B uses the READ UNCOMMITTED isolation mode and reads "Item01". It reads 8 items in stock: the transaction of computer A has not been committed, but computer B sees the quantity in stock as if it had been committed.
  • Computer A has two options:
    • Commit the transaction (COMMIT). In this case, the quantity in stock remains 8.
    • Undo the transaction (ROLLBACK). In this case, the quantity in stock changes back to 10.
  • As long as the transaction has not been committed or rolled back, computer B sees 8 items in stock, which is false if the transaction is rolled back.

Example: Modifying a customer

  • Computer A modifies a record in a transaction. In this transaction, "Ann" becomes "Juliet". The transaction has not been committed.
  • Computer B reads the same record in READ UNCOMMITTED mode. It reads "Juliet" directly.

Implementing the READ UNCOMMITTED isolation mode

To implement the READ UNCOMMITTED isolation mode, you must:
READ COMMITTED isolation mode

Principle

In this mode, as long as the transaction has not been committed (COMMIT), the other computers ALWAYS read the initial data, i.e. the data as it existed before the modifications made during the current transaction (inserts, updates, deletes).
The modifications made during the transaction will be visible only when the transaction is committed (COMMIT).

Example: Stock management

Let's take a PRODUCT file in which there are 10 "Item01" in stock.
  • Computer A starts a transaction (HTransactionStart or HTransaction). Computer A modifies "Item01" and subtracts 2 from the quantity (HModify). The stock quantity changes to 8 but the transaction has not been committed yet.
  • Computer B uses the READ COMMITTED isolation mode and reads "Item01". It reads 10 items in stock.
  • Computer A has two options:
    • Commit the transaction (COMMIT). In this case, the quantity in stock changes to 8.
    • Undo the transaction (ROLLBACK). In this case, the quantity in stock remains 10.
  • As long as the transaction has not been committed or rolled back, computer B sees10 items in stock, as if the quantity had not changed.

Example: Modifying a customer

  • Computer A modifies a record in a transaction. In this transaction, "Ann" becomes "Juliet". The transaction has not been committed.
  • Computer B reads the same record in READ COMMITTED mode. It reads "Ann".

Implementing the READ COMMITTED isolation mode

To implement the READ COMMITTED isolation mode, you must:
REPEATABLE READ isolation mode

Principle

This mode is useful for specific needs.
In this mode, if the computer that started the transaction reads the database again, it will read the data as it existed at the start of the transaction, even if other computers have committed transactions that modify this data.
During the transaction, the same computer reads a "snapshot" of the database taken at the start of the transaction, and not the data committed by other computers.

Example: Stock management

Let's take a PRODUCT file in which there are 10 "Item01" in stock.
  • Computers A and B use the REPEATABLE READ isolation mode.
  • Computer A starts a transaction (HTransactionStart or HTransaction). It reads "Item01" and there are 10 items.
  • Computer B starts a transaction (HTransactionStart or HTransaction). It modifies "Item01" and subtracts 2 from the quantity (HModify). It commits the transaction. The stock changes to 8.
  • When computer B commits the transaction (COMMIT), computer A still reads "Item01" with a quantity of 10.
  • When computer A commits the transaction, next time it reads the quantity in stock, it will read 8 articles. It is only when computer A has committed its transaction that it will see the new value of the quantity in stock.

Example: Modifying a customer

  • Two computers A and B start a transaction.
  • In the transaction of computer A, "Ann" becomes "Juliet". The transaction is committed
  • Computer B reads the same record. It will still read "Ann" as long as its own transaction has not been committed.

Implementing the REPEATABLE READ isolation mode

To implement the REPEATABLE READ isolation mode, you must:
  • Use HFSQL files in Client/Server mode.
  • Use an HFSQL server in version 19 (or later).
  • Enable the REPEATABLE READ mode in each file of the analysis included in the transaction. This option is available in the "Details" tab of the file description window. An automatic modification of the data files is required to take this parameter into account. Caution: if this option is enabled, the file is not compatible with version 18 and earlier.
  • Call HTransactionIsolation with the hRepeatableRead constant.
  • Use syntaxes that handle connections in transaction management functions (HTransactionStart, HTransaction, HTransactionEnd and HTransactionCancel).
Tip: To find out whether the REPEATABLE READ mode is enabled on a data file, use RepeatableReadSupported.
Minimum version required
  • Version 19
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 05/26/2022

Send a report | Local help