Databaselock with SQlite table(s)

[Hansvb]

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]

after changing data in 1 grid the other grid went empty

Have 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]

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]

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]

@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]

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]

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....

SQLITE_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.