|
|
|
|
|
|
|
|
|
|
- Overview
- READ UNCOMMITTED isolation level
- Principle
- Example: Stock management
- Example: Modifying a customer
- Implementing the READ UNCOMMITTED isolation level
- READ COMMITTED isolation level
- Principle
- Example: Stock management
- Example: Modifying a customer
- Implementing the READ COMMITTED isolation level
- REPEATABLE READ isolation level
- Principle
- Example: Stock management
- Example: Modifying a customer
- Implementing the REPEATABLE READ isolation level
Client/Server transactions: available isolation modes
 Available only with this kind of connection
The HFSQL engine offers transaction isolation: modifications made in a current transaction are isolated from those made in other transactions running simultaneously, until the transaction is validated. Several isolation levels are available: This help page presents the different isolation levels and their implementation. READ UNCOMMITTED isolation level 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 consider a PRODUCT data file with an item Art01 with a stock quantity of 10. - 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 level and reads "Item01". Quantity in stock is 8: item A's transaction has not been validated, yet item B sees the quantity in stock as if it had been validated.
- 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 level To implement the READ UNCOMMITTED isolation level, you must: READ COMMITTED isolation level 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 consider a PRODUCT data file with an item Art01 with a stock quantity of 10. - 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 level 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 level To implement the READ COMMITTED isolation level, you must: REPEATABLE READ isolation level 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 consider a PRODUCT data file with an item Art01 with a stock quantity of 10. - Computers A and B use the REPEATABLE READ isolation level.
- 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 level To implement the REPEATABLE READ isolation level, you must: - Using HFSQL data files in Client/Server mode.
- Use an HFSQL server in version 19 (or later).
- Activate REPEATABLE READ mode in each transaction analysis data file. This option is available in the "Details" tab of the data file description window. An automatic modification of the data files is required to take this parameter into account. Warning: if this option is enabled, the data file is incompatible with versions 18 and earlier.
- Call HTransactionIsolation with the hRepeatableRead constant.
- Use syntaxes that handle connections in transaction functions (HTransactionStart, HTransaction, HTransactionEnd and HTransactionCancel).
Tip To find out whether REPEATABLE READ mode is activated on a data file, use the RepeatableReadSupported property.
This page is also available for…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|