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.

▲ frollogaston 12 hours ago | parent | next [-]

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.

▲ 10000truths 8 hours ago | parent | next [-]

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.

▲

frollogaston 8 hours ago | parent [-]

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.

	▲	10000truths 7 hours ago \| parent [-]
		Pooling means you're sending multiple queries (from different HTTP requests) over the same DB connection. A well-designed DB wire protocol will allow for pipelining those queries: [send Query 1] -> [send Query 2] -> [send Query 3] -> [receive Result 1] -> [receive Result 2] -> [receive Result 3] But in Postgres and MySQL, pipelined queries are not executed in parallel. They're just queued up for a single thread (per connection) to execute sequentially. Thus, if Query 1 is a transaction that takes too long, then it ends up blocking the execution of Query 2 and Query 3.

▲ mrkaye97 12 hours ago | parent | prev [-]

+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.

▲ frollogaston 12 hours ago | parent [-]

Oh wow. Dep injection for DB connections is nasty.

▲ alfons_foobar 11 hours ago | parent [-]

I might be outing myself as a noob here, but... what is the (better) alternative?

▲ 0x696C6961 11 hours ago | parent [-]

You inject the pool itself.

	▲	alfons_foobar 38 minutes ago \| parent [-]
		Sorry, I am being dense... how does that solve the problem? I still have to get a connection from the pool, I just do it inside the function body now, right? So this `@app.get("/users") def get_users(conn = Depends[get_db_conn]): users = conn.execute("SELECT * FROM users") return users` would become that instead: `@app.get("/users") def get_users(pool = Depends[get_db_pool]): with pool.get_conn() as conn: users = conn.execute("SELECT * FROM users") return users` But I still need enough connections in the pool to handle all concurrent requests, no?

▲ mikeocool 12 hours ago | parent | prev [-]

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.