SQLite Forum

Nesting of statements
Login

Nesting of statements

(1) By Mike (mike.mcternan) on 2020-09-25 08:54:18 [link] [source]

I'm looking for rules on nesting SQL statements at the C API level and can't find anything definitive on this site.

Here's an example of the sort of think I'm trying to do:

sqlite3_prepare_v2(db, "SELECT id FROM table", -1, ps, NULL);

while(sqlite3_step(ps) == SQLITE_ROW)
{
  int id = sqlite3_column_int(ps, 0);

  sqlite3_execf(db, "UPDATE table SET x=1 WHERE id=?", id);
}

sqlite3_finalize(db);

This example is very simple and contrived - it's easily converted into a single SQL statement which is better in every way. However, in the real cases there is some complex business logic between the select and the update with features not easily replicated in SQL.

If the above isn't in a transaction, it looks like we get API misuse returned when trying the update. If in a transaction, it is okay (except the risk of SQLITE_BUSY due to upgrade from reader to writer - using an immediate transaction avoids that).

So I think I've worked out the rules, but it would nice to have a canonical reference in case things change in the future!

(2) By Gunter Hick (gunter_hick) on 2020-09-25 11:33:20 in reply to 1 [link] [source]

You cannot call sqlite3_finalize() on a connection, only on a statement.

The first call to sqlite3_step() on the SELECT statement creates a transaction.

This is kept open (and upgraded to a write transaction) when your UPDATE statement is prepared/stepped/finalized within sqlite3_exec.

You are losing performance here by repreparing the same UPDATE over and over instead of just once(outside the loop) and calling bind() and step() inside.

The transaction is terminated automatically when the SELECT completes.

Please note that you need to check the return status of all function calls.

(7) By Mike (mike.mcternan) on 2020-09-25 16:14:57 in reply to 2 [link] [source]

Sorry - yes it is just example code to illustrate the nesting of an update within a select, which for reasons of the application logic, is hard to avoid.

You are right about there always being a transaction.

(10) By Keith Medcalf (kmedcalf) on 2020-09-25 18:11:31 in reply to 7 [link] [source]

SQLite3 does not support the "FOR UPDATE OF column-list ON table" so you should not be performing UPDATEs interleaved on the same connection as you are using for the SELECT since isolation only exists between connections and not between statements.

You should either be using separate connections (so that isolation is maintained) OR you should be executing the entire select and collecting the necessary data BEFORE performing the UPDATE as part of the same transaction on the same connection. You also need to ensure that you maintain proper stability during the process.

Interleaving the row fetching from a SELECT with concomitant UPDATE involving common tables in both is fraught with peril.

The proper solution is to not do that.

(14) By Mike (mike.mcternan) on 2020-09-28 05:32:08 in reply to 10 [link] [source]

Interleaving the row fetching from a SELECT with concomitant UPDATE involving common tables in both is fraught with peril.

The proper solution is to not do that.

It would be nice if the sqlite manual said this somewhere, and if it could reliably detect and report API misuse in such cases (when SQLITE_ENABLE_API_ARMOR is set).

(15) By Keith Medcalf (kmedcalf) on 2020-09-28 06:36:55 in reply to 14 [link] [source]

Interleaving the row fetching from a SELECT with concomitant UPDATE involving common tables in both is fraught with peril.

The proper solution is to not do that.

It would be nice if the sqlite manual said this somewhere, and if it could reliably detect and report API misuse in such cases (when SQLITE_ENABLE_API_ARMOR is set).

Everyone does know that since about the very first SQL driven RDBMS system was conceived more than half a century ago. The fact is that half a century ago "special steps" and "language semantics" were introduced to deal with this issue and still exist (in various states of working for varying definitions of working) to this very day (the common case seems to be that the "language semantics" have been more or less maintained but the "working correctly" part has been totally ignored in the Eternal Septemberite drive to Form over Function).

It should be noted, however, that this is not just an SQL and RDBMS issue as it has existed for thousands upon thousands of years -- even the Monks indexing their scroll collections had the same problem with concurrent updating of the "index scroll" when the "researcher" was away with his concubine for a couple of weeks. Just because you remember where you were in the "index scroll" does not prevent things from being moved around and added or deleted while you go away and on a dalliance. The only way you can be sure that you have a "stable view" of the "index scroll" is to wait while the Monks make an exclusive copy for your use.

When using so-called "navigational databases" everyone and their mother knew that just because you know where "here" is does not mean that you cannot update "before here" and "after here" and that what you do "here and now" affects "the here after". In fact, you probably learned that concept by the time you were 5 years old. Why would you suddenly believe that what you do here and now does not affect the here after when it becomes here now?

SQLite3 supports neither the "special steps" nor "langauge semantics" to implement this complicated (non-Lite) process by magic. You must implement this yourself as there is no syntax in SQLite3 that will tell SQLite3 to do it for you.

As to reporting misuse, there is no way to know that you are doing something ill-conceived. As a theoretically competent user of a tool, it is your job to use it competently. There is no way for, for example, for a band saw to know that it is cutting off your hand and not just cutting through a particularly dense knot in the wood.

And it is documented somewhere as I have seen it even though it is obvious to any casual observer (probably in one of the pages documenting isolation since it is an isolation (or rather, a lack thereof) issue).

(3) By David Raymond (dvdraymond) on 2020-09-25 13:08:25 in reply to 1 [link] [source]

I know this was just sample code, but of note I think that updating the same table that you're iterating over is fraught with peril. Better would be something like getting all the IDs you want into a temp table first, then doing the updates based off of what's in the temp table. By updating the table you're iterating over you could miss records, get duplicates, maybe end up in an unending loop, who knows. (Well, the others on this list with more experience than me probably know, and can chime in)

(6) By Gunter Hick (gunter_hick) on 2020-09-25 14:07:26 in reply to 3 [link] [source]

Indeed it is a bad idea to update the fields of a record that determine it's location in the index used for the scan. AFAIK SQLite will continue with the "next record" based on the updated record's position in the index being scanned. Depending on what you did to the fields used in the index, you may skip forwards or backwards, thereby processing some records multiple (up to infinity) times and/or not processing some records.

(9) By Mike (mike.mcternan) on 2020-09-25 16:30:59 in reply to 3 [link] [source]

Using a temporary table of Ids is a pattern I've used before in this type of situation. It works well when the data set isn't too large - it's a good solution, thank you.

Alternatively using a temporary table for the calculated results is a different way I've done similar.

I guess what I was hoping for though were some rules on if UPDATEs during a SELECT are ever deterministic and safe or not. For example, adding an ORDER BY clause on the select could guarantee the output order with respect to some index, and possibly make things more predictable...

What I'm hearing though is that nesting UPDATES to a table while in a SELECT is unpredictably unsafe - though most of the time sqlite doesn't seem to notice and just skips rows or returns a subset. I'm not sure if it was me or sqlite, but I may have once got into an infinite loop too... :-/

Anyway, this seems like a bit of a trap in an otherwise impeccable bit of software. It would be nice if there were either documentation highlighting the weakness inherent in such a use case, or it was consistently trapped as API misuse.

I believe other databases handle such a use case as one may expect, but then they have MVCC or lots of other fancy stuff - the cost of which I don't really want in my embedded applications for which sqlite is perfect!

Thank you all!

(11) By Keith Medcalf (kmedcalf) on 2020-09-25 19:21:47 in reply to 9 [source]

I believe other databases handle such a use case as one may expect, but then they have MVCC or lots of other fancy stuff - the cost of which I don't really want in my embedded applications for which sqlite is perfect!

No database supports inverleaving updates within a select. The exception, of course, are those database which support BOTH "SELECT ... FOR UPDATE ..." and "UPDATE ... WHERE CURRENT OF CURSOR". "Proper support" for this feature is limited to RDBMS systems which were originally designed for "Big Iron" (as in the dinosaur pen) such as DB2 and Oracle. Some other bitty-box RDBMS systems seem to claim to support the syntax, but do not actually support the process.

I believe other databases handle such a use case as one may expect, but then they have MVCC or lots of other fancy stuff - the cost of which I don't really want in my embedded applications for which sqlite is perfect!

With respect to "other databases that have lots of other fancy stuff", most notably client/server systems, appearing to support this is "I can understand why you might think that, but I could not possibly comment".

However, I will comment. Client/Server databases appear to support this because they perform the query in its entirety on the "server" before sending the results back to the "client" for processing. So although it may appear to the lay observer that the "update" is interleaved with the query, in actual fact the update is not interleaved with the select execution.

Like "scrollable cursors" which are implemented by massive amounts of code contained within the server and the client, SQLite3 does not incorporate the massive amounts of code or the buffering required to "make it appear to the lay observer" that these are actual real things which exist. With SQLite3 you need to write that massive amount of code and buffering yourself.

(4) By David Raymond (dvdraymond) on 2020-09-25 13:17:31 in reply to 1 [link] [source]

sqlite3_execf(db, "UPDATE table SET x=1 WHERE id=?", id);

Also, pardon the newbie question from me: What is sqlite3_execf? I don't see it in the C function list and sqlite3_exec has a completely different parameter list.

(5) By Gunter Hick (gunter_hick) on 2020-09-25 14:02:28 in reply to 4 [link] [source]

My guess is that the OP wrote that function himself and it calls sqlite3_bind() with the last parameter on the prepared statement.

(8) By Mike (mike.mcternan) on 2020-09-25 16:20:50 in reply to 5 [link] [source]

Correct, it's one of my helper functions like:

sqlite3_stmt *sqlite3_preparef_v2(sqlite3 *db, const char *fmt, ...) __attribute__ ((sentinel));
bool          sqlite3_queryf(sqlite3 *db, const char *fmt, ...) __attribute__ ((sentinel)); 

They take multiple arguments and bind the parameters using a printf-style format string. It's super convenient when you only need to bind parameters once, and check all the return codes. The queryf function is handy for updates or deletes.

(12) By anonymous on 2020-09-27 18:48:30 in reply to 1 [link] [source]

What kind of complex business logic between the select and update will be needed?

(13) By Mike (mike.mcternan) on 2020-09-28 05:24:07 in reply to 12 [link] [source]

Lots of if/else stuff, looking up some rules and limits in config files and then calling into some libraries.

I have used sqlite3_create_function() in the past for some stuff, but in this case SQL doesn't seem like the best language for the particular calculation/problem.