SQLite Forum

Cancelling the query
Login

Cancelling the query

(1) By oneeyeman on 2021-03-18 23:55:42 [link] [source]

Hi, ALL,

There is sqlite3_interrupt() which should cancel the currently running operation. However I have questions about it:

  1. Lets say I have a thread that shuold continuously running and query the DB for a new tables. And at some point of time I will run the SELECT statement with _prepare/_step/_finalize again in a thread. Now when I call sqlite3_interrupt - which operation will be cancelled?

  2. What will happen with the statement handle after this call? Should there be a _finalize call?

  3. Is there another SQLite API that will allow to cancel the running statement (see question 1)?

Thank you.

(2) By Larry Brasfield (larrybr) on 2021-03-19 00:23:11 in reply to 1 [link] [source]

The flag set by sqlite3_interrupt() is checked when the VDBE code loops, which is during sqlite3_step() operation. There will be no interruption of prepare and certainly not of finalize.

Nothing untoward happens to SQLite library managed state. The usual rules apply to the user helping the library do so.

If you cannot be sure that a connection may become closed just before calling sqlite3_interrupt(), then using the sqlite3_progress_handler() interface would be a safer way to effect interruption. The progress callback can cause the VDBE execution in progress to be stopped, with no danger of the connection being closed while the callback runs.

(4) By oneeyeman on 2021-03-19 00:44:15 in reply to 2 [link] [source]

Hi, Larry,

The flag set by sqlite3_interrupt() is checked when the VDBE code loops, which is during sqlite3_step() operation. There will be no interruption of prepare and certainly not of finalize.

That's understandable.

Nothing untoward happens to SQLite library managed state. The usual rules apply to the user helping the library do so.

OK, so I will still have to finalize the statement right?

If you cannot be sure that a connection may become closed just before calling sqlite3_interrupt(), then using the sqlite3_progress_handler() interface would be a safer way to effect interruption. The progress callback can cause the VDBE execution in progress to be stopped, with no danger of the connection being closed while the callback runs.

Its not about closed connection.

Like I said I am running a thread checking if a new table is created by querying sqlite_master. And then I want to run another thread where I will execute SELECT FROM abc; When I hit ESC I want to interrupt the second SELECT statement. I want the first thread to keep running querying the sqlite_master.

So it looks like the way to go is to use progress_handler().

Thank you.

(7) By Larry Brasfield (larrybr) on 2021-03-19 01:24:59 in reply to 4 [link] [source]

The usual rules apply to the user helping the library [manage its state].

OK, so I will still have to finalize the statement right?

That is one of the usual rules. There are others.

I said I am running a thread checking if a new table is created by querying sqlite_master. And then I want to run another thread where I will execute SELECT FROM abc; When I hit ESC I want to interrupt the second SELECT statement.

That looks a lot like busy-waiting. I would be thinking about more direct coordination between threads, and awaking ones that suddenly have something to do with mutexes.

So it looks like the way to go is to use progress_handler().

If the connection you would be passing into sqlite3_interrupt() is sure to remain open during that call, that would be the simplest approach.

(3.1) By Keith Medcalf (kmedcalf) on 2021-03-19 00:33:24 edited from 3.0 in reply to 1 [link] [source]

And at some point of time I will run the SELECT statement with _prepare/_step/_finalize again in a thread. Now when I call sqlite3_interrupt - which operation will be cancelled?

sqlite3_interrupt(sqlite3*) sends an interrupt to all statements currently executing on the connection and continues to do so until all statements executing on the connection have been interrupted, at which point the interrupt flag on the connection is reset. See the documentation https://sqlite.org/c3ref/interrupt.html, particularly the last paragraph.

What will happen with the statement handle after this call? Should there be a _finalize call?

Which statement handle? Do you mean the handle of the statement which was interrupted and returned an SQLITE_INTERRUPT from sqlite3_step? There is no effect on the statement other than stopping its execution. If you are done with the statement you should finalize it to prevent memory leaks. See also the documentation referenced above.

Is there another SQLite API that will allow to cancel the running statement (see question 1)?

That would depend on what you mean. You can stop executing a statement at any time and reset/finalize it.

In other words, you run a statement like this:

statement = sqlite3_prepare(...)
while (rc = sqlite3_step(statement)
{
  if ((...I feel like stopping ...) || (rc != SQLITE_ROW))
     break;
  ... process the row ...
}
sqlite3_reset(statement)
sqlite3_finalize(statement)

(5) By oneeyeman on 2021-03-19 00:57:00 in reply to 3.1 [link] [source]

Hi, Keith,

I understand.

The problem is that I run _prepare/_step/finalize in a thread lunched from GUI. And when the user press a {hot} key I want the thread terminated and statement gracefully closed.

Now what I was thinking is to call some kind of function to cancel statement processing when a thread is deleted.

Or am I looking at it wrong?

Thank you.

(6) By Keith Medcalf (kmedcalf) on 2021-03-19 01:24:15 in reply to 5 [source]

Have the GUI set a flag that stops the loop from looping.

eg:

static int keepgoing;

void stopnow()
{
 keepgoing = 0;
}

void enterthread()
{
 keepgoing = 1;
 prepare(...)
 while (keepgoing)
 (
  step(...)
  reset(...)
  sleep(...)
 }
 finalize(...)
}

Dispatch enterthread on its own thread. Then just have the GUI change keepgoing to 0 (call stopnow()). Unless of course the statement is taking hours to run ... if it is only taking a few miliseconds then the overhead of even bothering to do anything else is hardly worth it.

(8) By Larry Brasfield (larrybr) on 2021-03-19 01:31:10 in reply to 5 [link] [source]

I second Keith's reply, and respond only to add to it.

You will be better off, over your multi-threaded programming life, if you take terminating threads out of your repertoire. Arrange for signals of various kinds to be passed between threads so that they know what do, synchronized to their own execution. That may include their own demise, gently effected by returning, often after doing whatever resource cleanup is appropriate. Keith's suggestion is aligned with that approach.

Think of yourself as the orchestrator rather than the forcer.

(9) By Simon Slavin (slavin) on 2021-03-19 16:14:10 in reply to 5 [link] [source]

In this case I suggest you handle the matter inside your application programming rather than using SQLite to do it. Set a flag when the user presses the key, and check that flag each time through your call of _step().

The thing that makes me think this is that you're using _step(), and that the query is done frequently. Presumably you've optimized the query: it has an ideal index and no one call to _step() takes much time. So checking for the flag each time you call _step() won't ever cause much delay.

sqlite3_interrupt() can be very useful for unpredictably queries on huge databases which might take unpredictable times. It can also be useful if you are using _exec() and can't check progress every time through the _step() loop. But in this case you have superior control and superior knowledge about what's going on and can use this to your advantage.