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
I hope this helps clarify the change and explain how to work around it, changing the transaction isolation level of your FireDAC connection to the one you specifically need, if different from the database default. This change wasn’t listed in the release notes for RAD Studio 10.4 and it did cause some concerns (and bug reports).
Design. Code. Compile. Deploy.
Start Free Trial Upgrade Today
Free Delphi Community Edition Free C++Builder Community Edition
