Databaselock with SQlite table(s) lazarus / 16-Databases ---------------------------------------------------------------------- Hansvb · 2026-04-05 19:11 Hi, I have a database lock that I can't find. I have to make a mistake somewhere in the following. I have 2 datagrids on a Form, each with its own connection, transaction and query, to be provided with data. That works, but if I then want to expand 1 of both datasets with a new record, I get a datalock message. I have brought this back to a test project. See the attachment. This creates a SQLite database in the exe folder during startup and then creates 2 dummy tables and 1 record of data. If you then click on the btnAdd1 or btnAdd2 button, you will see the datalock message. I use express 2 connections to the database because otherwise after changing data in 1 grid the other grid went empty. How can I put 2 datagrids on a screen, each with their own dataset (no master detail). And the dataset per Dbgrid must be editable. ---------------------------------------------------------------------- LemonParty · 2026-04-05 19:33 Quoteafter changing data in 1 grid the other grid went emptyHave you consider to refresh your other dataset by closing and opening it? Why do you use 2 connections? It seems like one is enough for your needs. ---------------------------------------------------------------------- Sieben · 2026-04-05 20:09 Two connections in default mode will always lock each other because each will start a transaction on opening a dataset. The reason why your other grid went empty when using only one connection after changing data is again the default mode of SQLdb, which closes all tables of a connection after commit. You can prevent this by setting the option sqoKeepOpenOnCommit on your datasets or by using CommitRetaining. You really should (re)read the documention of SQLdb. ---------------------------------------------------------------------- cdbc · 2026-04-05 21:32 Hi Hans @Sieben is right. What I usually do is: keep both datasets in memory (like a ClientDataset), e.g.: TBufDataset, maybe then I write a middle-tier that funnels / sequences the changing of data to database one at the time, so _no_ locks... IIRC there should be something in my 'Diary' app's BOM, worth looking at... A middle-tier is like a Presenter, to put it in words you can relate to  It controls & orchestrates... Regards Benny ---------------------------------------------------------------------- Hansvb · 2026-04-06 07:47 @Sieben, thanks this will help me move forward. I now have 1 connection and 1 transaction and 2 tsqlqueries. If I now add a record to dbgrid 1, dbgrid 2 remains intact. @benny, I know..., I downloaded your 'Diary' app a while ago and still have to sit down and take a good look at it. My "problem" is that I still want to learn/read a lot but also want to see some progress in my trying projects. In short, always a kind of lack of time . Edit: added the attachment ---------------------------------------------------------------------- Xenno · 2026-04-06 09:40 You may want to take look on WAL for your database's journal mode. Although it does not prevent table lock, it might help for your case. I often use WAL to smoothen processing in separated connections. https://sqlite.org/wal.html ---------------------------------------------------------------------- Zvoni · 2026-04-07 06:45 Journal-Mode = WAL and pending Transactions have already been mentioned. I'd additionally take a look at the "OpenFlags" of the SQLite- Connection https://sqlite.org/c3ref/open.html "sofNoMutex" looks promising.... QuoteSQLITE_OPEN_NOMUTEX     The new database connection will use the "multi-thread" threading mode. This means that separate threads are allowed to use SQLite at the same time, as long as each thread is using a different database connection.