Strange sqlite sigsev
lazarus / 63-Database

----------------------------------------------------------------------
Чебурашка · 2024-02-23 20:14

I have a software with two threads doing operations on sqlite
databases.

The strange fact is that the two threads do not operate on the same
sqlite database, but on different databases. And the connection
objects used are local to each thread (actually created and destroyed
on the stack of the running method, not even global to the thread), so
non sharing nor concurrent access is occurring.

Nevertheless I get random SIGSEV when doing conn.Connected := True;

Code: Bash  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i [Current
thread is 1 (Thread 0xec2ffb40 (LWP 13364))](gdb) where#0  0x00000000
in ?? ()#1  0xeb5324de in ?? () from /usr/bin/mep/libsqlite3.so#2
 0xed0b5cae in
SQLITE3CONN$_$TSQLITE3CONNECTION_$__$$_DOINTERNALCONNECT () from
/usr/bin/mep/libMEPPanelDataExporter-i386-linux.so#3  0xed0607a1 in
DB$_$TDATABASE_$__$$_DOCONNECT () from
/usr/bin/mep/libMEPPanelDataExporter-i386-linux.so#4  0xecfbf151 in
SQLDB$_$TSQLCONNECTION_$__$$_DOCONNECT () from
/usr/bin/mep/libMEPPanelDataExporter-i386-linux.so#5  0xed061c86 in
DB$_$TCUSTOMCONNECTION_$__$$_SETCONNECTED$BOOLEAN () from
/usr/bin/mep/libMEPPanelDataExporter-i386-linux.so Line "#6
 0xed0abef4" was doing  conn: TSQLConnection;conn.Connected :=
True; #6  0xed0abef4 in TDBIOCONNECTION__CONNECTIONOPEN
(this=0xeca035e0) at /home/mep/dev/src/fp/MEP-
poleis/components/db/Poleis.Components.DB.Toolkit.pas:1459#7
 0xed0a77f3 in TDBIOKIT__CONNECTIONOPEN (this=0xec489080) at
/home/mep/dev/src/fp/MEP-
poleis/components/db/Poleis.Components.DB.Toolkit.pas:564#8
 0xed0a7ae4 in TDBIOKIT__CONNECTANDSTARTTRANSACTION_SQLITE
(SQLITEDATABASEFILENAME=0xed73238c "/mnt/mep/libMEPPanelDataExporter-
tmpfs/transmission_register_db", this=0xec489080) at
/home/mep/dev/src/fp/MEP-
poleis/components/db/Poleis.Components.DB.Toolkit.pas:627#9
 0xed11600f in LOCALRUNCYCLE_DOCYCLE (SQLITEDB=0xed73238c
"/mnt/mep/libMEPPanelDataExporter-tmpfs/transmission_register_db",
HADERROR=false, LASTATTEMPTEDNO=0, LASTSUCCESSFULNO=0,
parentfp=0xec2ff078) at /home/mep/dev/src/fp/MEP-
poleis/processing/Poleis.Processing.HandleRemoteForwarding.pas:545#10
0xed115bc1 in LOCALRUNCYCLE_MAKENORMAL (parentfp=0xec2ff078) at
/home/mep/dev/src/fp/MEP-
poleis/processing/Poleis.Processing.HandleRemoteForwarding.pas:691#11
0xed1159ef in TREMOTESENDINGTHREAD__LOCALRUNCYCLE (this=0xeca8a080) at
/home/mep/dev/src/fp/MEP-
poleis/processing/Poleis.Processing.HandleRemoteForwarding.pas:757#12
0xed10d163 in MEPTHREADIOEXECUTE_TRYEXECHOOK (LOGGER=...,
HOOK=0xed1159c0 <TREMOTESENDINGTHREAD__LOCALRUNCYCLE>,
HOOKNAME=0xed272b0c "OnRunCycle", THREADNAME=0xed2738a0
"TRemoteSendingThread", RESULT=true, MESSAGE=0x0) at
/home/mep/dev/src/fp/MEP-poleis/components/threads/Poleis.Components.T
hreads.ThreadIO.pas:195#13 0xed10d833 in MEPTHREADIOEXECUTE_EXECHOOK
(LOGGER=..., HOOK=0xed1159c0 <TREMOTESENDINGTHREAD__LOCALRUNCYCLE>,
HOOKNAME=0xed272b0c "OnRunCycle", THREADNAME=0xed2738a0
"TRemoteSendingThread") at /home/mep/dev/src/fp/MEP-poleis/components/
threads/Poleis.Components.Threads.ThreadIO.pas:264#14 0xed10e990 in
MEPTHREADIOEXECUTE_LOOP (PARAM=0xeca8a080, LOOPMODE=MFREE,
MAXLOOPCYCLES=0, MAXPROCESSEDMESSAGES=0, EXITASRESTARTED=false,
EXITASTERMINATED=false) at /home/mep/dev/src/fp/MEP-poleis/components/
threads/Poleis.Components.Threads.ThreadIO.pas:630#15 0xed10ebdc in
MEPTHREADIOEXECUTE (PARAM=0xeca8a080) at /home/mep/dev/src/fp/MEP-pole
is/components/threads/Poleis.Components.Threads.ThreadIO.pas:859#16
0xecf5140e in CTHREADS_$$_THREADMAIN$POINTER$$POINTER () from
/usr/bin/mep/libMEPPanelDataExporter-i386-linux.so#17 0xef44b27a in
start_thread () from /lib/i386-linux-gnu/libpthread.so.0#18 0xef370366
in clone () from /lib/i386-linux-gnu/libc.so.6 
Is it possible that the sqlite.so does not like at all thread
concurrence? Any other clue?

----------------------------------------------------------------------
Thaddy · 2024-02-23 20:54

Not any other, but the single clue: You must run Sqlite inside the
threads, so two instances.

Note you can avoid that if you are able to compile SQLite yourself
from source. On the SQLite.org website are instructions to compile it
thread safe. At the cost of considerable speed loss.

----------------------------------------------------------------------
Чебурашка · 2024-02-23 20:59

Quote from: Thaddy on February 23, 2024, 09:54:16 pmNot any other, but
the single clue: You must run Sqlite inside the threads, so two
instances.

As I said, it is what I currently do.

I open conn inside a thread (local stack variable), I work on conn,
then I close it. No globals, also each conn points to a different
sqlite db file.

BTW, what do you intend to say when you say 2 instances? I geuss you
refer to the instances of the connection object, isn't it?

----------------------------------------------------------------------
Чебурашка · 2024-02-23 21:01

I also modified code so that, after conn creation, it sets the
OpenFlags to FULL_MUTEX.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i      
Result := TSQLite3Connection.Create(nil);     
TSQLite3Connection(Result).OpenFlags :=
TSQLite3Connection(Result).OpenFlags + [sofFullMutex]; 
But I get same error.

----------------------------------------------------------------------
Zvoni · 2024-02-26 08:40

First check, how your Lib is compiled
https://www.sqlite.org/threadsafe.html

That said: If each thread has its own connection, transaction and
TSQLQuery, then you're accessing SQLite actually in Single-Thread
mode.
sofFullMutex is only needed if you have concurrent access via a single
Connection

----------------------------------------------------------------------
Чебурашка · 2024-02-26 13:08

I create a list of sqlite databases, all equal named template-
sqlite_db_1, template-sqlite_db_2, ..., template-sqlite_db_10,
with just one table:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE Table1(    id INTEGER,    v1 VARCHAR(255) ); 
and execute this program

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
project1; uses  cthreads,Classes, SysUtils, sqlite3conn, sqldb,
FileUtil, syncobjs; var  sync: TCriticalSection;  identifier:
Integer;  gotit: Boolean;  ended: Integer;  procedure
Insert(dbIdentifier: Integer; cycle: Integer);var  connection:
TSQLite3Connection;  transaction: TSQLTransaction;  q: TSQLQuery; 
step: Integer;begin  step := 0;  try    connection :=
TSQLite3Connection.Create(nil);    Inc(step);    try     
connection.DatabaseName := 'sqlite_db_' + IntToStr(dbIdentifier);    
Inc(step);            transaction := TSQLTransaction.Create(nil);    
Inc(step);      try        transaction.SQLConnection := connection;  
    Inc(step);                transaction.StartTransaction();       
Inc(step);                q := TSQLQuery.Create(nil);       
Inc(step);        try          q.SQLConnection := connection;        
 Inc(step);          q.Transaction := transaction;         
Inc(step);                    q.SQL.Add('insert into Table1 (id, v1)
values ('+ IntToStr(dbIdentifier) + ', ''value' +
IntToStr(dbIdentifier) + ''')');          Inc(step);                 
 try            q.ExecSQL();            Inc(step);               
transaction.Commit();            Inc(step);            Sleep(1); //
INNER LOOP SLEEP          except            on E: Exception do       
    begin              transaction.Rollback();                       
   Writeln('Internal error ' + IntToStr(dbIdentifier) + ' ' +
E.Message)                          end;          end;        finally 
        FreeAndNil(q);        end;      finally       
FreeAndNil(transaction);      end;    finally     
FreeAndNil(connection);    end;  except    on E: Exception do    
begin      Writeln('Internal error unhandled ' +
IntToStr(dbIdentifier) + ' cycle ' + IntToStr(cycle) + 'step ' +
IntToStr(step) + ' ' + E.Message);            raise;    end; 
end;end;  function TExec(parameter : pointer) : ptrint;var 
localIdentifier: Integer;  i: Integer;begin  if parameter <> nil then 
  Result := 0      else    Result := 0;    try    localIdentifier :=
identifier;      gotit := True;                      // Make sure
databases are reset to original empty status    if
FileExists('sqlite_db_' + IntToStr(localIdentifier)) then
DeleteFile('sqlite_db_' + IntToStr(localIdentifier));    if
FileExists('sqlite_db_' + IntToStr(localIdentifier) + '-journal') then
DeleteFile('sqlite_db_' + IntToStr(localIdentifier) + '-journal');   
CopyFile('template-sqlite_db_' + IntToStr(localIdentifier),
'sqlite_db_' + IntToStr(localIdentifier));              i := 0;   
repeat      Insert(localIdentifier, i);      Inc(i);    until i >
1000; // NUMBER OF REPETITIONS   except    on E: Exception do    
begin      Writeln('External error on ' + IntToStr(localIdentifier) +
' ' + E.Message)    end;  end;    sync.Acquire();  try    Inc(ended); 
finally    sync.Release();  end;      EndThread(0);end; begin  sync :=
TCriticalSection.Create();    // Threads do not exist yet, so no need
of semaphore to init ended  ended := 0;    identifier := 1 ; gotit :=
False; BeginThread(@TExec); while (not gotit) do Sleep(100); 
identifier := 2 ; gotit := False; BeginThread(@TExec); while (not
gotit) do Sleep(100);  identifier := 3 ; gotit := False;
BeginThread(@TExec); while (not gotit) do Sleep(100);  identifier := 4
; gotit := False; BeginThread(@TExec); while (not gotit) do
Sleep(100);  identifier := 5 ; gotit := False; BeginThread(@TExec);
while (not gotit) do Sleep(100);  identifier := 6 ; gotit := False;
BeginThread(@TExec); while (not gotit) do Sleep(100);  identifier := 7
; gotit := False; BeginThread(@TExec); while (not gotit) do
Sleep(100);  identifier := 8 ; gotit := False; BeginThread(@TExec);
while (not gotit) do Sleep(100);  identifier := 9 ; gotit := False;
BeginThread(@TExec); while (not gotit) do Sleep(100);  identifier :=
10; gotit := False; BeginThread(@TExec); while (not gotit) do
Sleep(100);   while True do   begin    sync.Acquire();    try      if
ended >= 10 then        Exit;    finally      sync.Release();    end; 
      Sleep(1);  end;  Writeln('ended');    Sleep(100);   
sync.Destroy();end. 

Result: no error, even if I change number of repetions and remove the
inner loop sleep.

Very strange.

----------------------------------------------------------------------
Чебурашка · 2024-02-26 13:23

Quote from: Zvoni on February 26, 2024, 09:40:43 amThat said: If each
thread has its own connection, transaction and TSQLQuery, then you're
accessing SQLite actually in Single-Thread mode.

Thanks, the guess is that the error is probably elsewhere then, and
likely not sqlite/db related.