Comment by frollogaston
7 hours ago
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.
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.