Concurrent access

[unidac5src]

Hi,

we are using UniDAC 6.3.12 to access SQLite3 databases on Android with Delphi 10.1 Berlin. After a while, we added an Android Service which also writes data into the same database, also with UniDAC / Delphi, in a separate process.

Since then we encountered problems when the two processes tried to write at the same time. Access violations until app restart, corrupted database files. After we switched to WAL mode it is a whole lot better, but not gone completely. We have added the behavior that if an exception is thrown during commit, it tries to commit for two more times, but that didn't help.

As I can see in the SQLite FAQ, SQLite is capable of concurrent write access through file locking. The second process just waits until the first one unlocks the file, if the lock is not released in a specific time, SQLITE_BUSY is returned.

So my question is, is UniDAC able to handle concurrent write access? Are there any properties to set to make the component more concurrent access proof? Do you have any suggestions?

We are not using direct mode.


Thanks
Dominik

[MaximG]

Yes, indeed, the 'database is locked' error may occur in the situation you are describing. Its occurrence is due to the specificity of locking implementation in SQLite. According to the link provided by you, SQLite developers do not recommend to use a lot of competitive requests to the database in applications. This error handling must be implemented
at the application level, because a similar error can occur in other scenarios, which differ from yours, and the logic of its handling depends on the business logic of the particular application. For example, you can handle an error by wrapping a piece of code in the try ... except statement:

Code: Select all

uses LiteError;
...

try
...
except
  on E: ESQLiteError do 
    if (ESQLiteError(E).ErrorCode = 5 {SQLITE_BUSY}) or (ESQLiteError(E).ErrorCode = 517 {SQLITE_BUSY_SNAPSHOT}) then 
    begin
     ...
     end
  else
     raise;
  end;

You can get more detailed information about error codes in SQL official documentation: https://www.sqlite.org/rescode.html .

[dominikkv]

Hi Maxim,

thank you for your answer. We have already tried to catch the exception and redo the commit, in fact we catch it two times. The problem is that we do not get an ESQLiteError, but an AccessViolation. Once we get this exception, all other Database access result in this AV until the App is restarted.

So I think we'll test with a newer version of UniDAC to see if we still get the AV. Do you recommend using direct mode?


Thanks
Dominik

[MaximG]

Of course, you can use the Direct Mode in your project. To reproduce the described Access Violation error in our environment, please compose and send us a small separate project, in which the issue occurs. In addition, we will need a file with the SQLite test database used in this project. To do this, use the e-support form (https://www.devart.com/company/contactform.html)