Strange sqlite sigsev

[Чебурашка]

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?

[Thaddy]

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.

[Чебурашка]

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?

[Чебурашка]

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.

[Zvoni]

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

[Чебурашка]

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.

[Чебурашка]

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.