5 ms·

Thanks for that! It seems trivial to enhance a DB wrapper to begin a transaction on the first query, and commit when the program finished without error. Like ho

Thanks for that! It seems trivial to enhance a DB wrapper to begin a transaction on the first query, and commit when the program finished without error. Like honestly, unless I totally missed something, why is that not built into everything it could be built into? Or put differently, is there ever a good reason to not use transactions?

josephg 3y ago
To be clear, you want to begin a transaction at the start of each http request and submit the transaction just before sending the response. (And error or retry if the transaction fails, depending on how your database works). But generally I agree. This pattern should generally be built into everything. Whenever I’m setting up an application server I usually whip up some middleware which takes care of this for me. The code is database specific - the semantics can differ slightly depending on what database you’re using. And it only matters for requests which issue multiple commands to the database before returning. (But this happens all the time in many applications. Especially if you’re using an ORM.)
- robocat 3y ago
  I presume that a DB lock for the length of the request processing time would cause contention when the number of concurrent session requests increases (with user growth). The article mentions this example: Some data structures aggressively tackle concurrency issues by using locking to only allow a single worker to access them at a time. One example of this is PHP's native session handler - if you send PHP two requests in the same session at the same time, they get processed sequentially! This approach is secure against session-based race conditions but it's terrible for performance, and quite rare as a result.
  - josephg 3y ago
    This is a misconception about how transactions are implemented by the database. Unlike php, a lot of modern databases implement transactions without locking out other concurrent queries from being executed at the same time. In serializable (linearizable?) databases, everything happens as if the queries happened one at a time, but using magic tricks like MVCC they don’t need to lock the database to do it. It’s cool stuff! So long as your transactions are short lived it usually performs way better than you’d expect. Modern databases are incredible feats of engineering.
    - krackers 3y ago
      Also optimistic locking

josephg 3y ago

To be clear, you want to begin a transaction at the start of each http request and submit the transaction just before sending the response. (And error or retry if the transaction fails, depending on how your database works). But generally I agree. This pattern should generally be built into everything. Whenever I’m setting up an application server I usually whip up some middleware which takes care of this for me. The code is database specific - the semantics can differ slightly depending on what database you’re using. And it only matters for requests which issue multiple commands to the database before returning. (But this happens all the time in many applications. Especially if you’re using an ORM.)

robocat 3y ago
I presume that a DB lock for the length of the request processing time would cause contention when the number of concurrent session requests increases (with user growth). The article mentions this example: Some data structures aggressively tackle concurrency issues by using locking to only allow a single worker to access them at a time. One example of this is PHP's native session handler - if you send PHP two requests in the same session at the same time, they get processed sequentially! This approach is secure against session-based race conditions but it's terrible for performance, and quite rare as a result.
- josephg 3y ago
  This is a misconception about how transactions are implemented by the database. Unlike php, a lot of modern databases implement transactions without locking out other concurrent queries from being executed at the same time. In serializable (linearizable?) databases, everything happens as if the queries happened one at a time, but using magic tricks like MVCC they don’t need to lock the database to do it. It’s cool stuff! So long as your transactions are short lived it usually performs way better than you’d expect. Modern databases are incredible feats of engineering.
  - krackers 3y ago
    Also optimistic locking