3 ms·

That's a fair point -- most (all?) SQL servers use the connection limit as an indirect transaction limit, rather than separating the two (in which case BEGIN TR

That's a fair point -- most (all?) SQL servers use the connection limit as an indirect transaction limit, rather than separating the two (in which case BEGIN TRANSACTION would sometimes have to block or fail). Are large numbers of concurrent transactions really an issue in practice though? There may be some per-transaction overhead, but I haven't seen any stern warnings about it, e.g. in the Postgres docs for max_connections. > Thus, a backend concurrent connection (i.e. transaction) cap of N, translates to O(N) concurrent MVCC "world states" that the DB engine has to keep track of. No matter how efficient the DB engine is at doing that, that's still a lot of state! I don't quite see what you mean about world state. An abort just rolls back changes made by the aborted transaction, so I would expect SQL engines to store a diff of each updated row. And if that's the case, it seems like a single huge transaction could use as much memory as many small transactions, if the same rows were modified.

deleted 8y ago
[deleted]
anarazel 8y ago
> Are large numbers of concurrent transactions really an issue in practice though? There may be some per-transaction overhead, but I haven't seen any stern warnings about it, e.g. in the Postgres docs for max_connections. There's definitely scalability implications to higher number of established connections in postgres. Essentially computing a "snapshot" (visibility information for queries etc, needs to be computed at least once a transaction) is O(#established-connections). If you get in the high hundreds on a large NUMA machine, and you run tiny fast transactions, you definitely can observe the overhead of that. >> > Thus, a backend concurrent connection (i.e. transaction) cap of N, translates to O(N) concurrent MVCC "world states" that the DB engine has to keep track of. No matter how efficient the DB engine is at doing that, that's still a lot of state! There's definitely ways to handle that better than O(N), at least in the average case.
- derefr 8y ago
  > There's definitely ways to handle that better than O(N), at least in the average case. O(N) root-level snapshot objects of the state, I mean. Like O(N) git commits. The minimum size of the persisted MVCC snapshot data, probably just scales with O(log N) of the number of connections, depending on what sort of data structure you're using for copy-on-write'ing your snapshots.
  - anarazel 8y ago
    Most databases don't have "root level snapshots of the state" in the way you appear to imagine them. Leaving trivial per-transaction information ('did this transaction id commit', ~a few bits per xact) aside, usually it won't scale with the number of snapshots, but with the amount of change done by writing transactions.

deleted 8y ago

[deleted]

anarazel 8y ago

> Are large numbers of concurrent transactions really an issue in practice though? There may be some per-transaction overhead, but I haven't seen any stern warnings about it, e.g. in the Postgres docs for max_connections. There's definitely scalability implications to higher number of established connections in postgres. Essentially computing a "snapshot" (visibility information for queries etc, needs to be computed at least once a transaction) is O(#established-connections). If you get in the high hundreds on a large NUMA machine, and you run tiny fast transactions, you definitely can observe the overhead of that. >> > Thus, a backend concurrent connection (i.e. transaction) cap of N, translates to O(N) concurrent MVCC "world states" that the DB engine has to keep track of. No matter how efficient the DB engine is at doing that, that's still a lot of state! There's definitely ways to handle that better than O(N), at least in the average case.

derefr 8y ago
> There's definitely ways to handle that better than O(N), at least in the average case. O(N) root-level snapshot objects of the state, I mean. Like O(N) git commits. The minimum size of the persisted MVCC snapshot data, probably just scales with O(log N) of the number of connections, depending on what sort of data structure you're using for copy-on-write'ing your snapshots.
- anarazel 8y ago
  Most databases don't have "root level snapshots of the state" in the way you appear to imagine them. Leaving trivial per-transaction information ('did this transaction id commit', ~a few bits per xact) aside, usually it won't scale with the number of snapshots, but with the amount of change done by writing transactions.