4 ms·

You don't seem to know this, but no traditional RDBMSs actually provide CAP consistency, for that they would have to use at least two-phase commit or something,

You don't seem to know this, but no traditional RDBMSs actually provide CAP consistency, for that they would have to use at least two-phase commit or something, but they don't. So, they all are noCAP databases. Electronic health or financial records are way safer in a proper eventually consistent database, like orders of magnitude safer, but everyone just takes the risk with some insurance at best to cover the losses. EDIT: If you downvote, please explain why. You can't disagree with the truth.

toomuchtodo 9y ago
In certain environments, it is better to fail then to have data that isn't immediately consistent. Finance and healthcare are two such systems. Availability is not always paramount. https://en.wikipedia.org/wiki/Database_transaction https://en.wikipedia.org/wiki/Database_transaction https://en.wikipedia.org/wiki/Atomicity_(database_systems) https://en.wikipedia.org/wiki/Atomicity_(database_systems)
- zzzcpan 9y ago
  They only guarantee consistency as long as you don't use them over a network, i.e. communications with the database are always reliable. But once you do use them over a network - CAP theorem comes in and forces you to either use something like two-phase commit or no promises of consistency. Which is the opposite of what his post implied, like there is CAP consistency with those databases. But there never was! Although I kind of got used to RDBMS crowd not understanding consistency, it's just another technology cult.
  - richardwhiuk 9y ago
    Proper RDMS databases have two phase commit with transactions.....
    - zzzcpan 9y ago
      Which ones? Traditional mainstream RDBMSs, like Mysql and Postgres don't use two-phase commit protocol. Obviously new distributed ones do it properly, but we are not talking about them.
      - orf 9y ago
        https://www.postgresql.org/docs/9.3/static/sql-prepare-transaction.html https://www.postgresql.org/docs/9.3/static/sql-prepare-trans...
  - deleted 9y ago
    [deleted]
  - cortesoft 9y ago
    Wait, CAP theorem just says that you have to sacrifice availability of you want Consistency and Partition tolerance.
artimaeis 9y ago
> "no traditional RDBMS actually provide CAP consistency, for that they would have to use at least two-phase commit https://docs.microsoft.com/en-us/sql/t-sql/language-elements/commit-transaction-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/language-elements... > If the transaction committed was a Transact-SQL distributed transaction, COMMIT TRANSACTION triggers MS DTC to use a two-phase commit protocol to commit all of the servers involved in the transaction. If a local transaction spans two or more databases on the same instance of the Database Engine, the instance uses an internal two-phase commit to commit all of the databases involved in the transaction. I'm only versed in SQL-Server but I'm pretty sure other RDBMS vendors provide similar functionality.
- zzzcpan 9y ago
  Thanks for pointing out. Oracle has distributed transactions too.
da_chicken 9y ago
> You don't seem to know this, but no traditional RDBMSs actually provide CAP consistency, for that they would have to use at least two-phase commit or something, but they don't. At the single server level (which is how I think others here are interpreting your comment)? No, they all do, with the exception of some configurations of MySQL (especially older editions, which is why it's often maligned by DBAs). That's what transaction logs do. They're literally a write ahead log (WAL). You commit a transaction, and the DB first obtains an exclusive lock on the affected rows (or page, or table). Any other transaction attempting to read or update those rows will be blocked (with exceptions). It then writes the change to the transaction log and flushes the change to disk. Then it writes the changes to the database file and flushes the change to disk. Then it returns the results of the query to the user. Many RDBMSs let you control how tightly the locks are and the degree that the data are isolated during a transaction. At the distributed network server level? Then I guess I kind of agree with you, sure. RDBMSs let you "get around" the problems of distributed scaling by not letting you do it easily. SQL servers often only have master/slave or publisher/subscriber setups or otherwise partition the data between instances with sharding. There's no need for raft or paxos type algorithms because they don't attempt to implement a true multi-master environment. There's either a fixed overall master, or each server is the deterministic master of it's own little world, so you avoid consistency problems with distributed data. However, in doing so you sacrifice availability, since if a shard goes down so does all that data, or if the master is busy then you can't always submit queries to the slaves. Replication is used for redundancy, not scaling or load balancing. The solution RDBMSs had was sharding + master/slave replication for redundancy, which can get messy fast and has issues like hot spots or limited queries or variant performance. It's just a lot harder to do than it feels like it should be, and with storage as cheap as it is it feels like a waste of effort. That said, some RDBMSs do allow you to use multimaster, bidirectional, or peer-to-peer replication, but most of those configurations basically warn you that you're sacrificing consistency by doing it and all of them that I've seen are a huge pain in the ass that makes shard + replicate look like child's play. They also have schema requirements that make life difficult, and they're somewhat notorious for being difficult both to administer and develop for. You have to design the whole thing from the ground up to work with this type of replication, it still feels like a house of cards, and it's this exact level of pain in the ass that encouraged the partitioning and availability focused NoSQL data stores. However... most applications don't need that kind of scaling. They don't need a database in every time zone for single millisecond response times globally. They don't have the users to demand it, or don't have the quantity of data to require it, or have other requirements that make a traditional RDBMS desirable where you can't accept a system that allows for out-of-date data (which is when PACELC theorem kicks in because NoSQL typically doesn't have locking like an RDBMS does to mitigate this particular problem).
userpass 9y ago
The C in the CAP theorem refers to strong consistency usually in the form of ACID transactions. MongoDB gives up the C because not all applications need ACID transactions and only a weaker form of consistency called eventual consistency. In exchange it can do automatic failover without excessively slow distributed transactions, data corruption or data loss (at least in theory) and sharding. On the other hand almost every RDBMS has ACID transactions.

toomuchtodo 9y ago

In certain environments, it is better to fail then to have data that isn't immediately consistent. Finance and healthcare are two such systems. Availability is not always paramount. https://en.wikipedia.org/wiki/Database_transaction https://en.wikipedia.org/wiki/Database_transaction https://en.wikipedia.org/wiki/Atomicity_(database_systems) https://en.wikipedia.org/wiki/Atomicity_(database_systems)

zzzcpan 9y ago
They only guarantee consistency as long as you don't use them over a network, i.e. communications with the database are always reliable. But once you do use them over a network - CAP theorem comes in and forces you to either use something like two-phase commit or no promises of consistency. Which is the opposite of what his post implied, like there is CAP consistency with those databases. But there never was! Although I kind of got used to RDBMS crowd not understanding consistency, it's just another technology cult.
- richardwhiuk 9y ago
  Proper RDMS databases have two phase commit with transactions.....
  - zzzcpan 9y ago
    Which ones? Traditional mainstream RDBMSs, like Mysql and Postgres don't use two-phase commit protocol. Obviously new distributed ones do it properly, but we are not talking about them.
    - orf 9y ago
      https://www.postgresql.org/docs/9.3/static/sql-prepare-transaction.html https://www.postgresql.org/docs/9.3/static/sql-prepare-trans...
- deleted 9y ago
  [deleted]
- cortesoft 9y ago
  Wait, CAP theorem just says that you have to sacrifice availability of you want Consistency and Partition tolerance.

artimaeis 9y ago

> "no traditional RDBMS actually provide CAP consistency, for that they would have to use at least two-phase commit https://docs.microsoft.com/en-us/sql/t-sql/language-elements/commit-transaction-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/language-elements... > If the transaction committed was a Transact-SQL distributed transaction, COMMIT TRANSACTION triggers MS DTC to use a two-phase commit protocol to commit all of the servers involved in the transaction. If a local transaction spans two or more databases on the same instance of the Database Engine, the instance uses an internal two-phase commit to commit all of the databases involved in the transaction. I'm only versed in SQL-Server but I'm pretty sure other RDBMS vendors provide similar functionality.

zzzcpan 9y ago
Thanks for pointing out. Oracle has distributed transactions too.

da_chicken 9y ago

> You don't seem to know this, but no traditional RDBMSs actually provide CAP consistency, for that they would have to use at least two-phase commit or something, but they don't. At the single server level (which is how I think others here are interpreting your comment)? No, they all do, with the exception of some configurations of MySQL (especially older editions, which is why it's often maligned by DBAs). That's what transaction logs do. They're literally a write ahead log (WAL). You commit a transaction, and the DB first obtains an exclusive lock on the affected rows (or page, or table). Any other transaction attempting to read or update those rows will be blocked (with exceptions). It then writes the change to the transaction log and flushes the change to disk. Then it writes the changes to the database file and flushes the change to disk. Then it returns the results of the query to the user. Many RDBMSs let you control how tightly the locks are and the degree that the data are isolated during a transaction. At the distributed network server level? Then I guess I kind of agree with you, sure. RDBMSs let you "get around" the problems of distributed scaling by not letting you do it easily. SQL servers often only have master/slave or publisher/subscriber setups or otherwise partition the data between instances with sharding. There's no need for raft or paxos type algorithms because they don't attempt to implement a true multi-master environment. There's either a fixed overall master, or each server is the deterministic master of it's own little world, so you avoid consistency problems with distributed data. However, in doing so you sacrifice availability, since if a shard goes down so does all that data, or if the master is busy then you can't always submit queries to the slaves. Replication is used for redundancy, not scaling or load balancing. The solution RDBMSs had was sharding + master/slave replication for redundancy, which can get messy fast and has issues like hot spots or limited queries or variant performance. It's just a lot harder to do than it feels like it should be, and with storage as cheap as it is it feels like a waste of effort. That said, some RDBMSs do allow you to use multimaster, bidirectional, or peer-to-peer replication, but most of those configurations basically warn you that you're sacrificing consistency by doing it and all of them that I've seen are a huge pain in the ass that makes shard + replicate look like child's play. They also have schema requirements that make life difficult, and they're somewhat notorious for being difficult both to administer and develop for. You have to design the whole thing from the ground up to work with this type of replication, it still feels like a house of cards, and it's this exact level of pain in the ass that encouraged the partitioning and availability focused NoSQL data stores. However... most applications don't need that kind of scaling. They don't need a database in every time zone for single millisecond response times globally. They don't have the users to demand it, or don't have the quantity of data to require it, or have other requirements that make a traditional RDBMS desirable where you can't accept a system that allows for out-of-date data (which is when PACELC theorem kicks in because NoSQL typically doesn't have locking like an RDBMS does to mitigate this particular problem).

userpass 9y ago

The C in the CAP theorem refers to strong consistency usually in the form of ACID transactions. MongoDB gives up the C because not all applications need ACID transactions and only a weaker form of consistency called eventual consistency. In exchange it can do automatic failover without excessively slow distributed transactions, data corruption or data loss (at least in theory) and sharding. On the other hand almost every RDBMS has ACID transactions.