3 ms·

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

> 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 think any realistic implementation keeps information that increases at a rate this implies. Undo based systems have the undo size grow roughly at the amount of changes that aren't visible to everyone, and postgres style in-heap MVCC systems have similar amount of data growth inside the table. Usually the amount of that additional state is more correlated with the age of the oldest observer than with the number of concurrent observers. > If you allow clients unlimited concurrency, they can execute enough transactions to fill memory and disk with MVCC world-states, causing potential data loss (since the full disk may have prevented the DB engine from writing down a committed world-state.) I don't think any real-world DB implementation would work that way. The definition of committed in most of such systems include that the necessary data has been written to disk before signalling success for the commit. This problem also seems largely independent of connection/transaction limits.

derefr 8y ago
Ah, yeah, I didn't mean to suggest that O(N) snapshots translates to O(N) data usage in practice. It could if you wrote a really stupid RDBMS, but the existing ones use low-overhead data structures for their undo/in-heap MVCC snapshot systems, such that they probably add less than O(log log N) overhead where N is the size of the changes themselves. I did mean to suggest that it's far harder to limit memory allocation, and thus paging/cache thrashing, when you have an unbounded number of transactions each tracking bounded-size changes†, than when you "only" have a bounded number of transactions each tracking bounded-size† changes. (Seems obvious when phrased that way, right?) † "bounded-size changes" because most RDBMSes have per-connection memory limits, and can-and-will just crash out a connection [with the semantics of a rollback] if it uses too much working memory. With limited concurrency, you can do individual queries that stall your own connection; and you can do "maintenance" statements like index rebuilds that lock various resources. But it's very hard to put the DBMS in a state where a DBA can't connect to it in order to issue administrative statements to e.g. kill user connections. Unlimited concurrency (or bounded concurrency if the bound x the per-connection workmem limit > the DB's RAM size) would allow a badly-configured client to "DDoS the RDBMS with established connections", such that the DBMS could become entirely unresponsive to the network and would need to be non-gracefully terminated. > The definition of committed in most of such systems include that the necessary data has been written to disk before signalling success for the commit. True, by definition, yeah. And probably always going to be true in single-instance clusters. In distributed clusters, testing by Jepsen and others has shown that, in most SQL DBMSes with configurable SQL "transaction isolation" levels, using anything other than the highest level configurable can allow for write-loss of locally [but not distributed-ly] "committed" writes, under certain conditions.

derefr 8y ago

Ah, yeah, I didn't mean to suggest that O(N) snapshots translates to O(N) data usage in practice. It could if you wrote a really stupid RDBMS, but the existing ones use low-overhead data structures for their undo/in-heap MVCC snapshot systems, such that they probably add less than O(log log N) overhead where N is the size of the changes themselves. I did mean to suggest that it's far harder to limit memory allocation, and thus paging/cache thrashing, when you have an unbounded number of transactions each tracking bounded-size changes†, than when you "only" have a bounded number of transactions each tracking bounded-size† changes. (Seems obvious when phrased that way, right?) † "bounded-size changes" because most RDBMSes have per-connection memory limits, and can-and-will just crash out a connection [with the semantics of a rollback] if it uses too much working memory. With limited concurrency, you can do individual queries that stall your own connection; and you can do "maintenance" statements like index rebuilds that lock various resources. But it's very hard to put the DBMS in a state where a DBA can't connect to it in order to issue administrative statements to e.g. kill user connections. Unlimited concurrency (or bounded concurrency if the bound x the per-connection workmem limit > the DB's RAM size) would allow a badly-configured client to "DDoS the RDBMS with established connections", such that the DBMS could become entirely unresponsive to the network and would need to be non-gracefully terminated. > The definition of committed in most of such systems include that the necessary data has been written to disk before signalling success for the commit. True, by definition, yeah. And probably always going to be true in single-instance clusters. In distributed clusters, testing by Jepsen and others has shown that, in most SQL DBMSes with configurable SQL "transaction isolation" levels, using anything other than the highest level configurable can allow for write-loss of locally [but not distributed-ly] "committed" writes, under certain conditions.