Forum > Databases
SQLite with SQLdb and Multithreading
asdf121:
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:
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:
--- 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.)
--- End quote ---
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:
--- Quote from: mangakissa 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.)
--- End quote ---
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.
--- End quote ---
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:
--- Quote from: Thaddy 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.
--- End quote ---
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 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.)
--- End quote ---
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.
--- End quote ---
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?
Navigation
[0] Message Index
[#] Next page