SQLite with SQLdb and Multithreading
lazarus / 16-Databases

----------------------------------------------------------------------
asdf121 · 2018-08-13 18:46

Hello,
I've got some code from Delphi which uses TSQLConnection and works
fine in my application, which uses many threads for parallel
inserts/selects without seperate lockings.
Now I want to use it with FPC as well but SQL stuff seems more complex
than in Delphi as you need a TSQLTransaction.
Now my problem is: How to use FPC's SQLdb in a multithread
environment? Haven't found any example which cover this case which
should be a basic thing these days...

1. Should I use one TSQLConnection+TSQLTransaction which is re-used
for all inserts/selects (activate/deactivate transaction every time?
keep it active?) or create a new TSQLConnection+TSQLTransaction
combination for each database interaction (inserts/selects)? Use
something different?
2. What about TSQLQuery, use it with an own TSQLTransaction, use an
own TSQLConnection+TSQLTransaction combination I mentioned in 1.?
3. What about the options like sqoAutoCommit? Any way to not use the
.Commit to save into db? (not needed in Delphi)
4. Is it possible at all to use SQLdb without lockings in FPC? (Not
sure if Delphi internally creates locks as it just works out of the
box.)

Best Regards.

----------------------------------------------------------------------
Thaddy · 2018-08-13 19:58

See https://sqlite.org/threadsafe.html and configure or compile sqlite
accordingly. Sqldb has not much to do with it.
Note your remark about the "not needed commit" in Delphi is incorrect,
at least for remote databases.
Note that autocommit options may seem handy but they will bite you
very quickly if you have more connections to the same database.

----------------------------------------------------------------------
mangakissa · 2018-08-14 06:34

Quote4. Is it possible at all to use SQLdb without lockings in FPC?
(Not sure if Delphi internally creates locks as it just works out of
the box.)
Even ZEOS uses locking, but has his autocommited to true as standard
value. Very confusing if you want to work with SQLdb.
I prefer SQLdb above ZEOS.

----------------------------------------------------------------------
Thaddy · 2018-08-14 07:03

Quote from: mangakissa on August 14, 2018, 08:34:22 amQuote4. Is it
possible at all to use SQLdb without lockings in FPC? (Not sure if
Delphi internally creates locks as it just works out of the box.)
Even ZEOS uses locking, but has his autocommited to true as standard
value. Very confusing if you want to work with SQLdb.
I prefer SQLdb above ZEOS.
As you can read from my link: sqlite itself is capable of locking.
Better to lock there, not local. As for autocommits? Think, if you
have multiple users.

----------------------------------------------------------------------
asdf121 · 2018-08-14 14:25

Quote from: Thaddy on August 13, 2018, 09:58:32 pmSee
https://sqlite.org/threadsafe.html and configure or compile sqlite
accordingly. Sqldb has not much to do with it.
That's not the problem as it works fine with my old SQLite code (same
SQLite library, no lockings in my code)

Quote from: mangakissa on August 14, 2018, 08:34:22 amQuote4. Is it
possible at all to use SQLdb without lockings in FPC? (Not sure if
Delphi internally creates locks as it just works out of the box.)
Even ZEOS uses locking, but has his autocommited to true as standard
value. Very confusing if you want to work with SQLdb.
I prefer SQLdb above ZEOS.
I don't use ZEOS, I use dbExpress as it's the thing FPC 'copied'.
That's why you can use it with some small code changes in FPC/Delphi
except the Transactions.

To be honest, none of your answers really helped me. 

EDIT:
A commit should be done before every TSQLQuery.free, or?

----------------------------------------------------------------------
mangakissa · 2018-08-15 06:26

I know something about DBExress and you can not compare it to SQLdb.
SQLdb is much older and more reliable.
SQLite is originally a single database. So the answer Thaddy told is
correct.
I know DBExpres doesn't work with transactions, because it closes the
connection and thats why there are no transactions.
Thaddy has also right that locking is a database issue and not a
framework issue. Fortunally SQLdb has a component called TTransaction
that work with it. So an commit(retaining) is not only send the data
to a table, but also closes the transaction on a database. That's why
(I think) Firebird works very well with SQLdb.
QuoteEDIT:
A commit should be done before every TSQLQuery.free, or?A
commit/rollback is always required to make your modifications to the
database. It also closes the transaction file. Even when you do a
SELECT. But the option readcommitted will do it for you.

----------------------------------------------------------------------
asdf121 · 2018-08-16 14:35

Quote from: asdf121 on August 13, 2018, 08:46:57 pm1. Should I use one
TSQLConnection+TSQLTransaction which is re-used for all
inserts/selects (activate/deactivate transaction every time? keep it
active?) or create a new TSQLConnection+TSQLTransaction combination
for each database interaction (inserts/selects)? Use something
different?
2. What about TSQLQuery, use it with an own TSQLTransaction, use an
own TSQLConnection+TSQLTransaction combination I mentioned in 1.?

What about those things? Any advices?

----------------------------------------------------------------------
mangakissa · 2018-08-17 06:48

https://www.sqlite.org/isolation.html
https://stackoverflow.com/questions/37516785/is-it-allowed-to-start-
multiple-transactions-in-the-same-sqlite-database-connect

I created a demo once for a Lazarus day in the Netherlands and used
one based transaction for all tables. I suggest to read the links.

----------------------------------------------------------------------
asdf121 · 2018-08-24 21:15

Quote from: mangakissa on August 17, 2018, 08:48:00
amhttps://www.sqlite.org/isolation.html

After reading this, I guess you would suggest to only use one
TSQLConnection, right?
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?
To prevent this I've to use locks but Thaddy says I shouldn't use
locks as this is a thing SQLite should handle.

----------------------------------------------------------------------
taazz · 2018-08-24 22:09

Quote from: asdf121 on August 24, 2018, 11:15:10 pmQuote from:
mangakissa on August 17, 2018, 08:48:00
amhttps://www.sqlite.org/isolation.html

After reading this, I guess you would suggest to only use one
TSQLConnection, right?
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?
To prevent this I've to use locks but Thaddy says I shouldn't use
locks as this is a thing SQLite should handle.
 
easy use a database and forget sqlite.

----------------------------------------------------------------------
mangakissa · 2018-08-25 09:06

Quote from: asdf121 on August 24, 2018, 11:15:10 pmQuote from:
mangakissa on August 17, 2018, 08:48:00
amhttps://www.sqlite.org/isolation.html

After reading this, I guess you would suggest to only use one
TSQLConnection, right?
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?
To prevent this I've to use locks but Thaddy says I shouldn't use
locks as this is a thing SQLite should handle.
 
Did you read the tutorials of SQLdb? Its working with transactions and
also with the database engine.

----------------------------------------------------------------------
asdf121 · 2018-08-25 10:44

Quote from: mangakissa on August 25, 2018, 11:06:22 amQuote from:
asdf121 on August 24, 2018, 11:15:10 pmQuote from: mangakissa on
August 17, 2018, 08:48:00 amhttps://www.sqlite.org/isolation.html

After reading this, I guess you would suggest to only use one
TSQLConnection, right?
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?
To prevent this I've to use locks but Thaddy says I shouldn't use
locks as this is a thing SQLite should handle.
 
Did you read the tutorials of SQLdb? Its working with transactions and
also with the database engine.

Sure, I did read it several times but it does not answer my
questions. 

It does not cover what happens if you're on multithreading.
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
Connect.Open; Trans.StartTransaction;Connect.ExecuteDirect('insert
some new stuff');Trans.Commit; 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?

----------------------------------------------------------------------
valdir.marcos · 2018-08-25 17:48

Quote from: asdf121 on August 25, 2018, 12:44:08 pmIn 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?Try to think of multi-
threading as being the natural path of a networked computers with a
database server.
Race condition is all about (R)DBMS wants to solve.
From this idea, using transactions, SQLdb will easily solve all of
your needs.
Perhaps your problem is wanting to abstract something that is
concrete.

----------------------------------------------------------------------
valdir.marcos · 2018-08-25 17:53

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.
My suggestion is to try both and realize what is best for you.

----------------------------------------------------------------------
valdir.marcos · 2018-08-25 17:56

As a last remark, it will be easier to help you, if you provide a very
small example project with a concrete case.

----------------------------------------------------------------------
valdir.marcos · 2018-08-25 18:07

Quote from: asdf121 on August 25, 2018, 12:44:08 pmBut 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.

QuoteIt does not cover what happens if you're on multithreading.
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
Connect.Open; Trans.StartTransaction;Connect.ExecuteDirect('insert
some new stuff');Trans.Commit; 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 · 2018-08-27 06:37

https://www.nldelphi.com/showthread.php?42932-SQLite-en-
transactions&p=359472&viewfull=1#post359472

----------------------------------------------------------------------
egsuh · 2018-08-27 09:52

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 · 2018-08-30 10:42

Quote from: valdir.marcos on August 25, 2018, 08:07:53 pmQuote from:
asdf121 on August 25, 2018, 12:44:08 pmBut 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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?)
QuoteIn SQLite, one connection corresponds to one transaction. To get
separate transactions, you need separate connections.

Quote from: valdir.marcos on August 25, 2018, 07:53:31 pmUsing 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 · 2018-08-30 11:48

Quote from: asdf121 on August 30, 2018, 12:42:22 pmQuote from:
valdir.marcos on August 25, 2018, 08:07:53 pmQuote from: asdf121 on
August 25, 2018, 12:44:08 pmBut 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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?)QuoteIn SQLite, one connection corresponds to one
transaction. To get separate transactions, you need separate
connections.
Quote from: valdir.marcos on August 25, 2018, 07:53:31 pmUsing 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLTransaction1.DATABASE := SQLConnection1;SQLQuery1.TRANSACTION :=
SQLTransaction1;SQLQuery1.SQL.Text := 'INSERT INTO somelist
(ItemNr,ItemCount) VALUES (1231,2)';SQLQuery1.ExecSQL();SQLTransaction
1.Commit(); SQLTransaction2.DATABASE :=
SQLConnection1;SQLQuery2.TRANSACTION :=
SQLTransaction2;SQLQuery2.SQL.Text := 'INSERT INTO somelist
(ItemNr,ItemCount) VALUES
(1232,3)';SQLQuery2.ExecSQL();SQLTransaction2.Commit();

----------------------------------------------------------------------
asdf121 · 2018-09-06 14:51

Here is a simple example code from what you mentioned above:

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
HelloWorld; {$MODE Delphi} uses    SysUtils, db, sqldb,
sqlite3conn; Var  C : TSQLConnector;  T1, T2 : TSQLTransaction;  Q1,
Q2 : TSQLQuery; procedure CreateConnection();begin        C :=
TSQLConnector.Create(nil);        C.ConnectorType := 'SQLite3';      
C.DatabaseName := 'mydatabase.db';                // add a general
purpose transaction        T1 := TSQLTransaction.Create(C);       
C.Transaction  := T1;                C.ExecuteDirect(        'CREATE
TABLE IF NOT EXISTS USERS (U_NAME VARCHAR(255) NOT NULL)'        );  
    C.ExecuteDirect(                'INSERT OR IGNORE INTO USERS
(U_NAME) VALUES (''hello'');'                );       
C.ExecuteDirect(                'INSERT OR IGNORE INTO USERS (U_NAME)
VALUES (''world'');'                );         // open
connection/database        C.Connected := True;end;   begin       
writeln('Start!');                // create SQLite3 connection + set a
basic Transaction        CreateConnection();    try                  
    // Create a second transaction.                T2 :=
TSQLTransaction.Create(C);                // Point to the database
instance                T2.Database := C;                 // Create a
query to return data                Q1 := TSQLQuery.Create(C);       
       // Point to database and transaction.               
Q1.Database := C;                Q1.Transaction := T2;               
// Set the SQL select statement                Q1.SQL.Text := 'SELECT
* FROM USERS';                                try                    
  Q1.Open;                        While not Q1.EOF do                
        begin                          Writeln('   Username is: ' +
Q1.FieldByName('U_NAME').AsString);                          Q1.Next 
                       end;                        Q1.Close;         
     except on E: Exception do                       
Writeln(E.Message);                end;                finally       
       C.Free;        end;           writeln('End!');end. 
It fails with: "cannot start a transaction within a transaction"

If you change 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 Q1.Transaction from T2 to T1 it works fine.

If I remove T1 from 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 C.Transaction, I cannot run ExecuteDirect: Transaction not set

----------------------------------------------------------------------
asdf121 · 2018-09-08 20:06

Here is also a very simple example which uses some threads.
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
HelloWorld; {$MODE Delphi} uses    cthreads, cmem, SysUtils, Classes,
db, sqldb, sqlite3conn; Var  C : TSQLConnector;  T1 :
TSQLTransaction;//  T1, T2 : TSQLTransaction;//  Q1, Q2 : TSQLQuery; 
TH1,TH2,TH3,TH4 : TThread; procedure CreateConnection();begin        C
:= TSQLConnector.Create(nil);        C.ConnectorType := 'SQLite3';   
   C.DatabaseName := 'mydatabase.db';               // add a general
purpose transaction        T1 := TSQLTransaction.Create(C);       
T1.Options := [stoUseImplicit];        C.Transaction  := T1;         
    C.ExecuteDirect(        'CREATE TABLE IF NOT EXISTS USERS (U_NAME
VARCHAR(255) NOT NULL)'        );        C.ExecuteDirect(            
  'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''hello'');'         
     );        C.ExecuteDirect(                'INSERT OR IGNORE INTO
USERS (U_NAME) VALUES (''world'');'                );         // open
connection/database        C.Connected := True;end; procedure
DoQuery();var  T2 : TSQLTransaction;  Q1 : TSQLQuery;begin           
   // Create a second transaction.                T2 :=
TSQLTransaction.Create(C);                T2.Options :=
[stoUseImplicit];                // Point to the database instance   
           T2.Database := C;                 // Create a query to
return data                Q1 := TSQLQuery.Create(C);               
// Point to database and transaction.                Q1.Database :=
C;                Q1.Transaction := T2;                // Set the SQL
select statement                Q1.SQL.Text := 'SELECT * FROM USERS'; 
                             try                        Q1.Open;     
                 While not Q1.EOF do                          begin  
                      Writeln(IntTostr(TThread.CurrentThread.ThreadID)
+ ' -   Username is: ' + Q1.FieldByName('U_NAME').AsString);         
               Q1.Next                          end;                 
     Q1.Close;                except on E: Exception do              
        Writeln(E.Message);                end;end; begin       
writeln('Start!');               // create SQLite3 connection + set a
basic Transaction        CreateConnection();    try                 
TH1:=TThread.ExecuteInThread(@DoQuery, nil);         
TH2:=TThread.ExecuteInThread(@DoQuery, nil);         
TH3:=TThread.ExecuteInThread(@DoQuery, nil);         
TH4:=TThread.ExecuteInThread(@DoQuery, nil);          Writeln('Main
thread done');          TH1.WaitFor;          TH2.WaitFor;         
TH3.WaitFor;          TH4.WaitFor;               finally             
 C.Free;        end;           writeln('End!');end. 
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 · 2018-09-11 17:22

Nobody?

----------------------------------------------------------------------
Thaddy · 2018-09-14 07:48

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.

----------------------------------------------------------------------
asdf121 · 2018-09-14 09:19

I've adopted my code to check for multi-threading:

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
HelloWorld; {$MODE Delphi} uses    cthreads, cmem, SysUtils, Classes,
db, sqldb, sqlite3conn, sqlite3dyn; Var  C : TSQLConnector;  T1 :
TSQLTransaction;//  T1, T2 : TSQLTransaction;//  Q1, Q2 : TSQLQuery; 
TH1,TH2,TH3,TH4 : TThread;  threadsafe: integer; procedure
CreateConnection();begin        C := TSQLConnector.Create(nil);      
C.ConnectorType := 'SQLite3';        C.DatabaseName :=
'mydatabase.db';               // add a general purpose transaction  
    T1 := TSQLTransaction.Create(C);        T1.Options :=
[stoUseImplicit];        C.Transaction  := T1;              
C.ExecuteDirect(        'CREATE TABLE IF NOT EXISTS USERS (U_NAME
VARCHAR(255) NOT NULL)'        );        C.ExecuteDirect(            
  'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''hello'');'         
     );        C.ExecuteDirect(                'INSERT OR IGNORE INTO
USERS (U_NAME) VALUES (''world'');'                );         // open
connection/database        C.Connected := True;end; procedure
DoQuery();var  T2 : TSQLTransaction;  Q1 : TSQLQuery;begin           
   // Create a second transaction.                T2 :=
TSQLTransaction.Create(C);                T2.Options :=
[stoUseImplicit];                // Point to the database instance   
           T2.Database := C;                 // Create a query to
return data                Q1 := TSQLQuery.Create(C);               
// Point to database and transaction.                Q1.Database :=
C;                Q1.Transaction := T2;                // Set the SQL
select statement                Q1.SQL.Text := 'SELECT * FROM USERS'; 
                             try                        Q1.Open;     
                 While not Q1.EOF do                          begin  
                      Writeln(IntTostr(TThread.CurrentThread.ThreadID)
+ ' -   Username is: ' + Q1.FieldByName('U_NAME').AsString);         
               Q1.Next                          end;                 
     Q1.Close;                except on E: Exception do              
        Writeln(E.Message);                end;end; begin       
writeln('Start!');               // create SQLite3 connection + set a
basic Transaction        CreateConnection();                  
threadsafe := sqlite3_threadsafe();        if threadsafe > 0 then    
      writeln('Your SQLite3 does support multithreading with mode: ' +
IntToStr(threadsafe))        else            writeln('Sorry, no
support for multithreading');     try                
TH1:=TThread.ExecuteInThread(@DoQuery, nil);         
TH2:=TThread.ExecuteInThread(@DoQuery, nil);         
TH3:=TThread.ExecuteInThread(@DoQuery, nil);         
TH4:=TThread.ExecuteInThread(@DoQuery, nil);          Writeln('Main
thread done');          TH1.WaitFor;          TH2.WaitFor;         
TH3.WaitFor;          TH4.WaitFor;               finally             
 C.Free;        end;           writeln('End!');end. 
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:
QuoteIf 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.

----------------------------------------------------------------------
Thaddy · 2018-09-14 10:45

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][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i program HelloWorld2; // warning: quick and dirty solution{$MODE
Delphi} uses    cthreads, cmem, SysUtils, Classes, db, sqldb,
sqlite3conn;threadvar  T2 : TSQLTransaction;  Q1 : TSQLQuery;  C :
TSQLConnector;  T1 : TSQLTransaction;  TH1,TH2,TH3,TH4 :
TThread; procedure CreateConnection();begin  C :=
TSQLConnector.Create(nil);  C.ConnectorType := 'SQLite3'; 
C.DatabaseName := 'mydatabase.db';        // add a general purpose
transaction,   // this is now threadsafe  T1 :=
TSQLTransaction.Create(C);  T1.Options := [stoUseImplicit]; 
C.Transaction  := T1;         C.ExecuteDirect(        'CREATE TABLE IF
NOT EXISTS USERS (U_NAME VARCHAR(255) NOT NULL)'        ); 
C.ExecuteDirect(                'INSERT OR IGNORE INTO USERS (U_NAME)
VALUES (''hello'');'                );  C.ExecuteDirect(             
 'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''world'');'          
    );   // open connection/database  C.Connected :=
True;end; procedure DoQuery();begin  try    CreateConnection;    //
simulate load    sleep(Random(1000));                      // Create a
second transaction.    T2 := TSQLTransaction.Create(C);    T2.Options
:= [stoUseImplicit];    // Point to the database instance   
T2.Database := C;     // Create a query to return data    Q1 :=
TSQLQuery.Create(C);    // Point to database and transaction.   
Q1.Database := C;    Q1.Transaction := T2;    // Set the SQL select
statement    Q1.SQL.Text := 'SELECT * FROM USERS';                  
try      Q1.Open;      While not Q1.EOF do      begin       
Writeln(IntTostr(TThread.CurrentThread.ThreadID) + ' -   Username is:
' + Q1.FieldByName('U_NAME').AsString);        Q1.Next      end;     
Q1.Close;    except on E: Exception do                       
Writeln(E.Message);    end;  finally    C.Free;  end;end; begin 
 writeln('Start!',  TThread.CurrentThread.ThreadID);        
 CreateConnection;   TH1:=TThread.ExecuteInThread(@DoQuery, nil); 
 TH2:=TThread.ExecuteInThread(@DoQuery, nil); 
 TH3:=TThread.ExecuteInThread(@DoQuery, nil); 
 TH4:=TThread.ExecuteInThread(@DoQuery, nil);   Writeln('Main thread
done');   TH1.WaitFor;   TH2.WaitFor;   TH3.WaitFor;   TH4.WaitFor;  
  writeln('End!',  TThread.CurrentThread.ThreadID);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.

----------------------------------------------------------------------
asdf121 · 2018-09-27 15:30

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 · 2018-09-27 15:35

Quote from: asdf121 on September 27, 2018, 05:30:02 pmNot 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     , 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...    >

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....

----------------------------------------------------------------------
asdf121 · 2018-09-27 15:52

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))

----------------------------------------------------------------------
Thaddy · 2018-10-29 11:27

Michael has implemented it and I have tested your scenario with the
fix.
See the bug report.
Options are:
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   // VFS
not supported at this time.  // Do not change the order. See
NativeFlags constant in GetSQLiteOpenFlags.   TSQLiteOpenFlag = (   
sofReadOnly,    sofReadWrite,    sofCreate,    sofNoMutex,   
sofFullMutex,    sofSharedCache,    sofPrivateCache,    sofURI,   
sofMemory  );  TSQLiteOpenFlags = set of TSQLiteOpenFlag; QuoteDefault
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   A matter of reading the documentation.

----------------------------------------------------------------------
lainz · 2019-04-30 02:05

Hi, someone knows the bug report link and the status?

And Thaddy thanks for the code

----------------------------------------------------------------------
engkin · 2019-04-30 02:57

Quote from: Lainz on April 30, 2019, 04:05:28 amHi, someone knows the
bug report link and the status?

Link: 34274 and 34278

----------------------------------------------------------------------
lainz · 2019-12-15 17:29

Quote from: engkin on April 30, 2019, 04:57:09 amQuote from: Lainz on
April 30, 2019, 04:05:28 amHi, someone knows the bug report link and
the status?

Link: 34274 and 34278

Thanks. Seems that just adding soFullMutex is the way to go in Trunk.

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
SQLite3InitialConnection.OpenFlags := [sofReadWrite, sofCreate,
sofFullMutex];
The connection can be used in multiple threads, as well the
transaction!

Plus as I readed here https://www.sqlite.org/compile.html#threadsafe

QuoteThis option controls whether or not code is included in SQLite to
enable it to operate safely in a multithreaded environment. The
default is SQLITE_THREADSAFE=1 which is safe for use in a
multithreaded environment.
So I don't need to rebuild the DLL since the multithread is the
default mode