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.
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.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.
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)
I don't use ZEOS, I use dbExpress (http://docwiki.embarcadero.com/Libraries/Tokyo/en/Data.SqlExpr.TSQLConnection) 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.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.
EDIT: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.
A commit should be done before every TSQLQuery.free, or?
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.?
https://www.sqlite.org/isolation.html
easy use a database and forget sqlite.https://www.sqlite.org/isolation.html (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.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.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.
:-\ :-\ :-\
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.
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.
It does not cover what happens if you're on multithreading.You are nitpicking.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?
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.
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.
Please show your code.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: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.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.
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.
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.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.
I doubt it's sqlite3, seems to be a SQLdb related issue.
Default is set to
DefaultOpenFlags = [sofReadWrite,sofCreate];
Which should be backwards compatible.
Hi, someone knows the bug report link and the status?
Hi, someone knows the bug report link and the status?
Link: 34274 (https://bugs.freepascal.org/view.php?id=34274) and 34278 (https://bugs.freepascal.org/view.php?id=34278)
This 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.