Comment by renegade-otter
10 hours ago
Most of the time you will save yourself a lot of grief by having a transaction decorator for each endpoint, as each HTTP call should be atomic. And then have another read-only decorator for RO transaction.
10 hours ago
Most of the time you will save yourself a lot of grief by having a transaction decorator for each endpoint, as each HTTP call should be atomic. And then have another read-only decorator for RO transaction.
That's an easy way to accidentally leave a xact open way too long. You might have enough connections in to support this normally, but when things go slightly wrong, they go very wrong.
Every RDBMS out there has an option to configure a DB-enforced transaction timeout. But that configuration is tied to a connection, not to a transaction. So using that configuration means giving up connection pooling. Which a lot of people don't want to do because it impacts latency and DB resource usage.
Every xact within a given connection will use the same connection-wide config, but the timeout is counting how long a single transaction takes, right? I don't see why you'd need to give up pooling for this unless you need different settings per xact.
1 reply →
+1 to this - I've griped pretty often that FastAPI's documentation implicitly recommends this (https://fastapi.tiangolo.com/tutorial/sql-databases/#create-...) by suggesting using dependency injection to manage database connections, only to start seeing connection pool exhausted errors as soon as the number of concurrent requests exceeds the number of allowed connections.
Oh wow. Dep injection for DB connections is nasty.
2 replies →
If your end point does something like:
* read from the database
* make a request to an API (or really any kind of long running non-database thing)
* write to the database
You're going to end up with a transaction that is open way longer than it needs to be, particularly if you're upstream API is misbehaving, which will potentially end up causing a lot more grief.