Recent

Author Topic: Strange sqlite sigsev  (Read 962 times)

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Strange sqlite sigsev
« on: February 23, 2024, 09:14:26 pm »
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][+][-]
  1. [Current thread is 1 (Thread 0xec2ffb40 (LWP 13364))]
  2. (gdb) where
  3. #0  0x00000000 in ?? ()
  4. #1  0xeb5324de in ?? () from /usr/bin/mep/libsqlite3.so
  5. #2  0xed0b5cae in SQLITE3CONN$_$TSQLITE3CONNECTION_$__$$_DOINTERNALCONNECT () from /usr/bin/mep/libMEPPanelDataExporter-i386-linux.so
  6. #3  0xed0607a1 in DB$_$TDATABASE_$__$$_DOCONNECT () from /usr/bin/mep/libMEPPanelDataExporter-i386-linux.so
  7. #4  0xecfbf151 in SQLDB$_$TSQLCONNECTION_$__$$_DOCONNECT () from /usr/bin/mep/libMEPPanelDataExporter-i386-linux.so
  8. #5  0xed061c86 in DB$_$TCUSTOMCONNECTION_$__$$_SETCONNECTED$BOOLEAN () from /usr/bin/mep/libMEPPanelDataExporter-i386-linux.so
  9.  
  10. Line "#6  0xed0abef4" was doing
  11.  
  12. conn: TSQLConnection;
  13. conn.Connected := True;
  14.  
  15. #6  0xed0abef4 in TDBIOCONNECTION__CONNECTIONOPEN (this=0xeca035e0) at /home/mep/dev/src/fp/MEP-poleis/components/db/Poleis.Components.DB.Toolkit.pas:1459
  16. #7  0xed0a77f3 in TDBIOKIT__CONNECTIONOPEN (this=0xec489080) at /home/mep/dev/src/fp/MEP-poleis/components/db/Poleis.Components.DB.Toolkit.pas:564
  17. #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
  18. #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
  19. #10 0xed115bc1 in LOCALRUNCYCLE_MAKENORMAL (parentfp=0xec2ff078) at /home/mep/dev/src/fp/MEP-poleis/processing/Poleis.Processing.HandleRemoteForwarding.pas:691
  20. #11 0xed1159ef in TREMOTESENDINGTHREAD__LOCALRUNCYCLE (this=0xeca8a080) at /home/mep/dev/src/fp/MEP-poleis/processing/Poleis.Processing.HandleRemoteForwarding.pas:757
  21. #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.Threads.ThreadIO.pas:195
  22. #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
  23. #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
  24. #15 0xed10ebdc in MEPTHREADIOEXECUTE (PARAM=0xeca8a080) at /home/mep/dev/src/fp/MEP-poleis/components/threads/Poleis.Components.Threads.ThreadIO.pas:859
  25. #16 0xecf5140e in CTHREADS_$$_THREADMAIN$POINTER$$POINTER () from /usr/bin/mep/libMEPPanelDataExporter-i386-linux.so
  26. #17 0xef44b27a in start_thread () from /lib/i386-linux-gnu/libpthread.so.0
  27. #18 0xef370366 in clone () from /lib/i386-linux-gnu/libc.so.6
  28.  

Is it possible that the sqlite.so does not like at all thread concurrence? Any other clue?
« Last Edit: February 23, 2024, 09:17:25 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

Thaddy

  • Hero Member
  • *****
  • Posts: 14352
  • Sensorship about opinions does not belong here.
Re: Strange sqlite sigsev
« Reply #1 on: February 23, 2024, 09:54:16 pm »
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.
« Last Edit: February 23, 2024, 10:03:07 pm by Thaddy »
Object Pascal programmers should get rid of their "component fetish" especially with the non-visuals.

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Strange sqlite sigsev
« Reply #2 on: February 23, 2024, 09:59:24 pm »
Not 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?
« Last Edit: February 23, 2024, 10:03:33 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

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Strange sqlite sigsev
« Reply #3 on: February 23, 2024, 10:01:17 pm »
I also modified code so that, after conn creation, it sets the OpenFlags to FULL_MUTEX.

Code: Pascal  [Select][+][-]
  1.       Result := TSQLite3Connection.Create(nil);
  2.       TSQLite3Connection(Result).OpenFlags := TSQLite3Connection(Result).OpenFlags + [sofFullMutex];
  3.  

But I get same error.
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: 2325
Re: Strange sqlite sigsev
« Reply #4 on: February 26, 2024, 09:40:43 am »
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
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: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Strange sqlite sigsev
« Reply #5 on: February 26, 2024, 02:08:15 pm »
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][+][-]
  1. CREATE TABLE Table1
  2. (
  3.     id INTEGER,
  4.     v1 VARCHAR(255)
  5.  
  6. );
  7.  

and execute this program

Code: Pascal  [Select][+][-]
  1. program project1;
  2.  
  3. uses
  4.   cthreads,Classes, SysUtils, sqlite3conn, sqldb, FileUtil, syncobjs;
  5.  
  6. var
  7.   sync: TCriticalSection;
  8.   identifier: Integer;
  9.   gotit: Boolean;
  10.   ended: Integer;
  11.  
  12. procedure Insert(dbIdentifier: Integer; cycle: Integer);
  13. var
  14.   connection: TSQLite3Connection;
  15.   transaction: TSQLTransaction;
  16.   q: TSQLQuery;
  17.   step: Integer;
  18. begin
  19.   step := 0;
  20.   try
  21.     connection := TSQLite3Connection.Create(nil);
  22.     Inc(step);
  23.     try
  24.       connection.DatabaseName := 'sqlite_db_' + IntToStr(dbIdentifier);
  25.       Inc(step);
  26.      
  27.       transaction := TSQLTransaction.Create(nil);
  28.       Inc(step);
  29.       try
  30.         transaction.SQLConnection := connection;
  31.         Inc(step);
  32.        
  33.         transaction.StartTransaction();
  34.         Inc(step);
  35.        
  36.         q := TSQLQuery.Create(nil);
  37.         Inc(step);
  38.         try
  39.           q.SQLConnection := connection;
  40.           Inc(step);
  41.           q.Transaction := transaction;
  42.           Inc(step);
  43.          
  44.           q.SQL.Add('insert into Table1 (id, v1) values ('+ IntToStr(dbIdentifier) + ', ''value' + IntToStr(dbIdentifier) + ''')');
  45.           Inc(step);
  46.          
  47.           try
  48.             q.ExecSQL();
  49.             Inc(step);
  50.    
  51.             transaction.Commit();
  52.             Inc(step);
  53.             Sleep(1); // INNER LOOP SLEEP
  54.           except
  55.             on E: Exception do
  56.             begin
  57.               transaction.Rollback();
  58.              
  59.               Writeln('Internal error ' + IntToStr(dbIdentifier) + ' ' + E.Message)
  60.              
  61.             end;
  62.           end;
  63.         finally
  64.           FreeAndNil(q);
  65.         end;
  66.       finally
  67.         FreeAndNil(transaction);
  68.       end;
  69.     finally
  70.       FreeAndNil(connection);
  71.     end;
  72.   except
  73.     on E: Exception do
  74.     begin
  75.       Writeln('Internal error unhandled ' + IntToStr(dbIdentifier) + ' cycle ' + IntToStr(cycle) + 'step ' + IntToStr(step) + ' ' + E.Message);
  76.      
  77.       raise;
  78.     end;
  79.   end;
  80. end;
  81.  
  82. function TExec(parameter : pointer) : ptrint;
  83. var
  84.   localIdentifier: Integer;
  85.   i: Integer;
  86. begin
  87.   if parameter <> nil then
  88.     Result := 0
  89.    
  90.   else
  91.     Result := 0;
  92.  
  93.   try
  94.     localIdentifier := identifier;  
  95.     gotit := True;        
  96.          
  97.     // Make sure databases are reset to original empty status
  98.     if FileExists('sqlite_db_' + IntToStr(localIdentifier)) then DeleteFile('sqlite_db_' + IntToStr(localIdentifier));
  99.     if FileExists('sqlite_db_' + IntToStr(localIdentifier) + '-journal') then DeleteFile('sqlite_db_' + IntToStr(localIdentifier) + '-journal');
  100.     CopyFile('template-sqlite_db_' + IntToStr(localIdentifier), 'sqlite_db_' + IntToStr(localIdentifier));
  101.          
  102.     i := 0;
  103.     repeat
  104.       Insert(localIdentifier, i);
  105.       Inc(i);
  106.     until i > 1000; // NUMBER OF REPETITIONS
  107.  
  108.   except
  109.     on E: Exception do
  110.     begin
  111.       Writeln('External error on ' + IntToStr(localIdentifier) + ' ' + E.Message)
  112.     end;
  113.   end;
  114.  
  115.   sync.Acquire();
  116.   try
  117.     Inc(ended);
  118.   finally
  119.     sync.Release();
  120.   end;
  121.    
  122.   EndThread(0);
  123. end;
  124.  
  125. begin
  126.   sync := TCriticalSection.Create();
  127.  
  128.   // Threads do not exist yet, so no need of semaphore to init ended
  129.   ended := 0;
  130.  
  131.   identifier := 1 ; gotit := False; BeginThread(@TExec); while (not gotit) do Sleep(100);
  132.   identifier := 2 ; gotit := False; BeginThread(@TExec); while (not gotit) do Sleep(100);
  133.   identifier := 3 ; gotit := False; BeginThread(@TExec); while (not gotit) do Sleep(100);
  134.   identifier := 4 ; gotit := False; BeginThread(@TExec); while (not gotit) do Sleep(100);
  135.   identifier := 5 ; gotit := False; BeginThread(@TExec); while (not gotit) do Sleep(100);
  136.   identifier := 6 ; gotit := False; BeginThread(@TExec); while (not gotit) do Sleep(100);
  137.   identifier := 7 ; gotit := False; BeginThread(@TExec); while (not gotit) do Sleep(100);
  138.   identifier := 8 ; gotit := False; BeginThread(@TExec); while (not gotit) do Sleep(100);
  139.   identifier := 9 ; gotit := False; BeginThread(@TExec); while (not gotit) do Sleep(100);
  140.   identifier := 10; gotit := False; BeginThread(@TExec); while (not gotit) do Sleep(100);
  141.  
  142.   while True do
  143.   begin
  144.     sync.Acquire();
  145.     try
  146.       if ended >= 10 then
  147.         Exit;
  148.     finally
  149.       sync.Release();
  150.     end;
  151.    
  152.     Sleep(1);
  153.   end;
  154.   Writeln('ended');
  155.  
  156.   Sleep(100);
  157.  
  158.   sync.Destroy();
  159. end.
  160.  


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

Very strange.

« Last Edit: February 26, 2024, 02:19:48 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

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Strange sqlite sigsev
« Reply #6 on: February 26, 2024, 02:23:05 pm »
That 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.
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