SQLite3 wait for update - Solved (I hope). lazarus / 16-Databases ---------------------------------------------------------------------- chrnobel · 2019-12-30 14:06 Hi all. I am reopening an old question: QuoteAlbeit SQLite3 is a single user database, it is quite usable as a (smaller) multiuser database, if it primarily is going to be used for reads. However in my application I do have some writes which gives me concurrency problems, however they should be to overcome if just waiting a little while, as a typical transaction takes less than 100ms. But sometimes it goes wrong if trying to read at exactly the same time as a write is being committed, so is there some way to check the SQLite3connection for whether the database is locked, so instead of raising an error I just can wait eg. 100 ms, and then try again? I am not using SQLIte3connection any more, but Zeoslib, and in general it works perfectly ok. But in rare occasions I have a read so fast after a write/commit (we are talking about something like 1-2 ms) that I have a crash, because the database is locked. I am looking for at way to check if the database is locked, and if it is, then wait perhaps 5 ms and then try again. I do not consider WAL as an option, because it has other drawbacks, and I have not found any usable documentation for sqlite3_busy_timeout with regards to Zeoslib. So any clues how I can make a sort of exception handling, that checks whether the database is locked? ---------------------------------------------------------------------- GAN · 2019-12-30 20:01 Hi, try using ZConnection.AutoCommit:=True, of course you have to change a little your code. ---------------------------------------------------------------------- chrnobel · 2020-01-06 10:16 Quote from: GAN on December 30, 2019, 09:01:05 pmHi, try using ZConnection.AutoCommit:=True, of course you have to change a little your code. Could you elaborate a bit, there is hardly any documentation. Also I would prefer having some sort of generic method, where I just wait until the table is released, before I try again - in that way I can accept waiting maybe 100ms, and if the transaction is not completed within this time, the program shall ignore the update and send an message, but not go into an error state. ---------------------------------------------------------------------- Thaddy · 2020-01-06 11:24 Trunk has - on my request - added support for the locking issue. It is not fully complete yet, but this specific issue should be solved in trunk since about 10 months ago. I would advise to test with trunk and the standard sqlite3 support, not with zwhatever. You also need to have a little in-depth knowledge about all options that sqlite3 itself supports, not only the FPC side, otherwise you may run into the same trouble again.. ---------------------------------------------------------------------- GAN · 2020-01-06 20:30 Quotehttps://sourceforge.net/projects/zeoslib/files/documentation/Yes, the official documentation: https://sourceforge.net/projects/zeoslib/files/documentation/ QuoteAlso I would prefer having some sort of generic method, where I just wait until the table is released, before I try again - in that way I can accept waiting maybe 100ms, Then don't use autocommit. ---------------------------------------------------------------------- chrnobel · 2020-01-08 10:28 Quote from: Thaddy on January 06, 2020, 12:24:27 pmI would advise to test with trunk and the standard sqlite3 support, not with zwhatever. I strongly disagree - in general Zeoslib is really good and rocksteady, and it is much simpler to migrate an application from e.g SQlite to MySQL (or what ever SQL). ---------------------------------------------------------------------- chrnobel · 2020-01-08 10:39 Quote from: GAN on January 06, 2020, 09:30:18 pmThen don't use autocommit. Ahh, ok, the documentation you link to have an example like this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Connection. SetAutoCommit(False);Connection.SetTransactionIsolation(tiReadCommited );...try...Statement.ExecuteUpdate(...);Statement.ExecuteUpdate(...);. ..Statement.Commit;exceptStatement.Rollback;end; And if there is an exception, then try again after eg. 100 ms. Will make some tests ---------------------------------------------------------------------- chrnobel · 2020-03-18 16:23 I have ended up in doing it with exceptions instead. First I prepare my SQL sentence, and then I call the following procedure, instead of directly calling ExecSQL. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure execdata;var  lock:boolean;  maxtry:integer;begin  lock:=true;  maxtry:=0;  while (lock=true) and (maxtry<20) do  begin    try      lock:=false;      DefQuery.ExecSQL      except      on E: EZSQLException do      begin        if E.ErrorCode=5 then        begin          lock:=true;          sleep(50);          inc(maxtry);      end;      end;    end;  end;end;   Also I have found out, that some times I face a locking problem if I try to connect to a database at the very same time as at write is in progress, this (seems to) can be avoided by: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i DefConnection.Properties.Add('busytimeout=1000'); The only price for all this is that a write can be delayed typically some 50-100ms, but that is acceptable.