sqlite unexpected access violations

[Чебурашка]

I analized a little the internals of our sqlite software, and I modified my software so that, while the tests are running there a dummy connection to another db that prevents the RefCount mechanism to load/unload the sqlite library every time connection is create/destroyed.

Problems disappear, and also changing the number of concurent threds to 100 there is no error.

Code: Bash  [Select][+][-]

1. [12:27:33] mep@mep-development:~/tmp/2024-02-26-threads-and-db$ ./project1
2. "SQLite3","3034001","3.34.1","libsqlite3.so","3034001"
3. "SQLite3","3034001","3.34.1","libsqlite3.so","3034001"
4. Heap dump by heaptrc unit of /home/mep/tmp/2024-02-26-threads-and-db/project1
5. 9611865 memory blocks allocated : 803625179/814465104
6. 9611865 memory blocks freed     : 803625179/814465104
7. 0 unfreed memory blocks : 0
8. True heap size : 131072
9. True free heap : 131072
10. [

Code: Pascal  [Select][+][-]

1. program project1;
2.  
3. uses
4.   cthreads,Classes, SysUtils,
5.   sqlite3conn, sqldb, FileUtil,
6.  
7.   Unit1;
8.  
9. // template-sqlite_db is a pre-existing sqlite db with only one table:
10. //
11. // CREATE TABLE Table1
12. // (
13. //   id integer,
14. //   v1 varchar(255),
15. //   text1 TEXT
16. // );
17.  
18. var
19.   connection: TSQLite3Connection;
20. begin
21.   connection := TSQLite3Connection.Create(nil);
22.   connection.DatabaseName := 'another-sqlite_db';
23.   connection.Connected := True;
24.   Writeln(connection.GetConnectionInfo(TConnInfoType.citAll));
25.  
26.   RunTheTest();
27.  
28.   connection.Connected := False;
29.   connection.Destroy();
30. end.
31.  

The problem is in the fpc sqlite software.

[TRon]

@Чебурашка:
I am not 100% convinced that the problem is in the sqlite components/package implementation.

It might be intended behaviour but I do not know.

That question should probably be answered by someone who implemented the classes.

At least opening the library in the main program thread solves your issue, so that is a relative easy fix do you agree ?

[Чебурашка]

@Чебурашка:
I am not 100% convinced that the problem is in the sqlite components/package implementation.

It might be intended behaviour but I do not know.

That question should probably be answered by someone who implemented the classes.

At least opening the library in the main program thread solves your issue, so that is a relative easy fix do you agree ?

The problem is related to the libsqlite3.so load/unload mechanism, that makes use of RefCount and InterlockedIncrement. I don't know exactly why but this is what happens when, like me one creates and destroys the connection obejcts every time they are used (btw it is not enough to create/destroy the connection obecjt, also Connected := True is necessary, because you have to go though the DoInternalConnect/DoInternalDisconnect couple).

The idea of keeping a connection created and opened is just a ugly workaround because I happened to come across this problem just because my threads are doing free wheel operations, but having threads doing operations just seldom does only make more unlikely the problem to happen, which is even worse because one can have an AV in just one year of execution and that would be extremely difficult to interpret (like all the concurrence problems of the world).

[TRon]

The problem is related to the libsqlite3.so load/unload mechanism, that makes use of RefCount and InterlockedIncrement.

Yes, that was clear from the results and the source-code. For/to me the implementation looks like it was implemented as intended. (again I can not tell for sure simply because I did not implement the classes  )

The idea of keeping a connection created and opened is just a ugly workaround because I happened to come across this problem just because my threads are doing free wheel operations, but having threads doing operations just seldom does only make more unlikely the problem to happen, which is even worse because one can have an AV in just one year of execution and that would be extremely difficult to interpret (like all the concurrence problems of the world).

Yes, but it is not about a connection. The connection makes sure that the sqlite library is opened in the main thread (and stays open during your RunTheTest procedure) and has such has a main program thread instance.

I solved it this way:

Code: Pascal  [Select][+][-]

1. ..
2. uses
3.   sqlite3dyn;
4. ..
5. begin
6.   InitializeSQLite;
7.   try
8.     writeln('sqlite version : ', sqlite3_libversion);
9.     writeln('is sqlite threadsafe : ', sqlite3_threadsafe);
10.  
11.     RunTheTest();
12.   finally
13.     ReleaseSQLite;
14.   end;
15. end;
16.  

[Zvoni]

You do realize, that instead of creating a "dummy"-Database (on Harddisk) as a workaround, you could use an "InMemory"-Database for that?

[Чебурашка]

I solved it this way:

I just tried the same.
It it like you say, not needed to open the connection, but just InitializeSQLite/ReleaseSQLite is enough.

Unfortunately InitializeSQLite is a deprecated method and therefore it might be removed in future.

Also it should be invesitigated why using the library like in the example raises the access violations, because the feeling is that who wrote the fp code wanted to implement a threadsafe refcounting mechanism. Why use interlockedincrement otherwise?

[TRon]

Unfortunately InitializeSQLite is a deprecated method and therefore it might be removed in future.

InitialiseSQLite is indeed 

Also it should be invesitigated why using the library like in the example raises the access violations, because the feeling is that who wrote the fp code wanted to implement a threadsafe refcounting mechanism. Why use interlockedincrement otherwise?

Unfortunately I can not answer that question.

[Чебурашка]

You do realize, that instead of creating a "dummy"-Database (on Harddisk) as a workaround, you could use an "InMemory"-Database for that?

Please see the following posts, it emerges that it is not even necessary to create dummy databases, just do a Init/Release of the library.

Anyway thanks for the suggestion, even if I don't see a significant advantage in doing the dummy db in memory rather than on the filesystem.

[Zvoni]

Unfortunately InitializeSQLite is a deprecated method and therefore it might be removed in future.

Wrong!

Look closely
From sqlite3.inc

Code: Pascal  [Select][+][-]

1. function InitializeSqliteANSI(const LibraryName: AnsiString = ''): Integer; //needed as TLibraryLoadFunction
2. function InitializeSqlite(const LibraryName: UnicodeString = ''): Integer;
3. function TryInitializeSqlite(const LibraryName: Unicodestring = ''): Integer;
4. procedure ReleaseSqlite; //needed as TLibraryUnLoadFunction
5.  
6. function InitialiseSQLite: Integer; deprecated;
7. function InitialiseSQLite(const LibraryName: UnicodeString): Integer; deprecated;
8.  

function InitializeSqlite(const LibraryName: UnicodeString = ''): Integer;

function InitialiseSQLite: Integer; deprecated;

[Чебурашка]

Wrong!

Thanks I didn't notice the difference, prob someone decided to force the American version, as for what I see British English uses also Initialise.

https://www.oxfordlearnersdictionaries.com/definition/english/initialize
https://www.wordreference.com/enit/initialize
https://dictionary.cambridge.org/it/dizionario/inglese/initialize

But Ok, lets not diverge from the topic, which is the most important thing for the fp-sqlite users

[Zvoni]

Wrong!

Thanks I didn't notice the difference, prob someone decided to force the American version, as for what I see British English uses also Initialise.

https://www.oxfordlearnersdictionaries.com/definition/english/initialize
https://www.wordreference.com/enit/initialize

Yeah, but i doubt it's going to be removed

sqlite3.inc

function InitialiseSQLite:integer;
begin
  result:=InitializeSqlite(SQLiteDefaultLibrary);
end;

[TRon]

Yeah, but i doubt it's going to be removed

Nope, because ... consistency  (actually I meant to link to this but there does not seem to be a individual link to initialization and finalization)

[TRon]

But Ok, lets not diverge from the topic, which is the most important thing for the fp-sqlite users

If you have strong feelings with regards the implementation being wrong then you can always opt to report the issue as a bug though I would advise to wait a little (a couple of days or so) in case a more knowledgeable person might be able to shed a light.

[Чебурашка]

If you ...

https://gitlab.com/freepascal.org/fpc/source/-/issues/40675

[Чебурашка]

If you ...

https://gitlab.com/freepascal.org/fpc/source/-/issues/40675

Closed!