Recent

Author Topic: sqlite unexpected access violations  (Read 4120 times)

Чебурашка

  • Hero Member
  • *****
  • Posts: 580
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: sqlite unexpected access violations
« Reply #15 on: February 29, 2024, 12:28:41 pm »
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.

FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

TRon

  • Hero Member
  • *****
  • Posts: 3510
Re: sqlite unexpected access violations
« Reply #16 on: February 29, 2024, 12:44:15 pm »
@Чебурашка:
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 ?
This tagline is powered by AI

Чебурашка

  • Hero Member
  • *****
  • Posts: 580
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: sqlite unexpected access violations
« Reply #17 on: February 29, 2024, 01:10:34 pm »
@Чебурашка:
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).

FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

TRon

  • Hero Member
  • *****
  • Posts: 3510
Re: sqlite unexpected access violations
« Reply #18 on: February 29, 2024, 01:18:23 pm »
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  :) )

Quote
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.  
« Last Edit: February 29, 2024, 01:24:33 pm by TRon »
This tagline is powered by AI

Zvoni

  • Hero Member
  • *****
  • Posts: 2720
Re: sqlite unexpected access violations
« Reply #19 on: February 29, 2024, 01:25:12 pm »
You do realize, that instead of creating a "dummy"-Database (on Harddisk) as a workaround, you could use an "InMemory"-Database for that?
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Чебурашка

  • Hero Member
  • *****
  • Posts: 580
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: sqlite unexpected access violations
« Reply #20 on: February 29, 2024, 01:25:23 pm »
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?
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

TRon

  • Hero Member
  • *****
  • Posts: 3510
Re: sqlite unexpected access violations
« Reply #21 on: February 29, 2024, 01:27:45 pm »
Unfortunately InitializeSQLite is a deprecated method and therefore it might be removed in future.
InitialiseSQLite is indeed  ;)

Quote
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.
This tagline is powered by AI

Чебурашка

  • Hero Member
  • *****
  • Posts: 580
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: sqlite unexpected access violations
« Reply #22 on: February 29, 2024, 01:28:03 pm »
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.
« Last Edit: February 29, 2024, 01:32:28 pm by Чебурашка »
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

Zvoni

  • Hero Member
  • *****
  • Posts: 2720
Re: sqlite unexpected access violations
« Reply #23 on: February 29, 2024, 01:30:30 pm »
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.  

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

function InitialiseSQLite: Integer; deprecated;
« Last Edit: February 29, 2024, 01:35:49 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Чебурашка

  • Hero Member
  • *****
  • Posts: 580
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: sqlite unexpected access violations
« Reply #24 on: February 29, 2024, 01:35:54 pm »
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
« Last Edit: February 29, 2024, 01:38:46 pm by Чебурашка »
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

Zvoni

  • Hero Member
  • *****
  • Posts: 2720
Re: sqlite unexpected access violations
« Reply #25 on: February 29, 2024, 01:38:30 pm »
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
Quote
function InitialiseSQLite:integer;
begin
  result:=InitializeSqlite(SQLiteDefaultLibrary);
end;
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

TRon

  • Hero Member
  • *****
  • Posts: 3510
Re: sqlite unexpected access violations
« Reply #26 on: February 29, 2024, 01:41:18 pm »
Yeah, but i doubt it's going to be removed
Nope, because ... consistency  ;D (actually I meant to link to this but there does not seem to be a individual link to initialization and finalization)
« Last Edit: February 29, 2024, 01:44:21 pm by TRon »
This tagline is powered by AI

TRon

  • Hero Member
  • *****
  • Posts: 3510
Re: sqlite unexpected access violations
« Reply #27 on: February 29, 2024, 01:49:52 pm »
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.
This tagline is powered by AI

Чебурашка

  • Hero Member
  • *****
  • Posts: 580
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

Чебурашка

  • Hero Member
  • *****
  • Posts: 580
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

 

TinyPortal © 2005-2018