SQLite3 "database is locked"

[Lacak2]

When I run same application more than once on one computer (or from Windows network share from more computers) simultaneously, then when I try update and commit changes in one instance of application, I get error "database is locked" (SQLITE_BUSY). How to avoid this ?
 (see also http://www.sqlite.org/faq.html#q5)
I use sqldb (TSQLQuery+TSQLite3Connection)

[stonefull]

the FAQ answers your question. I think you should use another database.

[Batuhan]

Hi! check this link, (its user comment)

http://www.php.net/manual/en/pdo.begintransaction.php#90239

of course  this is an PHP example but you can easily convert it to FreePascal

[Lacak2]

Thanks for the tips. It seems, that if there is at least one explicit  "read" (select) transaction or at least one implicit transaction with prepared (unfinalized) statement, then SQLite3 holds shared lock on file, so another process can not acquire reserved (exclusive) lock and write updates.

Because I work with high level components sqlDB I can not control SQLite at API level (as required by example in PHP link above).
Any other ideas ?
Other components like TSqlite3Dataset also have this "problem"?

[vfclists]

The way to handle this then would be to add support for this to the TSQLite3Dataset.

It can't be that hard, can it?

I have come across that problem myself and I am considering FirebirdSQL if it will fix this issue, otherwise SQLite would be my preference.

[Lacak2]

The way to handle this then would be to add support for this to the TSQLite3Dataset.

It can't be that hard, can it?

It is question for author (LuizAmérico?) , if he will spend time and if it is doable ... but if possible it will be great.

[clauslack]

You can try Firebird 2.5

From
http://www.mindthebird.com/download/mtbweb20100406.pdf

Embedded engine uses new architecture
- Host applications may still have multiple connections to the same database
- Different host applications may safely access the same database simultanously
- Official utilities (gbak, gfix, etc) and 3rd party tools
(DBWorkbench, IBExpert, etc) can be used in parallel with your application as well.

Regards

[LuizAmérico]

I have an app that 10 instances (in different machines) access a sqlite datafile using TSqlite3DataSet.

The only thing i did is add an OnGetHandle event for each dataset and do small transactions, i.e., after each update/add/delete i call ApplyUpdates

procedure TDataCenter.GenericDatasetGetHandle(Dataset: TDataSet);
begin
  sqlite3_busy_timeout(TSqlite3Dataset(Dataset).SqliteHandle, 1500);
end;

[spitofland]

I have not had a chance to use SQLLite myself, but I have received advice that when using SQLLite you should run the query and then close the connection as soon as you can since SQLLite only allows one connection at a time. This may not prevent all conflicts, but it should help reduce the number of errors.

[gajahbengkak]

SQLite support multiple read transaction but only one write transaction at the same time so it is wise to close transaction immediately after finish writing to database.

[vrull]

I wrote an application that addresses this issue. It uses single connection, to deal with temporary tables, but closes transaction as soon as data are retrieved or saved.
You can download the source from https://sourceforge.net/projects/sqlmm/files/source-code/.
For the grids, it copies dataset into memory, so you can run multiple requests per application / instances.