PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • 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/iPadApple WatchUniversal Windows 10 AppWindows Mobile
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 validated.
Several isolation modes are available:
This help page presents the different isolation modes and their implementation.
Versions 22 and later
Android Widget This feature is now available in Android Widget mode.
Android This feature is now available for the Android applications.
Java This feature is now available for the Java applications.
New in version 22
Android Widget This feature is now available in Android Widget mode.
Android This feature is now available for the Android applications.
Java This feature is now available for the Java applications.
Android Widget This feature is now available in Android Widget mode.
Android This feature is now available for the Android applications.
Java This feature is now available for the Java applications.
READ UNCOMMITTED isolation mode

Principle

when a computer starts a transaction, the other computers sees the modified data as soon as the modification (addition, modification, deletion) is performed (and not only when the transaction is validated).
Even though the transaction was not validated (COMMIT), the other computers read the new data. Meanwhile, if the transaction is canceled (ROLLBACK), invalid data will be read by the other computers.

Example: Stock management

Let's consider a PRODUCT file with a Prod01 product whose quantity in stock is equal to 10.
  • The computer A starts a transaction (HTransactionStart). The computer A modifies the Prod01 product and subtracts 2 from the quantity (HModify). The quantity in stock is equal to 8 but the transaction is not validated.The computer A starts a transaction (HTransactionStart or HTransaction). The computer A modifies the Prod01 product and subtracts 2 from the quantity (HModify). The quantity in stock is equal to 8 but the transaction is not validated.
  • The computer B is using the READ UNCOMMITTED isolation mode and it reads the Prod01 product. The quantity in stock is equal to 8: the transaction of computer A was not validated but the computer B see the quantity in stock as if it was validated.
  • The computer A has two solutions:
    • Validate the transaction (COMMIT). In this case, the quantity in stock is equal to 8.
    • Cancel the transaction (ROLLBACK). In this case, the quantity in stock is equal to 10.
  • As long as the transaction was neither validated nor canceled, the computer B saw 8 in stock, which is false if the transaction is canceled.

Example: Modifying a customer

  • A computer A modifies a record in a transaction. In this transaction, "Ann" becomes "Juliet". The transaction is not validated.
  • A 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 is not validated (COMMIT), the computers ALWAYS read the initial data, which means before the modifications performed during the transaction (addition, modification, deletion).
The modifications performed during the transaction will be visible only when the transaction is validated (COMMIT).

Example: Stock management

Let's consider a PRODUCT file with a Prod01 product whose quantity in stock is equal to 10.
  • The computer A starts a transaction (HTransactionStart). The computer A modifies the Prod01 product and subtracts 2 from the quantity (HModify). The quantity in stock is equal to 8 but the transaction is not validated.The computer A starts a transaction (HTransactionStart or HTransaction). The computer A modifies the Prod01 product and subtracts 2 from the quantity (HModify). The quantity in stock is equal to 8 but the transaction is not validated.
  • The computer B is using the READ COMMITTED isolation mode and it reads the Prod01 product. The quantity in stock is equal to 10.
  • The computer A has two solutions:
    • Validate the transaction (COMMIT). In this case, the quantity in stock is equal to 8.
    • Cancel the transaction (ROLLBACK). In this case, the quantity in stock is equal to 10.
  • As long as the transaction was neither validated nor canceled, the computer B saw 10 in stock, as if the quantity in stock was not modified.

Example: Modifying a customer

  • A computer A modifies a record in a transaction. In this transaction, "Ann" becomes "Juliet". The transaction is not validated.
  • A 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 in its initial status (when the transaction was started), even if other computers have validated transactions that modify this data.
During the entire duration of transaction, a same computer reads a "snapshot" of database that was taken when starting the transaction, and not the data validated by the other computers.

Example: Stock management

Let's consider a PRODUCT file with a Prod01 product whose quantity in stock is equal to 10.
  • The computers A and B are using the REPEATABLE READ isolation mode.
  • The computer A starts a transaction (HTransactionStart). It reads the Prod01 product and the quantity is equal to 10.The computer A starts a transaction (HTransactionStart or HTransaction). It reads the Prod01 product and the quantity is equal to 10.
  • The computer B starts a transaction (HTransactionStart). It modifies the Prod01 product and subtracts 2 from the quantity (HModify). It validates its transaction. The stock is equal to 8.The computer B starts a transaction (HTransactionStart or HTransaction). It modifies the Prod01 product and subtracts 2 from the quantity (HModify). It validates its transaction. The stock is equal to 8.
  • When the transaction is validated by computer B (COMMIT), the computer A still reads the Prod01 product with a quantity equal to 10.
  • If the computer A validates its transaction, during the next read operation, the quantity in stock will correspond to 8. Indeed, the computer A will see the new value of the quantity in stock only when its transaction is validated.

Example: Modifying a customer

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

Implementing the REPEATABLE READ isolation mode

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