That’s what run_until_complete does, yeah. But the reason you would use it instead of asyncio.run is if you want to alternate between running async code for a while, and then freezing the async tasks while running some sync code, and then going back to running the same async code, etc. And for that use case, you can accomplish the same effect more simply by just calling your sync code from inside some async task.
Nothing will explode if you call sync code from inside async context. It’s just that while you’re doing that, none of the other tasks can make progress, because they all share the same thread and your sync code is hogging that thread until it finishes.
I know you’ve specifically said this is not what you’re looking for, but if it was me doing this, I’d put the sqlite logic into a thread and use something like https://github.com/aio-libs/janus to push data to it (the logic is just waiting on the queue until a poison pill comes out and doing inserts). Conceptually it’s the cleanest way. Maybe encapsulate the entire thing into a class so shutdown is easier.
My main problem with this is handling Ctrl-C. If I get a KeyboardInterrupt in the main thread, how does that cleanly get the sqlite thread to clean up the database connection and then terminate the thread? Because unless you do something special, threaded applications typically hang when you hit Ctrl-C because the main thread exits, but the worker threads still exist and are not daemons, so the process waits forever for the workers to complete. (And making the worker a daemon doesn’t help in this case because the DB needs to be tidied up).
This is why I prefer not to use threads - but if there’s a clean, easy to use idiom for making sure that Ctrl-C handling works, I’m happy to use it. I’m not aware of anything that isn’t fiddly to get right, though.
I can’t specifically attest for how clean it is because I don’t frequently use signal handling myself, but it might be worth registering a specific signal handler function for SIGINT so that the first Ctrl+C can close your db connections and join the worker threads instead of just exiting the main thread (with signal.signal(signal.SIGINT, custom_handler)). I don’t believe there’s much you can/should do about multiple Ctrl+C, such as if it’s sent while in the handler (other than except KeyboardInterrupt); but that would likely be an improvement.
Unfortunately, if the worker thread is in the middle of a long blocking DB operation, the interrupt in the main thread is not passed to the DB, so nothing can interrupt that. Even making the thread a daemon doesn’t fix that, all it does is allow the whole process to die before the DB operation is complete, but that gives no chance for cleanup. Databases are built to handle unexpected termination, but there’s still consequences (sqlite, for example, leaves a journal file lying around which is tidied up when you next open the database and do an update - and if you lose the journal file before then you’re screwed).
Here’s a simple reproducer (that doesn’t involve asyncio):
from time import sleep
if __name__ == '__main__':
t = threading.Thread(target=big_block)
There’s no way that I know of to modify this so that a Ctrl-C will terminate the process cleanly (printing “cleanup”) before 10 seconds have passed. And that’s basically why blocking operations on threads are problematic. (Disclaimer: I’ve only tried this on Windows, I don’t know Linux signal handling so it may be different there - but that’s no help to me).
Yes, that seems to be the best option we have right now - or I do what I’d basically decided to do which is do the blocking DB operations on the main thread.
Hmm, I wonder if it’s possible to have the main thread be my DB consumer loop, with the async stuff happening on a worker thread? That might be viable, I guess, although it starts to seem fairly over-engineered (block on the main thread is 100% sufficient for my use case, at this point I’m just speculating on other options “in case I ever need them”…)
Note that even if you run blocking sqlite queries on the main thread, control-C won’t actually interrupt any sqlite database queries, since Python doesn’t run signal handlers while the main thread is blocked in C code. So if you hit control-C while sqlite is working, then you’re still going to be stuck waiting until sqlite finishes before Python notices and you get the KeyboardInterrupt.
In practice I’m guessing this is probably fine for your use case, because all your sqlite queries probably complete in a few milliseconds. But the point is that running in the main thread isn’t actually magic :-).
You can get the same semantics with threads by running each sqlite query in its own thread, and then when control-C arrives, wait until the thread completes before raising KeyboardInterrupt. I agree that implementing this by hand is probably super annoying and would have lots of tricky edge cases. But it’s the sort of thing a framework can handle for you, e.g. these are the semantics that you’d get “for free” if you used trio.to_thread.run_sync. And in general, this is about the best you can hope for when working with arbitrary C code.
With asyncio specifically, I don’t think it can handle this, because in general when you hit control-C on an asyncio program then the results are undefined. Usually it makes the event loop stop at some arbitrary point and whatever happens from that happens, including background threads continuing to run, maybe. So if you’re using asyncio then keeping the db access in the main thread is a reasonable idea.
In theory, the ideal solution would be to delegate the sqlite queries into threads, but also integrate it into some kind of overarching cancellation system so that when control-C comes in, it eventually invokes sqlite_interrupt to tell sqlite that we want the thread to terminate ASAP. Regular synchronous Python and asyncio don’t currently have the plumbing to make this possible. In Trio it’s possible in principle, though you’d definitely have to do some work to wire everything up.
In practice, blocking on the main thread is fine, but not because my queries are small. I’m bulk-updating a 2.5 GB database, with a 3-million row table, so queries taking a long time is definitely a problem. Blocking is fine because I don’t need to interrupt that often (and when I do, proper cleanup is more important to me than aborting the current update).
As a side note, I did think that Ctrl-C interrupted C calls - it does interrupt time.sleep, but apparently it doesn’t interrupt sqlite3 calls. I’d argue that’s a bug in the sqlite3 module (albeit not a particularly critical one).
That bothers me a lot, though. Are you saying that Ctrl-C is unsafe to use in an async program? That sounds like a pretty worrying limitation. I’d hope that the event loop is exception-safe, in the sense that if an exception happens (ctrl-C or anything else) it would propagate out of the asyncio.run (or equivalent) cleanly, without the asyncio internal data structures being corrupted.
I’d assumed that something like the following was true. Within async code, when an exception occurs everything bar at most one routine is sitting in an await. That one routine would see the exception just as normal synchronous code does, and if it doesn’t handle the exception, it would propagate back up out of an (essentially arbitrary) await in some other routine. The code calling that await then gets to handle or ignore the exception, and so on back to asyncio.run.
That’s not IMO “undefined” even though the exact flow is “arbitrary”. And certainly an exception could mean that any number of await calls never actually return. But it’s possible to reason with, and I’d certainly hope that something like this was the case and not “if you hit Ctrl-C on your async program, say goodbye to any assurance of data integrity”! I do admit that it’s a rather low-level description of what’s going on, and may not be easily translated into something higher level, but "in the face of exceptions, not every await will resume before the exception propagates out of asyncio.run" seems sufficient…
Aha! I hadn’t noticed the existence of sqlite3.Connection.interrupt(), but using that, I suspect it’s possible to put together some sort of cancellation system, as you suggest. I’m not sure why you say “regular synchronous Python and asyncio don’t currently have the plumbing to make this possible”, but I suspect if I try I’ll find out soon enough
By the way, thanks to everyone for all the information - it’s given me a lot of food for thought, and significantly advanced my understanding of asyncio.
One other piece of advice: if you need to scrape a lot of web pages, I would use a semaphore or some other form of limiting concurrency so you’re only fetching a limited number at a time (although that number can still be high). I remember spawning a thousand coroutines to do a similar workload at once, and a bunch failing due to various timeouts.
@pf_moore When I’ve had to do this in the past, I’ve used aiosqlite. The main advantage (for me) is that because everything is async, you don’t have to deal with cross the sync/async boundary from the perspective of application design.
edit: oops I see you’re already aware of it, carry on.
It doesn’t interrupt the sleep in the background thread, so if I increase the sleep to 20 seconds, the process continues running for 20 seconds after I hit Ctrl-C.
For sqlite I may be able to get around this by clever use of connection.interrupt() but the general problem remains. (It’s not an asyncio issue as such, it’s basically a fundamental issue with threads, and why “defer to a thread” isn’t always as useful an approach as you might want.)
Thanks, that’s a good tip, I’ve been getting the odd timeout and didn’t realise why.
The reason control-C manages to interrupt time.sleep is because there’s some platform-specific machinery in CPython’s low-level signal handling code and in time.sleep that manually hooks them together. If you search for _PyOS_SigintEvent in the CPython source you can see the details. It’s a very manual kind of integration. I think if you wanted to make something similar work for the sqlite3 module, you’d have to change both upstream sqlite and CPython’s low-level signal handling code. It’s one of those things where it seems like it ought to be simple, but then you open the box and this ocean of gnarliness spills out.
A regular exception inside a user task is totally fine of course. But KeyboardInterrupt is special and weird, because it can suddenly materialize at any arbitrary bytecode instruction in your program. And I’m pretty sure that a KeyboardInterrupt at the wrong time can in fact corrupt asyncio’s internal data structures. It probably won’t, like, burn down your house or anything, but if asyncio is in the middle of manipulating some internal data structure, then a KeyboardInterrupt in the middle of that will generally leave the structure in an inconsistent state.
That’s what Trio does, but accomplishing this requires deep wizardry and hooks throughout Trio’s internals to detect when a control-C happens at an “unsafe” moment, and delaying the KeyboardInterrupt until it’s safe to handle. asyncio doesn’t have anything similar.
In asyncio IIUC you want to guarantee fully defined behavior on control-C, then the official way is to use loop.add_signal_handler to convert control-C into a regular asyncio event that you can handle however you like. Unfortunately, this isn’t implemented on Windows…
When calling sqlite synchronously in the main thread: you want to make it so that control-C causes sqlite3_interrupt to be called immediately, without waiting for the current operation to finish. How can you wire these together? You can’t use the signal module to register a signal handler, because Python doesn’t run signal handlers while the main thread is blocked in C code…
When calling sqlite in a worker thread from asyncio: as described above, asyncio doesn’t guarantee that the event loop keeps functioning at all after control-C, so there’s not much point in trying to make stronger guarantees about specific operations… Also, just in general, I’m not sure how to define custom cancellation code in asyncio, because when integrating with other systems like threads you have to use Future. And Future's cancellation semantics kind of hard-code that when a Future is cancelled, the work actually keeps going in the background. In particular, Future.cancel immediately resolves the Future as cancelled, so you can’t wait for the work to clean up, and there’s no easy way to detect when Future.cancel has been called so you can issue a sqlite3_interrupt or anything.
Wow, that seems pretty bad to me. You’re basically saying that if I write an asyncio program, and the user hits Ctrl-C, then I may not even get control back to my application in a way that will allow me to sanely exit? In the sense that I do all of my tidying up, whatever that may involve, but that’s still not good enough?
Worse and worse…
I shall read that with interest (and a fair amount of fear and trepidation )
None of this gives me any sense of security when it comes to writing a database-backed application with asyncio. If I can’t get enough control back to cleanly tidy up my database connection, I need to assume any Ctrl-C is going to act as a connection abort. Databases are robust by design, so they can survive this, but it feels like subjecting them to unnecessary levels of abuse…
I’m very carefully trying not to look at this in terms of framework comparisons, but this would be a big selling point to me for trio. Whether it’s enough to counterbalance the “every async library supports asyncio” question, I’m not sure I can judge yet.
Thinking about it, should this be raised as a bug against Python? Something like “asyncio doesn’t protect its internal structures against the end user hitting Ctrl-C while the event loop is running”.
I’m not particularly comfortable raising a bug where I can’t offer a test case to demonstrate the problem, and I can’t clearly express what I want to see happen, beyond "please write your code to protect against KeyboardInterrupt". But conversely, I don’t like the idea that this isn’t recorded anywhere as being an issue.
Just found this, did you ever file a bug? Because this ought to be just fixed. It was perhaps acceptable when asyncio was young (in the days when it was named Tulip and our philosophy was “don’t catch BaseException”. But we’ve mellowed about that…