CODEWITHSUNDEEP
pythondatabaseweb-applicationsflask
max
max

Reputation: 52323

Reusing database connection for multiple requests

If I don't need transactions, can I reuse the same database connection for multiple requests?

Flask documentation says:

Because database connections encapsulate a transaction, we also need to make sure that only one request at the time uses the connection.

Here's how I understand the meaning of the above sentence:

Python DB-API connection can only handle one transaction at a time; to start a new transaction, one must first commit or roll back the previous one. So if each of our requests needs its own transaction, then of course each request needs its own database connection.

Please let me know if I got it wrong.

But let's say I set autocommit mode, and handle each request in a single SQL statement. Or, alternatively, let's say I only read - not write - to the database. In either case, it seems I can just reuse the same database connection for all my requests to save the overhead of multiple connections. But I'm not sure if there's any downside to this approach.

Edit: I can see one issue with what I'm proposing: each request might be handled by a different process. Since connections should probably not be reused across processes, let me clarify my question: I mean creating one connection per process, and using it for all requests that happen to be handled by this process.

On the other hand, the whole point of (green or native) threads is usually to serve one request per thread, so my proposed approach implies sharing connection across threads. It seems one connection can be used concurrently in multiple native threads, but not in multiple green threads.

So let's say for concreteness my environment is flask + gunicorn with multiple multi-threaded sync workers.

Upvotes: 4

Views: 3280

Answers (1)

max
max

Reputation: 52323

Based on @Craig Ringer comment on a different question, I think I know the answer.

The only possible advantage of connection sharing is performance (other factors - like transaction encapsulation and simplicity - favor a separate connection per request). And since a connection can't be shared across processes or green threads, it only has a chance with native threads. But psycopg2 (and presumably other drivers) doesn't allow concurrent access from the same connection. So unless each request spends very little time talking to the database, there is likely a performance hit, not benefit, from connection sharing.

Upvotes: 2

Related Questions