3 ms·

With a single writer (as it the case with sqlite). You don't need transactions and rollbacks. As all writes happen in sequence. Each batch item can be a combin

With a single writer (as it the case with sqlite). You don't need transactions and rollbacks. As all writes happen in sequence. Each batch item can be a combination of read/write/update that happen in sequence and therefore can give you the same semantics as a traditional transaction/rollback. eg: - read -> Does the account have enough funds? - write -> transfer 100$ from this user to another account This is also much simpler to write than in other databases as you don't have to worry about n+1.

graemep 1y ago
I definitely want transactions and rollbacks even if writes happen in sequence. To go with your example, take something like 1) add $100 to this user's account 2) add $100 to the service fees account 3) deduct $101 from the other user's account to cover these Must all happen or none.
- andersmurphy 1y ago
  The batch is still atomic (as it's wrapped in a database transaction). So you batch items will never partially happen (say in the case of a crash). You do have to write your batch items so that they check their own constraints though. I.e check the accounts have funds etc.
  - hu3 1y ago
    But then rolling back the entire batch would potentially rollback inserts/updates/deletes from multiple independent requests. I need to bne able to rollback just the queries of a single request.
    - andersmurphy 1y ago
      You don't need to rollback, because you have already checked the invariants and the system is a single writer. Ah you're doing request response? sqlite/single writer fits much better with a CQRS still approach, so in my experience you move away from request/response to something push based. That being said even in a request/response model wrapping a request in a transaction is not a great idea. The minute you have anything that takes some time (slow third party) that transaction is going to sit open and cause trouble.

graemep 1y ago

I definitely want transactions and rollbacks even if writes happen in sequence. To go with your example, take something like 1) add $100 to this user's account 2) add $100 to the service fees account 3) deduct $101 from the other user's account to cover these Must all happen or none.

andersmurphy 1y ago
The batch is still atomic (as it's wrapped in a database transaction). So you batch items will never partially happen (say in the case of a crash). You do have to write your batch items so that they check their own constraints though. I.e check the accounts have funds etc.
- hu3 1y ago
  But then rolling back the entire batch would potentially rollback inserts/updates/deletes from multiple independent requests. I need to bne able to rollback just the queries of a single request.
  - andersmurphy 1y ago
    You don't need to rollback, because you have already checked the invariants and the system is a single writer. Ah you're doing request response? sqlite/single writer fits much better with a CQRS still approach, so in my experience you move away from request/response to something push based. That being said even in a request/response model wrapping a request in a transaction is not a great idea. The minute you have anything that takes some time (slow third party) that transaction is going to sit open and cause trouble.