'Database is locked' when using 2 TLiteQuery

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
KajC
Posts: 8
Joined: Sun 30 Jun 2013 10:38

'Database is locked' when using 2 TLiteQuery

Post by KajC » Wed 09 May 2018 21:31

Hi,
I'm trying to hunt down a problem with using the same SQLite-database from more than one application. I made a small test app that has the same behavior/problem.

Setup:
A TForm with TLiteQuery, DBGrid, DBNavigator and TDBEdit:s.
I can instantiate multiple form-objects
All forms work with the same database and table.
TLiteConnection-settings are default except specifying direct read.
TLiteQuery-settings are default. Setting SELECT-statement in SQL-property.
TDataSource has AutoEdit=true.

If I run the application with a single instance of the form, then everything works.
If I instantiate 2 forms, they work with browsing the dataset, and I can modify the records in one of the instances. But as soon as I try to modify anything in the other instance I get a 'database is locked.' error.

It doesn't matter in which form I start editing first, the other is unable to modify any records.

I tried to replace TLiteQuery with TLiteTable, but I have the same behaviour.

Am I doing something wrong setting up the components?


Using:
RAD Studio 10.2 Tokyo (C++ project)
LiteDAC 3.1.3

KajC
Posts: 8
Joined: Sun 30 Jun 2013 10:38

Re: 'Database is locked' when using 2 TLiteQuery

Post by KajC » Fri 11 May 2018 06:03

Update;
Migrated to newer versions, but the problem remain.

Versions:
Rad Studio 10.2.2
LiteDAC 3.2.4

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: 'Database is locked' when using 2 TLiteQuery

Post by MaximG » Mon 14 May 2018 08:51

Please send us the source code of your small test app, execution of which causes the "database is locked" error. In addition, we will need a DDL script to create the database objects used in this sample, or a file with the SQLite test database. It is convenient to do this using the e-support form (https://www.devart.com the "Support"\"Request Support" menu)

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: 'Database is locked' when using 2 TLiteQuery

Post by LHSoft » Wed 23 May 2018 13:07

Hello,
I have the same Problems since changing my application (Newsletter) from other database to SQLite.
I do have up to 16 threads, each of them sets a boolen in a small database with about 4000 table rows to true.
On mainform with a TLiteTable I move through table and start this thread on each row. then moving next and so on.
As faster as the thread fulfills its work, the more Errors (database is locked) I do have.
If thread makes About 2000 rows / Minute there are about 500 Errors, if making 100 rows / Minute there are 5-20 Errors.

because this does work so bad, I have to do a trick:
I collect the IDs of successful rows in a StringList and Setting boolean to true after thread work is done. then there are no Errors left. But this is a very bad solution so could work with text databases then SQLite.

I had this subject 2 years ago but did never get an answer.
Lets look, if this time there will be presented a result.

Best regards
hans

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: 'Database is locked' when using 2 TLiteQuery

Post by MaximG » Thu 24 May 2018 11:13

This behavior is due to SQLite work specificity, and we cannot influence this behavior. In the case you are describing, SQLite will return the SQLITE_BUSY_SNAPSHOT error with the code 517. The detailed information about this error can be found in the official SQLite documentation: https://www.sqlite.org/rescode.html#busy_snapshot .

You can independently implement this error handling in your project, for example, as follows:

Code: Select all

uses LiteError, LiteCall;
...

try
...
except
  on E: ESQLiteError do 
     if ESQLiteError(E).ErrorCode = 517 then begin
       ...
     end
  else
    raise;
end;

Post Reply