SQLite Transaction

[dominikkv]

Hi,

we have updated our project from Delphi 10.4, UniDAC 8.3.2 to Delphi 11, UniDAC 9.1.1. Now we get an ESQLiteError "database is locked" when opening a second connection to a database while the first connection is in an isolated transaction. Example:

Code: Select all

FirstConnection.StartTransaction(TCRIsolationLevel.ilIsolated);
try
  SecondConnection := TSQLConnection.Create();
  try
    SecondConnection.Database := '...';
    SecondConnection.Open(); // "database is locked"
  finnaly
    SecondConnection.Free;
  end;
except
  // ...
end;

WAL mode, non-DirectMode

-> Is this the right behaviour? I thought a connection to a database is possible, even when another connection is in a transaction. The WAL mode should even enable us to read data...
-> When ommitting the TCRIsolationLevel.ilIsolated parameter, it works. What does this parameter do in the SQLite world? Is this SQLite functionality, or does UniDAC implement a feature that is not available in plain SQLite?

Thanks for your answers!

Cheers
Dominik

[dominikkv]

Ok, found out that with UniDAC version 8.4.1 the following changelog is given:

* The LockingMode specific option in the Connection component is added
* The Synchronous specific option in the Connection component is added
* The JournalMode specific option in the Connection component is added
* Performance with default values of the new options is significantly improved

https://www.devart.com/unidac/revision_history.html

It seems that the defaults are LockingMode=lmExclusive and Synchronous=smOff. The LockingMode takes effect on the first write, and that seems to happen when starting an isolated transaction.

I am still interested in the SQLite equivalent of Connection.StartTransaction(TCRIsolationLevel.ilIsolated);
:-)

And, I am surprised that this change is not mentioned in the release notes. Am I right that the default settings changed from (slow, multi-connection possible, data safe on system crash) to (fast, only single-connection possible, database corrupted on system crash)?


Cheers
Dominik

[MaximG]

We changed the default settings for creating a connection to a database as we received many requests from our users to make a connection faster and remove initialization. Sorry for not including this information in the change logs.

[darianmillervas]

+1 for terrible idea of changing default behavior and not notifying users in the change log!