Posts and Reads in thread and mainthread

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Posts and Reads in thread and mainthread

Post by LHSoft » Wed 08 Jun 2016 14:47

Hello,
I have 2 Databases, one of them is only used in a thread and therefore in locking mode exclusive. No problem here.
the 2nd is used by main thread and thread: the thread writes a integer value (1 or 2), the mainthread requests this value.
the thread and mainthread have their own tables to use, the table in mainthread is set to "read only"
both databases are set Options.Direct := True; Options.UseUnicode := True; Options.ReadUncommitted := False;

The table is closed in Mainthread.
To read the value I do:

Code: Select all

TbSwitch.Open;
TbSwitch.Refresh;
case TbSwitchSwitch.AsInteger of
...
I always get the same value as the value the program started.
No Changes are registered.

Whats wrong?

best regards
Hans

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Posts and Reads in thread and mainthread

Post by MaximG » Fri 10 Jun 2016 08:12

Unfortunately, the information you provided is not enough for the problem investigation. Please compose a small sample demonstrating the issue and send it to maximg*devart*com . Specify the LiteDAC version you have installed.

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Posts and Reads in thread and mainthread

Post by LHSoft » Fri 10 Jun 2016 11:27

Hello,
maybe following information will take you closer to the problem:

Both, main thread and thread have their own database components.
(This is how I handle Databases in Thread in all other Databases like BDE or your SDAC, in SDAC a notification is enough to transfer changes between the threads).

But in LiteDAC if I want to read changes thread has done in main thread, it is not enough to just refresh the table or even reopen the table in main thread, I must close and reopen main database connection.
I have tried with a connection.applyupdates in thread too, no result.

I can avoid this if I make a "synchronize" and work with table and connection of main thread in thread, but this is only possible for very small tables in order not to loose the advantage of seperate threads.

If I use LiteSQL in thread to update 200.000 rows this updates arrive in main thread, but using a table and editing the one and only row does not arrive. funny.

So I use your latest version and Berlin

best regards
Hans

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Posts and Reads in thread and mainthread

Post by LHSoft » Fri 10 Jun 2016 11:32

PS:
I can send you whole Programm cause it is in first 2 weeks developement status and only database handling in it yet.
Please let me know, cause in this moment I have solved the problem with an application variable, cause it is not possible to transfer this small info between the threads (what should be).
So in this case I have to "redevelope" this part.

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Posts and Reads in thread and mainthread

Post by LHSoft » Sat 11 Jun 2016 09:42

Hello,
I have been testing a few hours and the results let me think that there could be a serious problem with intern structure of tables or database.
So I tried to make a small demo for you, but the hell, it worked fine :(

I checked this whole night and found out:
the occurrence of this error seems to depend on data size, maybe on transferred data or counts of rows or similar.
the demo only has one database (the origin 3 databases, 2 of them with about 250.000 rows in 5 tables), so in the demo the error occurs a few loops later (after about 4-6 loops) then in origin (here at first loop) (loop is one cycle of thread).
if reducing rows in tables to about 16.000 the error does not occur (or the program has to run longer for 1st occurrence).

And now the disturbing news:
This persits if program is new started. even if a very small import file is used updating only 3 rows.

Result: from the beginning the error does not occur. after some work it occurs. the error persits after restarting. This is for me:
There must be changes in database or table, causing this error.

I send you the packed demo.
The zip file has about 5MB (cause of 3 import text files you need), hope it arrives

best regards
hans

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Posts and Reads in thread and mainthread

Post by LHSoft » Sun 12 Jun 2016 08:11

Just got more problems in this theme:
same problem for all tables:
If updating Customer Table 1 (TbStmKun1), refreshing this table and switching from TbStmKun2 to TbStmKun1 no changes arrived until restart.

I must do an afterClose event of tables : connection.close ?????

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Posts and Reads in thread and mainthread

Post by MaximG » Tue 14 Jun 2016 14:05

We have received your sample and are now investigating it. At multi-threaded access to a SQLite database, use the Shared-Cache Mode setting: https://www.sqlite.org/sharedcache.html . You can control the Shared-Cache Mode parameter in LiteDAC with the Options.Options.EnableSharedCache property of the TLiteConnection component: https://www.devart.com/litedac/docs/?de ... dcache.htm

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Posts and Reads in thread and mainthread

Post by LHSoft » Tue 14 Jun 2016 15:07

OK, I'll try with this option again and will report
thanks
Hans

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Posts and Reads in thread and mainthread

Post by LHSoft » Wed 15 Jun 2016 00:51

Hello,
I have sent you a new test project with Options EnableSharedCache in main and thread Database set to true.
Now there is a new behavior: "If" no error the switches are running synchron.
BUT:
There are Errors "Locked Table" although I take care that the set of tables is closed in main thread when thread updates table. If set 1 in main thread is active thread will work with set 2 and opposite. So, although KaStmKun2 is closed there is a error message "KaStmKun2 is locked". I do not see where this locking is comming from.

In the demo project I get other tables locked then in my main program, but there I nearly have the same structure. In the Demoproject (with 1 instead 3 Databases), the lock comes in Table KaStmKun, this is later then in my main project, there the error is earlier with KaKonBD (maybe cause of Datasizes transferred).

Another difference between demo and main project:
in demo project the error persist, even if restarted, at once if 1 time occurred. This was till now the behavior in both, demo and main.
But now, after start main program allows 2 successful loops followed by infinite error loops.

Best regards
hans

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Posts and Reads in thread and mainthread

Post by LHSoft » Thu 16 Jun 2016 00:25

My daily report:
Now I take care, that the switch is not changed by thread (and so the Database set is not switched to the other set) as long as a tables in Mainthread are open. And I do not leave any table or query open in Mainthread (this might be a bit problematic, but in testing its OK), in tread all tables are closed if using table, mostly using LiteSQL for updating).
Now I do not get the lock message but message:
database disk image is malformed
If restarting, no message about malformed database is shown till the next events.

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Posts and Reads in thread and mainthread

Post by LHSoft » Thu 16 Jun 2016 07:52

Hello,
now the
database disk image is malformed
is permanent and also shown by third party tools. I'll try to get the error in my demoproject too.

I get this error after changes that take care, that all tables are closed and, within the tread, all SQLLite components are now created and freed for each loop.

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Posts and Reads in thread and mainthread

Post by LHSoft » Fri 17 Jun 2016 00:39

Hello,
hope I have some useful results for you:

First I implemented a REINDEX for all Tables at start.
When the error "database disk image is malformed" occurred and restarted, the Reindex tells me more details about the error:
UNIQUE constraint failed: KaLagKas2.Idx
So I tested with the autoincrements setting them to "unique conflict fail" and "not NULL conflict fail".
This was not successful but showed me a new error:
TLiteTables opening this tables can not insert unless "required" of Field[0] is deactivated. I have read in this forum, that this error is old and, if understood correctly, should have been fixed a few years ago.

But Back to the topic:
I develop my Program in 64bit.
accidentally I compiled it in 32bit and run the "stresstest" in 32bit: NO ERROR !!!
Running in 64bit: ERROR !!!
The error meanwhile takes a while (up to 5 minutes) but occurs 100%, but only if compiled in 64bit.
The 32bit Version now runs for hours in stresstest mode without error.

I'm using Delphi 10.1 Berlin.

Can you help me with this? Maybe your search is easier now.

best regards
Hans

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Posts and Reads in thread and mainthread

Post by MaximG » Mon 20 Jun 2016 10:37

We will study the issue and inform you about the results soon

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Posts and Reads in thread and mainthread

Post by LHSoft » Sun 26 Jun 2016 00:11

Hello,
I have just noticed a behavior I want to ask you if it is OK for SQLLite Database:
In my thread I start a transaction with the thread connection with option shareCache to update set1 while set2 is used.
in some circumstances I have to drop one of these tables of set1 and recreate it.
When executing a "drop table" within a transaction in thread, then both connections (thread and main connection) are locked and script aborts.
To do so successfully, I have to commit transaction before drop and start it again after drop.

best regards
Hans

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Posts and Reads in thread and mainthread

Post by LHSoft » Mon 27 Jun 2016 20:20

Hello,
should there be any improvements in today's version 2.7.24?
The result is the same: Databases are damaged in 64bit version after 2 or 3 loops, 32bit compiled program runs fine.
Hans

Post Reply