Strategies when using Async given that Oracle Connections are Not Thread Safe

[mkmoisen]

I have a fundamental question about using oracledb with async python.

Oracle connections are not thread safe. Two threads/coroutines cannot execute queries concurrently, whether you are in a transaction or not.

If we have a pool of 5 connections, and then 10 HTTP requests arrive simultaneously and are processed as coroutines on an event loop, and all 10 request an async oracle connection from the pool, only the first 5 will win, and the remaining 5 connections will hang until the connections have been returned to the pool.

Given this, I'm struggling to understand in what cases you would want to use an async connection in an event loop. It seems to me that in the common case, using threads with sync python should be adequate.

---

I suppose you could at the most basic level simply crank up the number of connections in your pool to some very high number, say 1000 connections. Oracle can handle 1000 open connections.

Is this what everyone who uses async connections is doing?

If your use case allows it, you could take a hot path API and instead of holding a connection open for the entirety of the API call, you could return a connection to a pool as soon as you are done executing a query, and then request a new connection when you need to execute a new query. For example if you had some hot path that was read only and didn't use a connection, and executed 5 queries over the course of the hot path, you could open and close a connection 5 times.

And if your hot path API has writes and if your use case permits it, you could commit after each write/use pragma autonomous transaction, return the connection to the pool, and then request a new one for the next write.

Are there any other strategies to consider?

[anthony-tuininga]

In general, asyncio is intended to simplify the code needed to handle multiple concurrent activities. Yes, you can use threads with sync mode in a very similar fashion, but it is far easier to mess up. You will also be stuck with the same problems that you suggested would occur for using asyncio! If you have a pool of 5 connections, the first 5 will proceed and do their work while the other 5 will "hang" (wait) for those first 5 to be completed. And the main drawback to using threads with sync mode is that those threads cannot do any useful work while they are waiting -- either for a connection to be available in the pool or for the database to complete its work.

If you have a lot of I/O in your application then asyncio will work well and be highly efficient. Using threads with sync mode in that situation will not improve things -- if you think otherwise, please let me know! If you have a lot of CPU intensive work, that should be done outside the event loop or you should avoid using asyncio completely. The scenario you have discussed, however, is not one that is a "problem" for asyncio. Perhaps I am missing something?

[mkmoisen]

Hi @anthony-tuininga

Let's assume for sake of argument that we are referring to

1. a database CRUD application that is an HTTP server handing HTTP requests exclusively,
2. this is a pure database application, we are only making IO requests to Oracle and we are not making any IO requests anywhere else.
3. Our application is IO-bound and we are not doing any CPU-intensive work.

In this case, our WSGI (or ASGI) server is taking care of all the thread (or coroutine) setup; we as developers are not doing that.

Because all of our IO is exclusively through Oracle connections, and because a single oracle connection can not be shared concurrently, the way I understand it is that there will no benefit to using an event loop, as the coroutines will not have any other useful work to do, since they will all get blocked waiting for a connection from our pool.

The only reason I can think of how async oracledb would be helpful in this situation is if you cranked up the pool to 1000 connections.

If you used 1000 connections in sync python, you would need 1000 threads. But if you used 1000 connections in async python, you might need only 10 threads for example.

Thank you.

[anthony-tuininga]

So the question is whether you want (a) a pile of threads sitting doing nothing waiting for connections to be available or for the database to finish its work or (b) one thread doing nothing waiting for connections to be available or for the database to finish its work. :-) So the benefit to using the event loop is that you don't have a lot of threads created that aren't doing any useful work.

Note that asyncio uses one thread for all of its work. Yes, you can setup an event loop in multiple threads -- but that isn't straightforward. I don't know if your WSGI server is taking care of that for you or not!