4 ms·

The question really isn't SQL but ACID transactions. If you can do ACID transaction, SQL drastically reduces the amount of data transmitted. There is no intri

The question really isn't SQL but ACID transactions. If you can do ACID transaction, SQL drastically reduces the amount of data transmitted. There is no intrinsic reason that ACID transactions, with or without SQL, can't scale, just that up until now, it hasn't. And there's a reason that until now, it hasn't. From the beginning of time, academic computer "scientists" have confused the terms serializability and consistency. In short, serializability is a sufficient condition for consistency, but it isn't a necessary condition. If you design a system that enforces consistency without requiring serializability, it scales. Period. Legacy RDMSes don't work that way, but that's their problem.

lincolnq 15y ago
Huh. Your insight is an interesting one, but I am having trouble understanding how it is actually useful. If I may reduce to a concrete example: Posit shared variables x and y, both zero, and simultaneously do (on threads T1 and T2) T1: atomic { y = 1; return x; } T2: atomic { x = 1; return y; } where 'atomic' indicates a transactional operation. Traditional ACID rules would state that either T1 takes effect before T2 (and so (T1, T2) return (0,1)) or T2 before T1 (so they return (1,0)). Other interleavings are not permitted -- returning (0,0) or (1,1) would violate consistency. What it seems to me that you are saying is that both (0,0) and (1,1) might be OK, depending on your application domain -- maybe the app can specify a consistency rule that says that (1,1) isn't OK. Then you can design databases that follow application-directed consistency rules. At first blush this seems impractical, so I am hoping you can elaborate on the point you were trying to make.
- jhugg 15y ago
  My understanding of the MVCC at work is that writes must effectively be serialized, but that pure-read transactions never have to block, as they can view a consistent snapshot of the state at any time. This would imply read-scaling, but I don't see how this makes writes go any faster without reducing consistency. What am I missing?
- jimstarkey 15y ago
  The C in ACID is consistent, not serializable (i.e. it's not ASID). Consistency means that declared consistency constraints are enforce -- no dirty writes, no violations of unique indexes, referential integrity, and anything else that the bells and whistles supports. Let me give a simpler example: Database with one table of one field, a number. One transaction: count the number of records and store that number. A serializable system will force one zero, one one, one two, etc. But a consistent system can have two zeros and no ones. Why? Because that's what each concurrent transaction saw? Nothing wrong with that. But if application semantics dictate that each value must be distinct, then put a unique index on the number and the system will enforce uniqueness. Automatically enforcing "auto-magic" constraints that nobody cares about is why serializability destroys scalability of distributed system. In NuoDB all messaging is asynchronous and batched, making it very fast and efficient. For more than you want to know, see http://www.gbcacm.org/sites/www.gbcacm.org/files/slides/SpecialRelativity[1]_0.pdf http://www.gbcacm.org/sites/www.gbcacm.org/files/slides/Spec... Someplace there's even an audio recording which I recommend if you're into self-abuse.
  - jhugg 15y ago
    So running with your example, assume I have a transaction that adds 5 to a column value and then reads the value back. If I start with a value of 0, then run my transaction twice, can both transactions return 5? Or is it guaranteed that one will return 10?
    - deleted 15y ago
      [deleted]
    - andrewcooke 15y ago
      [sorry. replied earlier incorrectly]. one of the two transactions would abort in this case. snapshot isolation needs to check that any data mutated in the transaction were not also mutated externally. in the example you are replying to, there is no mutation, and so no problem, but in your example there is. see http://en.wikipedia.org/wiki/Snapshot_isolation http://en.wikipedia.org/wiki/Snapshot_isolation note that it's only mutated values that are checked for conflicts, and only against other mutations (this is why it is efficient - the number of checks required is small). so you can get weird behaviour when multiple values are read while different transactions change each - there's a good example in the link above. this is called "write skew". the whole approach is, in a sense, exploiting poor phrasing of the ansi sql-92 standard, which doesn't actually require serialisation even though that is the most natural way to interpret it (as far as i understand things). so you can think of MVCC as "exploiting a loophole" that leads to a more efficient system, but one that is less intuitive. on the other hand, this is not new - it's already the standard behaviour for postgres, oracle, sql server, etc.

lincolnq 15y ago

Huh. Your insight is an interesting one, but I am having trouble understanding how it is actually useful. If I may reduce to a concrete example: Posit shared variables x and y, both zero, and simultaneously do (on threads T1 and T2) T1: atomic { y = 1; return x; } T2: atomic { x = 1; return y; } where 'atomic' indicates a transactional operation. Traditional ACID rules would state that either T1 takes effect before T2 (and so (T1, T2) return (0,1)) or T2 before T1 (so they return (1,0)). Other interleavings are not permitted -- returning (0,0) or (1,1) would violate consistency. What it seems to me that you are saying is that both (0,0) and (1,1) might be OK, depending on your application domain -- maybe the app can specify a consistency rule that says that (1,1) isn't OK. Then you can design databases that follow application-directed consistency rules. At first blush this seems impractical, so I am hoping you can elaborate on the point you were trying to make.

jhugg 15y ago
My understanding of the MVCC at work is that writes must effectively be serialized, but that pure-read transactions never have to block, as they can view a consistent snapshot of the state at any time. This would imply read-scaling, but I don't see how this makes writes go any faster without reducing consistency. What am I missing?
jimstarkey 15y ago
The C in ACID is consistent, not serializable (i.e. it's not ASID). Consistency means that declared consistency constraints are enforce -- no dirty writes, no violations of unique indexes, referential integrity, and anything else that the bells and whistles supports. Let me give a simpler example: Database with one table of one field, a number. One transaction: count the number of records and store that number. A serializable system will force one zero, one one, one two, etc. But a consistent system can have two zeros and no ones. Why? Because that's what each concurrent transaction saw? Nothing wrong with that. But if application semantics dictate that each value must be distinct, then put a unique index on the number and the system will enforce uniqueness. Automatically enforcing "auto-magic" constraints that nobody cares about is why serializability destroys scalability of distributed system. In NuoDB all messaging is asynchronous and batched, making it very fast and efficient. For more than you want to know, see http://www.gbcacm.org/sites/www.gbcacm.org/files/slides/SpecialRelativity[1]_0.pdf http://www.gbcacm.org/sites/www.gbcacm.org/files/slides/Spec... Someplace there's even an audio recording which I recommend if you're into self-abuse.
- jhugg 15y ago
  So running with your example, assume I have a transaction that adds 5 to a column value and then reads the value back. If I start with a value of 0, then run my transaction twice, can both transactions return 5? Or is it guaranteed that one will return 10?
  - deleted 15y ago
    [deleted]
  - andrewcooke 15y ago
    [sorry. replied earlier incorrectly]. one of the two transactions would abort in this case. snapshot isolation needs to check that any data mutated in the transaction were not also mutated externally. in the example you are replying to, there is no mutation, and so no problem, but in your example there is. see http://en.wikipedia.org/wiki/Snapshot_isolation http://en.wikipedia.org/wiki/Snapshot_isolation note that it's only mutated values that are checked for conflicts, and only against other mutations (this is why it is efficient - the number of checks required is small). so you can get weird behaviour when multiple values are read while different transactions change each - there's a good example in the link above. this is called "write skew". the whole approach is, in a sense, exploiting poor phrasing of the ansi sql-92 standard, which doesn't actually require serialisation even though that is the most natural way to interpret it (as far as i understand things). so you can think of MVCC as "exploiting a loophole" that leads to a more efficient system, but one that is less intuitive. on the other hand, this is not new - it's already the standard behaviour for postgres, oracle, sql server, etc.