Recent

Author Topic: SQLite with SQLdb and Multithreading  (Read 15060 times)

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: SQLite with SQLdb and Multithreading
« Reply #15 on: August 25, 2018, 08:07:53 pm »
But how do I prevent problems of starting a transaction in several threads or closing it when another thread is still working with a  (e.g.) SELECT? So I close the transaction before all my current tasks are done, or?
You can have as much TSQLTransaction as you need for one single TSQLConnection in you application. if you use many TSQLTransaction, they do not need to see each other. It's an isolation level.

Quote
It does not cover what happens if you're on multithreading.
Code: Pascal  [Select][+][-]
  1. Connect.Open;
  2. Trans.StartTransaction;
  3. Connect.ExecuteDirect('insert some new stuff');
  4. Trans.Commit;
  5.  
but what happens if there is another thread calling .Commit because its done with it's SELECT statement? Or a TSQLQuery is done with it's work.
In my eyes there is a data race but that's not mentioned once in SQLdb tutorial or somewhere else in docs, so what should I do?
You are nitpicking.
TSQLTransaction DOES cover what happens on multithreading.

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: SQLite with SQLdb and Multithreading
« Reply #17 on: August 27, 2018, 11:52:58 am »
Please anybody let me know if I'm misunderstanding something.

@asdf121

- As SQLite3's default threading mode is serialized, there would be no problem even though your application access with the same database connection. Any command that operates on the database will wait until the previous transaction is finished (commited or rolled back). 

  So the isolation, etc. are done at SQLite level, not at your application.

- TSQLQuery is not directly related with Transaction.  Transaction works on the server-side database file, while you'd better think TSQLQuery as a local copy of whole database (if you use select). But once you commit or rollback transaction, then the TSQLQuery is automatically "closed" (not freed).  So you can't see the content of "select ..." in the TSQLQuery. In this case, you may call CommitRetaining or RollbackRetaining. These statements will commit or rollback the server DB, but your TSQLQuery will still be open. So you can continue local jobs on the TSQLQuery (like first, next, etc.).


This is as what I understand. I don't understand the transaction issue (locking database issue, more accurately), but this is good enough for me.

asdf121

  • New Member
  • *
  • Posts: 35
Re: SQLite with SQLdb and Multithreading
« Reply #18 on: August 30, 2018, 12:42:22 pm »
But how do I prevent problems of starting a transaction in several threads or closing it when another thread is still working with a  (e.g.) SELECT? So I close the transaction before all my current tasks are done, or?
You can have as much TSQLTransaction as you need for one single TSQLConnection in you application. if you use many TSQLTransaction, they do not need to see each other. It's an isolation level.

If I try to assign another transaction, it fails with:
Code: Pascal  [Select][+][-]
  1. Cannot assign transaction while old transaction active!
  2.  
Or is there a special command to add a second transaction to an existing connection?

And the link from mangakissa says also something else: (or does that mean something else?)
Quote
In SQLite, one connection corresponds to one transaction. To get separate transactions, you need separate connections.

Using one TSQLConnection+TSQLTransaction per thread is easier and slower than using only one TSQLConnection for the whole application and one (or more) TSQLTransaction per thread.

How do I realize that? If I want to assign the Con.Transaction property I get the error from above.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: SQLite with SQLdb and Multithreading
« Reply #19 on: August 30, 2018, 01:48:17 pm »
But how do I prevent problems of starting a transaction in several threads or closing it when another thread is still working with a  (e.g.) SELECT? So I close the transaction before all my current tasks are done, or?
You can have as much TSQLTransaction as you need for one single TSQLConnection in you application. if you use many TSQLTransaction, they do not need to see each other. It's an isolation level.

If I try to assign another transaction, it fails with:
Code: Pascal  [Select][+][-]
  1. Cannot assign transaction while old transaction active!
Or is there a special command to add a second transaction to an existing connection?

And the link from mangakissa says also something else: (or does that mean something else?)
Quote
In SQLite, one connection corresponds to one transaction. To get separate transactions, you need separate connections.

Using one TSQLConnection+TSQLTransaction per thread is easier and slower than using only one TSQLConnection for the whole application and one (or more) TSQLTransaction per thread.

How do I realize that? If I want to assign the Con.Transaction property I get the error from above.
Please show your code.

This is how you use different transactions for different commands in the same database:
SQLTransaction2.Database := SQLConnection1;
SQLQuery2.Transaction := SQLTransaction2;



https://www.sqlite.org/lang_transaction.html
"o changes can be made to the database except within a transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.
Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified. See the documentation on the ON CONFLICT clause for additional information about the ROLLBACK conflict resolution algorithm."


http://wiki.freepascal.org/TSQLQuery
Code: SQL  [Select][+][-]
  1. SQLTransaction1.DATABASE := SQLConnection1;
  2. SQLQuery1.TRANSACTION := SQLTransaction1;
  3. SQLQuery1.SQL.Text := 'INSERT INTO somelist (ItemNr,ItemCount) VALUES (1231,2)';
  4. SQLQuery1.ExecSQL();
  5. SQLTransaction1.Commit();
  6.  
  7. SQLTransaction2.DATABASE := SQLConnection1;
  8. SQLQuery2.TRANSACTION := SQLTransaction2;
  9. SQLQuery2.SQL.Text := 'INSERT INTO somelist (ItemNr,ItemCount) VALUES (1232,3)';
  10. SQLQuery2.ExecSQL();
  11. SQLTransaction2.Commit();

asdf121

  • New Member
  • *
  • Posts: 35
Re: SQLite with SQLdb and Multithreading
« Reply #20 on: September 06, 2018, 04:51:23 pm »
Here is a simple example code from what you mentioned above:

Code: Pascal  [Select][+][-]
  1. program HelloWorld;
  2.  
  3. {$MODE Delphi}
  4.  
  5. uses
  6.     SysUtils, db, sqldb, sqlite3conn;
  7.  
  8. Var
  9.   C : TSQLConnector;
  10.   T1, T2 : TSQLTransaction;
  11.   Q1, Q2 : TSQLQuery;
  12.  
  13. procedure CreateConnection();
  14. begin
  15.         C := TSQLConnector.Create(nil);
  16.         C.ConnectorType := 'SQLite3';
  17.         C.DatabaseName := 'mydatabase.db';
  18.        
  19.         // add a general purpose transaction
  20.         T1 := TSQLTransaction.Create(C);
  21.         C.Transaction  := T1;
  22.        
  23.         C.ExecuteDirect(
  24.         'CREATE TABLE IF NOT EXISTS USERS (U_NAME VARCHAR(255) NOT NULL)'
  25.         );
  26.         C.ExecuteDirect(
  27.                 'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''hello'');'
  28.                 );
  29.         C.ExecuteDirect(
  30.                 'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''world'');'
  31.                 );
  32.  
  33.         // open connection/database
  34.         C.Connected := True;
  35. end;
  36.  
  37.  
  38. begin
  39.         writeln('Start!');
  40.        
  41.         // create SQLite3 connection + set a basic Transaction
  42.         CreateConnection();
  43.     try
  44.        
  45.                 // Create a second transaction.
  46.                 T2 := TSQLTransaction.Create(C);
  47.                 // Point to the database instance
  48.                 T2.Database := C;
  49.  
  50.                 // Create a query to return data
  51.                 Q1 := TSQLQuery.Create(C);
  52.                 // Point to database and transaction.
  53.                 Q1.Database := C;
  54.                 Q1.Transaction := T2;
  55.                 // Set the SQL select statement
  56.                 Q1.SQL.Text := 'SELECT * FROM USERS';
  57.                
  58.                 try
  59.                         Q1.Open;
  60.                         While not Q1.EOF do
  61.                           begin
  62.                           Writeln('   Username is: ' + Q1.FieldByName('U_NAME').AsString);
  63.                           Q1.Next
  64.                           end;
  65.                         Q1.Close;
  66.                 except on E: Exception do
  67.                         Writeln(E.Message);
  68.                 end;
  69.        
  70.         finally
  71.                 C.Free;
  72.         end;
  73.    
  74.         writeln('End!');
  75. end.
  76.  

It fails with: "cannot start a transaction within a transaction"

If you change
Code: Pascal  [Select][+][-]
  1. Q1.Transaction
from T2 to T1 it works fine.

If I remove T1 from
Code: Pascal  [Select][+][-]
  1. C.Transaction
, I cannot run ExecuteDirect: Transaction not set

asdf121

  • New Member
  • *
  • Posts: 35
Re: SQLite with SQLdb and Multithreading
« Reply #21 on: September 08, 2018, 10:06:22 pm »
Here is also a very simple example which uses some threads.
Code: Pascal  [Select][+][-]
  1. program HelloWorld;
  2.  
  3. {$MODE Delphi}
  4.  
  5. uses
  6.     cthreads, cmem, SysUtils, Classes, db, sqldb, sqlite3conn;
  7.  
  8. Var
  9.   C : TSQLConnector;
  10.   T1 : TSQLTransaction;
  11. //  T1, T2 : TSQLTransaction;
  12. //  Q1, Q2 : TSQLQuery;
  13.   TH1,TH2,TH3,TH4 : TThread;
  14.  
  15. procedure CreateConnection();
  16. begin
  17.         C := TSQLConnector.Create(nil);
  18.         C.ConnectorType := 'SQLite3';
  19.         C.DatabaseName := 'mydatabase.db';
  20.        
  21.         // add a general purpose transaction
  22.         T1 := TSQLTransaction.Create(C);
  23.         T1.Options := [stoUseImplicit];
  24.         C.Transaction  := T1;
  25.        
  26.         C.ExecuteDirect(
  27.         'CREATE TABLE IF NOT EXISTS USERS (U_NAME VARCHAR(255) NOT NULL)'
  28.         );
  29.         C.ExecuteDirect(
  30.                 'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''hello'');'
  31.                 );
  32.         C.ExecuteDirect(
  33.                 'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''world'');'
  34.                 );
  35.  
  36.         // open connection/database
  37.         C.Connected := True;
  38. end;
  39.  
  40. procedure DoQuery();
  41. var
  42.   T2 : TSQLTransaction;
  43.   Q1 : TSQLQuery;
  44. begin
  45.                 // Create a second transaction.
  46.                 T2 := TSQLTransaction.Create(C);
  47.                 T2.Options := [stoUseImplicit];
  48.                 // Point to the database instance
  49.                 T2.Database := C;
  50.  
  51.                 // Create a query to return data
  52.                 Q1 := TSQLQuery.Create(C);
  53.                 // Point to database and transaction.
  54.                 Q1.Database := C;
  55.                 Q1.Transaction := T2;
  56.                 // Set the SQL select statement
  57.                 Q1.SQL.Text := 'SELECT * FROM USERS';
  58.                
  59.                 try
  60.                         Q1.Open;
  61.                         While not Q1.EOF do
  62.                           begin
  63.                           Writeln(IntTostr(TThread.CurrentThread.ThreadID) + ' -   Username is: ' + Q1.FieldByName('U_NAME').AsString);
  64.                           Q1.Next
  65.                           end;
  66.                         Q1.Close;
  67.                 except on E: Exception do
  68.                         Writeln(E.Message);
  69.                 end;
  70. end;
  71.  
  72. begin
  73.         writeln('Start!');
  74.        
  75.         // create SQLite3 connection + set a basic Transaction
  76.         CreateConnection();
  77.     try
  78.        
  79.           TH1:=TThread.ExecuteInThread(@DoQuery, nil);
  80.           TH2:=TThread.ExecuteInThread(@DoQuery, nil);
  81.           TH3:=TThread.ExecuteInThread(@DoQuery, nil);
  82.           TH4:=TThread.ExecuteInThread(@DoQuery, nil);
  83.           Writeln('Main thread done');
  84.           TH1.WaitFor;
  85.           TH2.WaitFor;
  86.           TH3.WaitFor;
  87.           TH4.WaitFor;
  88.        
  89.         finally
  90.                 C.Free;
  91.         end;
  92.    
  93.         writeln('End!');
  94. end.
  95.  

Sometimes it works without problems but it also fails with one of the following errors:
  • EDatabaseError: No such dataset registered : ""
  • EAccessViolation: Access violation
  • *** Error in `main': realloc(): invalid pointer: 0x000000000262cf98 ***
  • Not all threads were executed/prints the writeln line

Even the use of stoUseImplicit does not help and I also tried all kinds of variation between using those transactions.

Please tell me how I use it in a multithreading environment without getting errors %)

asdf121

  • New Member
  • *
  • Posts: 35
Re: SQLite with SQLdb and Multithreading
« Reply #22 on: September 11, 2018, 07:22:02 pm »
Nobody?  :(

Thaddy

  • Hero Member
  • *****
  • Posts: 14201
  • Probably until I exterminate Putin.
Re: SQLite with SQLdb and Multithreading
« Reply #23 on: September 14, 2018, 09:48:50 am »
I have narrowed down the issue after playing with the bug report:
- sqlite needs to be compiled for multithreading. This is not always the case.
- currently all sqlite3 components use sqlite3_open and NOT sqlite3_open_v2() which is required for a multithreaded Sqlite.

I have opened a feature request on mantis. To mitigate the issue yourself, change all calls to sqlite3_open to sqlite3_open_v2() with the correct flag(s).
It is backwards compatible and only the flags need some extra code (property).
Note you are still responsible to verify that you have a, sqlite3 compiled for multithreading. This can be queried in code.
« Last Edit: September 14, 2018, 10:04:59 am by Thaddy »
Specialize a type, not a var.

asdf121

  • New Member
  • *
  • Posts: 35
Re: SQLite with SQLdb and Multithreading
« Reply #24 on: September 14, 2018, 11:19:00 am »
I've adopted my code to check for multi-threading:

Code: Pascal  [Select][+][-]
  1. program HelloWorld;
  2.  
  3. {$MODE Delphi}
  4.  
  5. uses
  6.     cthreads, cmem, SysUtils, Classes, db, sqldb, sqlite3conn, sqlite3dyn;
  7.  
  8. Var
  9.   C : TSQLConnector;
  10.   T1 : TSQLTransaction;
  11. //  T1, T2 : TSQLTransaction;
  12. //  Q1, Q2 : TSQLQuery;
  13.   TH1,TH2,TH3,TH4 : TThread;
  14.   threadsafe: integer;
  15.  
  16. procedure CreateConnection();
  17. begin
  18.         C := TSQLConnector.Create(nil);
  19.         C.ConnectorType := 'SQLite3';
  20.         C.DatabaseName := 'mydatabase.db';
  21.        
  22.         // add a general purpose transaction
  23.         T1 := TSQLTransaction.Create(C);
  24.         T1.Options := [stoUseImplicit];
  25.         C.Transaction  := T1;
  26.        
  27.         C.ExecuteDirect(
  28.         'CREATE TABLE IF NOT EXISTS USERS (U_NAME VARCHAR(255) NOT NULL)'
  29.         );
  30.         C.ExecuteDirect(
  31.                 'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''hello'');'
  32.                 );
  33.         C.ExecuteDirect(
  34.                 'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''world'');'
  35.                 );
  36.  
  37.         // open connection/database
  38.         C.Connected := True;
  39. end;
  40.  
  41. procedure DoQuery();
  42. var
  43.   T2 : TSQLTransaction;
  44.   Q1 : TSQLQuery;
  45. begin
  46.                 // Create a second transaction.
  47.                 T2 := TSQLTransaction.Create(C);
  48.                 T2.Options := [stoUseImplicit];
  49.                 // Point to the database instance
  50.                 T2.Database := C;
  51.  
  52.                 // Create a query to return data
  53.                 Q1 := TSQLQuery.Create(C);
  54.                 // Point to database and transaction.
  55.                 Q1.Database := C;
  56.                 Q1.Transaction := T2;
  57.                 // Set the SQL select statement
  58.                 Q1.SQL.Text := 'SELECT * FROM USERS';
  59.                
  60.                 try
  61.                         Q1.Open;
  62.                         While not Q1.EOF do
  63.                           begin
  64.                           Writeln(IntTostr(TThread.CurrentThread.ThreadID) + ' -   Username is: ' + Q1.FieldByName('U_NAME').AsString);
  65.                           Q1.Next
  66.                           end;
  67.                         Q1.Close;
  68.                 except on E: Exception do
  69.                         Writeln(E.Message);
  70.                 end;
  71. end;
  72.  
  73. begin
  74.         writeln('Start!');
  75.        
  76.         // create SQLite3 connection + set a basic Transaction
  77.         CreateConnection();
  78.        
  79.             threadsafe := sqlite3_threadsafe();
  80.         if threadsafe > 0 then
  81.             writeln('Your SQLite3 does support multithreading with mode: ' + IntToStr(threadsafe))
  82.         else
  83.             writeln('Sorry, no support for multithreading');
  84.  
  85.     try
  86.        
  87.           TH1:=TThread.ExecuteInThread(@DoQuery, nil);
  88.           TH2:=TThread.ExecuteInThread(@DoQuery, nil);
  89.           TH3:=TThread.ExecuteInThread(@DoQuery, nil);
  90.           TH4:=TThread.ExecuteInThread(@DoQuery, nil);
  91.           Writeln('Main thread done');
  92.           TH1.WaitFor;
  93.           TH2.WaitFor;
  94.           TH3.WaitFor;
  95.           TH4.WaitFor;
  96.        
  97.         finally
  98.                 C.Free;
  99.         end;
  100.    
  101.         writeln('End!');
  102. end.
  103.  

And on my platform it's compiled with multi-threading support, so that's definitely not the problem.

According to Using SQLite In Multi-Threaded Applications it cannot be an issue of using sqlite3_open in SQLdb:
Quote
If neither flag is specified or if sqlite3_open() or sqlite3_open16() are used instead of sqlite3_open_v2(), then the default mode determined by the compile-time and start-time settings is used.
« Last Edit: September 14, 2018, 11:30:09 am by asdf121 »

Thaddy

  • Hero Member
  • *****
  • Posts: 14201
  • Probably until I exterminate Putin.
Re: SQLite with SQLdb and Multithreading
« Reply #25 on: September 14, 2018, 12:45:40 pm »
No. All open's need to be replaced with the V2 versions. That is desirable anyway. The old open needs to be deprecated.
I tested similar code with SQLITE_FULLMUTEX as open-v2 parameter and I could not reproduce the reallocation error. I Also tested similar code with NO_MUTEX and that failed sometimes like it failed for you.
If recode it with per thread connections/transactions and threadvars then I also don't get errors with SQLITE_OPEN_NOMUTEX

The example in the bug report can be fixed like so and with current library code:
Code: Pascal  [Select][+][-]
  1. program HelloWorld2;
  2.  // warning: quick and dirty solution
  3. {$MODE Delphi}
  4.  
  5. uses
  6.     cthreads, cmem, SysUtils, Classes, db, sqldb, sqlite3conn;
  7. threadvar
  8.   T2 : TSQLTransaction;
  9.   Q1 : TSQLQuery;
  10.   C : TSQLConnector;
  11.   T1 : TSQLTransaction;
  12.   TH1,TH2,TH3,TH4 : TThread;
  13.  
  14. procedure CreateConnection();
  15. begin
  16.   C := TSQLConnector.Create(nil);
  17.   C.ConnectorType := 'SQLite3';
  18.   C.DatabaseName := 'mydatabase.db';      
  19.   // add a general purpose transaction,
  20.   // this is now threadsafe
  21.   T1 := TSQLTransaction.Create(C);
  22.   T1.Options := [stoUseImplicit];
  23.   C.Transaction  := T1;
  24.        
  25.   C.ExecuteDirect(
  26.         'CREATE TABLE IF NOT EXISTS USERS (U_NAME VARCHAR(255) NOT NULL)'
  27.         );
  28.   C.ExecuteDirect(
  29.                 'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''hello'');'
  30.                 );
  31.   C.ExecuteDirect(
  32.                 'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''world'');'
  33.                 );
  34.  
  35.   // open connection/database
  36.   C.Connected := True;
  37. end;
  38.  
  39. procedure DoQuery();
  40. begin
  41.   try
  42.     CreateConnection;
  43.     // simulate load
  44.     sleep(Random(1000));                  
  45.     // Create a second transaction.
  46.     T2 := TSQLTransaction.Create(C);
  47.     T2.Options := [stoUseImplicit];
  48.     // Point to the database instance
  49.     T2.Database := C;
  50.     // Create a query to return data
  51.     Q1 := TSQLQuery.Create(C);
  52.     // Point to database and transaction.
  53.     Q1.Database := C;
  54.     Q1.Transaction := T2;
  55.     // Set the SQL select statement
  56.     Q1.SQL.Text := 'SELECT * FROM USERS';
  57.                
  58.     try
  59.       Q1.Open;
  60.       While not Q1.EOF do
  61.       begin
  62.         Writeln(IntTostr(TThread.CurrentThread.ThreadID) + ' -   Username is: ' + Q1.FieldByName('U_NAME').AsString);
  63.         Q1.Next
  64.       end;
  65.       Q1.Close;
  66.     except on E: Exception do
  67.                         Writeln(E.Message);
  68.     end;
  69.   finally
  70.     C.Free;
  71.   end;
  72. end;
  73.  
  74. begin
  75.    writeln('Start!',  TThread.CurrentThread.ThreadID);      
  76.    CreateConnection;
  77.    TH1:=TThread.ExecuteInThread(@DoQuery, nil);
  78.    TH2:=TThread.ExecuteInThread(@DoQuery, nil);
  79.    TH3:=TThread.ExecuteInThread(@DoQuery, nil);
  80.    TH4:=TThread.ExecuteInThread(@DoQuery, nil);
  81.    Writeln('Main thread done');
  82.    TH1.WaitFor;
  83.    TH2.WaitFor;
  84.    TH3.WaitFor;
  85.    TH4.WaitFor;  
  86.    writeln('End!',  TThread.CurrentThread.ThreadID);
  87. end.

note I know this is not ideal code but it shows the basic problem as solved.
A better solution, though, would be to implement sqlite3_open_v2() in the FPC classes as the default and add a property for the flags. That would solve it like it should be solved.
« Last Edit: September 14, 2018, 03:06:29 pm by Thaddy »
Specialize a type, not a var.

asdf121

  • New Member
  • *
  • Posts: 35
Re: SQLite with SQLdb and Multithreading
« Reply #26 on: September 27, 2018, 05:30:02 pm »
Not sure if you have seen it but I posted an example without SQLdb which works fine with multithreading. It does not use any of those new _v2 functions from SQLite nor any locking.
I doubt it's sqlite3, seems to be a SQLdb related issue.

Thaddy

  • Hero Member
  • *****
  • Posts: 14201
  • Probably until I exterminate Putin.
Re: SQLite with SQLdb and Multithreading
« Reply #27 on: September 27, 2018, 05:35:16 pm »
Not sure if you have seen it but I posted an example without SQLdb which works fine with multithreading. It does not use any of those new _v2 functions from SQLite nor any locking.
I doubt it's sqlite3, seems to be a SQLdb related issue.
It is sqlite specific. You are very stubborn. I like that  :D :D O:-) 8-), but since I showed you how to fix it I am not wasting any more time on it. Buy some glasses and learn to read.
Your example code is NEVER going to work reliably. Did you even try my -quick - fix? Do you truly understand what the Sqlite documentation says? < sorry. grumpy... >:D >:D >:D >

Now, take a step back, look at your code, read the docs, try my fix (which you can improve. that was just quick) , and report back....
« Last Edit: September 27, 2018, 05:44:27 pm by Thaddy »
Specialize a type, not a var.

asdf121

  • New Member
  • *
  • Posts: 35
Re: SQLite with SQLdb and Multithreading
« Reply #28 on: September 27, 2018, 05:52:57 pm »
You shouldn't talk about your "fix", it uses threadvar - each thread has a separate instance of the connection.

If you look at my example: it only has 1 connection which is used for all threads. Completely different to your "solution". (also mentioned above from valdir.marcos and egsuh (serialized mode))
« Last Edit: September 27, 2018, 05:57:04 pm by asdf121 »

Thaddy

  • Hero Member
  • *****
  • Posts: 14201
  • Probably until I exterminate Putin.
Re: SQLite with SQLdb and Multithreading
« Reply #29 on: October 29, 2018, 12:27:53 pm »
Michael has implemented it and I have tested your scenario with the fix.
See the bug report.
Options are:
Code: Pascal  [Select][+][-]
  1.   // VFS not supported at this time.
  2.   // Do not change the order. See NativeFlags constant in GetSQLiteOpenFlags.
  3.  
  4.   TSQLiteOpenFlag = (
  5.     sofReadOnly,
  6.     sofReadWrite,
  7.     sofCreate,
  8.     sofNoMutex,
  9.     sofFullMutex,
  10.     sofSharedCache,
  11.     sofPrivateCache,
  12.     sofURI,
  13.     sofMemory
  14.   );
  15.   TSQLiteOpenFlags = set of TSQLiteOpenFlag;
Quote
Default is set to
DefaultOpenFlags = [sofReadWrite,sofCreate];

Which should be backwards compatible.

You will be amazed to find your scenario now works if you set the proper flags  :D A matter of reading the documentation.
« Last Edit: October 29, 2018, 12:30:51 pm by Thaddy »
Specialize a type, not a var.

 

TinyPortal © 2005-2018