Forum > Database
Best practice for multiple / parallel queries (select, insert, ...)
Zvoni:
Hmmm….. true…
Hmmmm….. https://www.sqlite.org/lang_savepoint.html
Hi im Pascal:
Thanks for that link, interesting and confirms the suspicions
--- Quote ---The BEGIN command only works if the transaction stack is empty, or in other words if there are no pending transactions. If the transaction stack is not empty when the BEGIN command is invoked, then the command fails with an error.
--- End quote ---
Although if this is the case, I haven't quite understood yet why even a "stack" of transactions is implemented and required, if only one transaction can begin at any given moment. Will need to read up more on that.
Doing some more research, e.g., found this post: https://stackoverflow.com/questions/18791411/lazarus-sqldb-and-transactions, they seemed to have a similar problem.
Furthermore: https://forum.lazarus.freepascal.org/index.php?topic=33645.0 (was not easy to find this thread, I didn't know how to search it well, but now I found it).
I'm doing some more testing now and will come back with my conclusion(s).
Also weird, there seem to be actually three transaction options, because not setting anything (i.e., empty set, which appears to be the default) also gives a certain behavior.
* Empty TSQLTransactionOptions --> Looks like it will still implicitly start a transaction, but also the transaction somehow remains active until explicitly committed
* stoUseImplicit --> With this I'm acble to start multiple transactions in parallel, so it seems like somehow it implicitly starts it but also does not "block" it while running my SELECT query
* stoExplicitStart --> It literally "plays dumb" as expected, i.e., it will not even allow opening the first outer query with an error that transaction was not started
* [stoUseImplicit, stoExplicitStart] --> Results in equivalent behavior to only stoExplicitStart as far as I can tell
I guess I'll be doing some more reading in https://wiki.lazarus.freepascal.org/SqlDBHowto ...
Some more observations:
* Using stoUseImplicit will automatically deactivate the transaction after running Query.Open. However, if this is not used and instead manually ending the transaction after Query.Open is used, then the dataset is also inactive and cannot be iterated. sqoKeepOpenOnCommit would need to be used as additional query option
* On closer inspection (I think one needn't have to read the source code to be able to use a library, but oh well), it's not that stoUseImplicit "cancels" the transaction after Query.Open, but in fact it will ensure that the 'BEGIN' command is never issued to the database due to the interactions of TSQLConnection.StartTransaction and TSQLConnection.StartImplicitTransaction, which is not further overridden by TSQLite3Connection, however StartDBTransaction will call 'BEGIN'
* Setting Query.ReadOnly := true has no effect, i.e., the transaction will will start by issuing 'BEGIN' call. IMHO, it would make more sense if TSQLTransaction does not start a DB transaction if a query is readOnly.
* My guess is that needing multiple "nested" SELECT queries is probably just bad design of the SQL query itself and likely could be resolved by making a smarter / better query in the first place. However, it seems to work with one transaction when using stoUseImplicit and then having a separate query per SELECT.
* It seems TSQLQuery was already designed to edit or simultaneously INSERT data after a SELECT statement, however, I think it only works within the same table. If data shall be modified in other tables, I believe a separate query is needed.
* Another note: Examples usually show insert as assigning SQL, calling Query.ExecSQL and then Transaction.Commit, but there's actually a very significant performance impact for many insertions when calling Commit every time in a tight loop v.s. calling it at the end of the insertions, just something to keep in mind.
Navigation
[0] Message Index
[*] Previous page