* * *

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

asdf121

  • New member
  • *
  • Posts: 14
SQLite with SQLdb and Multithreading
« on: August 13, 2018, 08:46:57 pm »
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

  • Hero Member
  • *****
  • Posts: 7087
Re: SQLite with SQLdb and Multithreading
« Reply #1 on: August 13, 2018, 09:58:32 pm »
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.
« Last Edit: August 13, 2018, 10:05:52 pm by Thaddy »
inline variables like in D10.3 are a bit like Brexit: if you are given the wrong information it sounds like a good idea. Every kid loves candy, but it makes you fat and your teeth will disappear.

mangakissa

  • Hero Member
  • *****
  • Posts: 840
Re: SQLite with SQLdb and Multithreading
« Reply #2 on: August 14, 2018, 08:34:22 am »
Quote
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.)
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.
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

Thaddy

  • Hero Member
  • *****
  • Posts: 7087
Re: SQLite with SQLdb and Multithreading
« Reply #3 on: August 14, 2018, 09:03:45 am »
Quote
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.)
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.
inline variables like in D10.3 are a bit like Brexit: if you are given the wrong information it sounds like a good idea. Every kid loves candy, but it makes you fat and your teeth will disappear.

asdf121

  • New member
  • *
  • Posts: 14
Re: SQLite with SQLdb and Multithreading
« Reply #4 on: August 14, 2018, 04:25:02 pm »
See 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
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.)
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?
« Last Edit: August 14, 2018, 08:18:59 pm by asdf121 »

mangakissa

  • Hero Member
  • *****
  • Posts: 840
Re: SQLite with SQLdb and Multithreading
« Reply #5 on: August 15, 2018, 08:26:41 am »
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.
Quote
EDIT:
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.
 

 
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

asdf121

  • New member
  • *
  • Posts: 14
Re: SQLite with SQLdb and Multithreading
« Reply #6 on: August 16, 2018, 04:35:14 pm »
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.?

What about those things? Any advices?

mangakissa

  • Hero Member
  • *****
  • Posts: 840
Re: SQLite with SQLdb and Multithreading
« Reply #7 on: August 17, 2018, 08:48:00 am »
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.
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

asdf121

  • New member
  • *
  • Posts: 14
Re: SQLite with SQLdb and Multithreading
« Reply #8 on: August 24, 2018, 11:15:10 pm »
https://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.
 :-\ :-\ :-\
« Last Edit: August 24, 2018, 11:18:59 pm by asdf121 »

taazz

  • Hero Member
  • *****
  • Posts: 5342
Re: SQLite with SQLdb and Multithreading
« Reply #9 on: August 25, 2018, 12:09:16 am »
https://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.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

mangakissa

  • Hero Member
  • *****
  • Posts: 840
Re: SQLite with SQLdb and Multithreading
« Reply #10 on: August 25, 2018, 11:06:22 am »
https://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.
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

asdf121

  • New member
  • *
  • Posts: 14
Re: SQLite with SQLdb and Multithreading
« Reply #11 on: August 25, 2018, 12:44:08 pm »
https://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]
  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?
« Last Edit: August 25, 2018, 12:58:47 pm by asdf121 »

valdir.marcos

  • Hero Member
  • *****
  • Posts: 524
Re: SQLite with SQLdb and Multithreading
« Reply #12 on: August 25, 2018, 07:48:39 pm »
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?
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

  • Hero Member
  • *****
  • Posts: 524
Re: SQLite with SQLdb and Multithreading
« Reply #13 on: August 25, 2018, 07:53:31 pm »
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

  • Hero Member
  • *****
  • Posts: 524
Re: SQLite with SQLdb and Multithreading
« Reply #14 on: August 25, 2018, 07:56:14 pm »
As a last remark, it will be easier to help you, if you provide a very small example project with a concrete case.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus