Recent

Author Topic: Best practice for multiple / parallel queries (select, insert, ...)  (Read 4056 times)

Hi im Pascal

  • Jr. Member
  • **
  • Posts: 53
Hi all,

I've been using databases quite a lot in Lazarus/FPC (TSQLConnection, TSQLTransaction, TSQLQuery, ...) with SQLite3 and generally have been able to figure things out.
However, I often run into issues and I wonder if there is a design flaw or wrong approach, and what is the best recommended practice to do this.

Simple cases like this work flawlessly:
Code: [Select]
Setup query (I'm using a custom "query builder" class for this to prepare the statements)
Exec select
while not query.EOF
    do nice things with database row
    query.Next
Code: [Select]
Setup query
Exec insert

This is where things get dicey
Code: [Select]
Setup query
Exec select
while not query.EOF
    do nice things with database row
    I want to iterate further based on data, think like a nested loop for i in x, for j in x[i] ...
    Setup query2
    Exec select
    while not query2.EOF
        Nested query results processing
        query2.Next
    query.Next
That alone always seemed to not work, probably due to underlying stuff getting mixed up, like using the same query and/or transaction and/or connection. I even tried to hack my way out of it my taking ownership of the query and allocating a new one for the nested query, etc.
However, now when also wanting to do parallel insert, it simply won't work anymore, because apparently a select query still uses an open transaction, which I've come to realize is apparently because there are some crazy features in TSQLQuery where one can still update / change data even after only having performed a SELECT. So this does not work
Code: [Select]
Setup query
Exec select
while not query.EOF
    do nice things with database row
    Decide I need to enter a new row into database based on selected data
    Setup query2
    Exec insert
    query.Next

Ultimately I can come up with a few ideas, but I wanted to ask the expert opinion on whether there are any recommendations or if I'm completely on the wrong track:
  • I could first select, then parse everything into an internal model / structure (e.g., TVector of records) and then process / iterate over that instead (I know this could work)
  • I could create complete duplicate sets of (Connection, Transaction, Query) and operate them in parallel, e.g., at least one separate one for reading and writing (not tried yet)
  • ... some other elegant options I don't know about, perhaps using a single query, or using multiple (Transaction, Query) pairs within a Connection, although I doubt that will work, due to already having the "No transaction within an transaction" error.

Edit:
Tried option 2, still does not work due to multiple transaction error, so having only a single active transaction seems a global restriction per database even and not just per connection.

> My current working direction is now option 1 which seems to be working ok.
« Last Edit: April 06, 2024, 05:47:56 pm by Hi im Pascal »

Zvoni

  • Hero Member
  • *****
  • Posts: 2410
Re: Best practice for multiple / parallel queries (select, insert, ...)
« Reply #1 on: April 06, 2024, 04:56:25 pm »
You do know that you can execute Inserts, Updates and Deletes just with the connection object?
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Hi im Pascal

  • Jr. Member
  • **
  • Posts: 53
Re: Best practice for multiple / parallel queries (select, insert, ...)
« Reply #2 on: April 06, 2024, 05:07:08 pm »
You do know that you can execute Inserts, Updates and Deletes just with the connection object?
Yes, I do. But can I do it in parallel, is the problem...
Edit: Ah, you mean only TSQLConnection without Query and Transaction, I was not aware if that's the case. However, I use paramter substitution / prepared statements heavily, which according to the docs would not be supported here, and anyway it uses a default transaction in the background, which I'm guessing still would conflict in the same way with any other transaction started due to an insert statement.
« Last Edit: April 06, 2024, 05:11:49 pm by Hi im Pascal »

Zvoni

  • Hero Member
  • *****
  • Posts: 2410
Re: Best practice for multiple / parallel queries (select, insert, ...)
« Reply #3 on: April 06, 2024, 08:13:39 pm »
Well, then there is your answer: use a second transaction (you’re already using a second query).

One caveat: the „inner“ transaction MUST be committed or rolled back before continuing with the outer one
« Last Edit: April 06, 2024, 08:15:49 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Hi im Pascal

  • Jr. Member
  • **
  • Posts: 53
Re: Best practice for multiple / parallel queries (select, insert, ...)
« Reply #4 on: April 07, 2024, 03:52:22 am »
Well, then there is your answer: use a second transaction (you’re already using a second query).

One caveat: the „inner“ transaction MUST be committed or rolled back before continuing with the outer one

Are you sure? I'll have to try again tomorrow... I will give more concrete error messages. I thought I distinctly remembered that merely attempting to execute another query with an "inner" transaction already raised an exception along the lines of "Cannot start another transaction within a transaction".

Zvoni

  • Hero Member
  • *****
  • Posts: 2410
Re: Best practice for multiple / parallel queries (select, insert, ...)
« Reply #5 on: April 07, 2024, 11:48:11 am »
Hmmm….. true…

Hmmmm….. https://www.sqlite.org/lang_savepoint.html
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Hi im Pascal

  • Jr. Member
  • **
  • Posts: 53
Re: Best practice for multiple / parallel queries (select, insert, ...)
« Reply #6 on: April 07, 2024, 02:47:41 pm »
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.
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.
« Last Edit: April 07, 2024, 11:23:57 pm by Hi im Pascal »

 

TinyPortal © 2005-2018