SQLite3 "database is locked"
lazarus / 16-Databases

----------------------------------------------------------------------
Lacak2 · 2010-05-24 03:26

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 · 2010-05-24 04:49

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

----------------------------------------------------------------------
Batuhan · 2010-05-24 11:01

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 · 2010-05-25 22:06

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 · 2010-06-01 02:20

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 · 2010-06-02 22:04

Quote from: vfclists on June 01, 2010, 04:20:19 amThe 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 · 2010-06-11 07:07

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 · 2010-06-17 12:42

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 · 2010-06-21 04:26

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 · 2016-10-28 18:44

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 · 2016-11-08 02:22

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.