Changes to FireDAC Transaction Isolation Configuration in RAD Studio 10.4
2020-07-29 by Marco Cantu
2020-07-29 by Marco Cantu
[SHOWTOGROUPS=4,20,22]One of the default setting for FireDAC transactions configuration has been changed in 10.4, and that might require some changes in your code.
Before RAD Studio 10.4, the default of transaction isolation option for FireDAc connections was read committed. That is TFDConnection.TxOptions.Isolation was xiReadCommitted. This was the value set for the component, and being the default it wasn't send to the database. FireDAC just assumed the default value in its configuration was the default of the database and it did not explicitly send this isolation configuration to the database at the start of a session. For example, in MySQL the required command is SET SESSION TRANSACTION ISOLATION LEVEL was not executed if the default wasn't modified. In this case, the MySQL transaction isolation was remaining to the database default value, which is xiRepeatableRead, no matter what was set in the FireDAC default configuration.
To fix this issue we decided that is was better to keep the default isolation level to xiUnspecified, meaning that if you won't require a specific isolation level, the default configuration for the specific database is used -- this is the one already preset int he database and you don't need to ask configuring. The default isolation level is optimized for specific database, as the defaults are different and some of the isolation levels are not even supported by all database engines equally well.
If a developer wants to use an isolation level different from the default used by the database, it must be set explicitly in the component configuration or in code. If the developer wants to the use the database default, no code is required.
Here are default isolation levels of the main databases in FireDAC terms -- again the property value is just xiUnspecified:
[/SHOWTOGROUPS]
Before RAD Studio 10.4, the default of transaction isolation option for FireDAc connections was read committed. That is TFDConnection.TxOptions.Isolation was xiReadCommitted. This was the value set for the component, and being the default it wasn't send to the database. FireDAC just assumed the default value in its configuration was the default of the database and it did not explicitly send this isolation configuration to the database at the start of a session. For example, in MySQL the required command is SET SESSION TRANSACTION ISOLATION LEVEL was not executed if the default wasn't modified. In this case, the MySQL transaction isolation was remaining to the database default value, which is xiRepeatableRead, no matter what was set in the FireDAC default configuration.
To fix this issue we decided that is was better to keep the default isolation level to xiUnspecified, meaning that if you won't require a specific isolation level, the default configuration for the specific database is used -- this is the one already preset int he database and you don't need to ask configuring. The default isolation level is optimized for specific database, as the defaults are different and some of the isolation levels are not even supported by all database engines equally well.
If a developer wants to use an isolation level different from the default used by the database, it must be set explicitly in the component configuration or in code. If the developer wants to the use the database default, no code is required.
Here are default isolation levels of the main databases in FireDAC terms -- again the property value is just xiUnspecified:
- DB2 - xiReadCommitted
- InterBase and Firebird - xiSnapshot
- MySQL and MariaDB - xiRepeatableRead
- Oracle - xiReadCommitted
- Microsoft SQL Server - xiReadCommitted
- SQLite - xiSerializible
- PostgreSQL - xiReadCommitted
[/SHOWTOGROUPS]