In my professional life I've worked mainly on mainframe DB2 (a very different beast to sqlite and possibly causing unjustified assumptions on my part). I have coded Delphi programs on Windows using the BDE and Absolute database. I recently started exploring Lazarus and sqlite using Zeos.

I'm running Windows 11 64 bit. Both Lazarus and sqlite are 64 bit versions.

As part of my testing I created a sqlite database with two tables and coded up a Lazarus program to load data into them.

The load looks like this:

FUNCTION add_img(dir_no : integer; img_name, ftype, fsize : string) : integer;
BEGIN
IF inum = -1 THEN BEGIN
    datamod.dmHuffin.ZConnection1.StartTransaction;
    datamod.dmHuffin.ZQ_getmax.SQL.clear;
    datamod.dmHuffin.ZQ_getmax.SQL.Add('SELECT COALESCE(max(img_num),0) AS m' );
    datamod.dmHuffin.ZQ_getmax.SQL.Add('FROM image' );
    datamod.dmHuffin.ZQ_getmax.Open;
    IF datamod.dmHuffin.ZQ_getmax.FindFirst THEN
      inum := datamod.dmHuffin.ZQ_getmax.FieldByName('m').AsInteger;
    datamod.dmHuffin.ZQ_getmax.Close; 
    datamod.dmHuffin.ZConnection1.Commit;
  END;
  inc(inum); 
    datamod.dmHuffin.ZConnection1.StartTransaction;
// The SQL text is 'INSERT INTO image (<list of columns>) VALUE (:dir_num,...) ;'
  WITH datamod.dmHuffin.ZQ_insert_img DO BEGIN
    ParamByName('dir_num').AsInteger := dir_no;
    ParamByName('img_num').AsInteger := inum;
    ParamByName('img_name').AsString := img_name;  
    ParamByName('img_suffix').AsString := ExtractFileExt(img_name);
    ParamByName('img_type').AsString := ftype;
    ParamByName('img_size_kb').AsInteger := strip(fsize);
    ExecSQL;
  END;  
    datamod.dmHuffin.ZConnection1.Commit;
  result := inum;
END;               

For the first table this worked fine and loaded rows as expected. But when I extended the program and tried to do the same thing for the second the insert fails with a "Database Locked" error.

This all leaves me very puzzled. The documentation I could find says that you get locking when two processes try and access the database at the same time. But my code is very simple, no multithreading, and I issue a Close on the read SQL before trying the insert. The database is on the C: drive so there's no NAS concurrency issues. I've tried adding StartTransaction and Commit commands around both the SELECT and INSERT statements but to no avail.

I also can't understand why T1 can load without issue but T2 has problems. The code to load T2 was basically a cut and paste of the T1 code with the field names changed.

I'm out of ideas on how to proceed. Any help greatly appreciated.

[[Edited to markdownify the large code block.]]

Did you close the query after the first one was done?

I'm very familiar with FPC and lazarus, but not with Zeos, so not sure what it does under the hood. Just musing from my experience, in SQLite, you need to finish a transaction or close a current query before starting the next. (A query which is not opened within an explicit transaction is a transaction unto itself).

I'm supposing your Zeos machinery would have some way of doing that.

As an aside - I use two distinct ways of accessing SQLite DBs in FPC, the first is the bog standard TSQLQuery tooling, works a treat (and the same way as MySQL, Postgres, MSSQL, etc. variations). I can give soem examples of this if needed.

The second method is a custom-made wrapper of the SQLite library (which works on any platform) and is quite fast and versatile. You can see it at work in the SQLiteSpeed tool and I'm willing to share it if you contact me directly on ryansmithhe at gmail dot com.

Hi Ryan, Yes, I make sure I close any query after I'm finished with it. And in the program's current state I only have one query open at a time. I would have thought that closing a query would release any locks associated with the query but I'm not really sure. I have a good idea of how locking works under DB2 but despite the most monk like study of the sqlite documentation I still cannot wrap my head around the way sqlite does locking. Locking a database is a meaningless idea under DB2 - the largest object you can lock there is a table (technically a tablespace) I can try wrapping every SQL statement in a transaction and issuing a commit after every close but this is just flailing around - I don't really understand what's going on. I can try recoding the program not to use Zeos but I really like to get an understanding of just what the issue is.

Well, there's only one thing I can say:

Locking in SQLite works spectacularly easy and is not usually a problem, which is why I was suggesting that maybe Zeos is the problem, or you are not closing the query (which you confirmed you did indeed do). Further tot his you report seeing "Database Locked" errors, which is not really something SQLite would say (it has a "Database Busy" error) which makes me think that Zeos may be doing some other locking on top of the DB or such.

The reason locking seems weird is that it very much depends on the journal mode. I'll try explain it in broad non-technical terms for the basic journal modes, but note that WAL mode works a bit different.

If a transaction (or even single statement) executes, it has to ensure the statement sees a consistent view of the data for the duration, so it typically acquires a read-lock on the DB (how exactly this happens depends very much on the underlying Virtual File-System interface, or VFS for short). Once a read-lock is held, other queries can be started that also read the data, but not one that wishes to write data (such a request will get the "Database Busy" error). If the same transaction (which holds the read-lock) wishes to start writing, it should be able to upgrade to a write-lock once other readers have completed, before which the error will also be returned, and after which, other readers have to wait, and if they do try to start a read operation at this point, will get the "Database Busy" error. (Again, WAL mode works different and allows concurrent readers while writing, and some of the above workings can be adjusted with pragmas).

It is thus imperative for both read and write transactions to finish as fast as possible and when mixing and matching from different connections, to expect the occasional "Busy" signal back. Also, there is nothing wrong with getting a Busy signal back, it's merely a state and you should be handling it by simply waiting some time and trying again.

You could also have SQLite do that automagically by specifying a Busy-timeout which would simply make it, when encountering a "Busy" signal, wait a bit in progressively larger increments until no longer busy, or until the timeout milliseconds expire, whichever happens first. It's not uncommon to set timeouts of one or two minutes for big DBs.

Lastly - NONE of the above matters if the DB enters a permanent locked state, which is usually the result of programmer error, not closing a statement, or encountering a real dead-lock (which is hard to engineer, but can happen). That is why our first responses were along the lines of "Are you sure you close the statements?"

I'll also mention that you can search the forums and check, but nobody really battles with DB locking errors (unless OS induced), it just works well, so to encounter someone who has trouble with it, one has to assume a fundamental mistake. The biggest trouble is that seemingly none of us know Zeos well - it may also be useful to ask this on the Lazarus forum.

Ok, program is working now. Apparently I needed to add a Commit after every SQL close. I'm a bit puzzled why this should be necessary but at least we can move forward. Thanks to everyone who offered help.

As an aside - I use two distinct ways of accessing SQLite DBs in FPC, the first is the bog standard TSQLQuery tooling, works a treat (and the same way as MySQL, Postgres, MSSQL, etc. variations). I can give soem examples of this if needed.

I am also working on Lazarus and accessing SQLite database. I am using the provided TSQLite3Connection. I would appreciate having some examples as you mention so that I can see whether I'm doing things right. Especially error handling (retrying a busy state) would be of interest to me.

Sure thing, but that is quite off-topic on this forum, so kindly send an email directly to the address mentioned (ryansmithhe at gmail dot com) and I'll be happy to share.

Maybe you have a TZConnection/TZQuery opened in the lazarus designer, and that's what's locking the database.

Hi Luca, I have the TZConnection opened in designer. That's normal when developing a program so the IDE can get at the database's metadata. The 'Active' property for all queries is Off in the IDE

Well, that was just a thought, since it's the only problem I remember while using zeos with sqlite. Did you try closing the TZConnection and restarting Lazarus? Or playing with the DesignConnection property (I never remember if it should be true or false)?. Also check the TransactionIsolationLevel, by default is tiNone so it shouldn't keep the database locked (unless you manually start a transaction IIRC).