Clarification on “Call Me Maybe: MariaDB Galera Cluster” • Hacker News Board

Re: Clarification on “Call Me Maybe: MariaDB Galera Cluster”

#21

Post by hobofan » Fri, Sep 18, 2015, 1:04 PM UTC

sagichmal wrote: post #14

> Following that conclusion is using Galera cluster may result in “corrupted” data. I do not quite like the usage of the word “corrupted” here. For me, the more correct word be to use is “inconsistent”. But Aphyr never once uses the term "corrupted data", or the word "corrupted". If you're going to quote an article, it's important to be precise. This response feels panicked, or at least rushed. And it really misses t…

Chronos, not Mesos. I still wonder why Chronos was even tested. To break Chronos you don't need a network partition, you just have to let it run for a day.

Re: Clarification on “Call Me Maybe: MariaDB Galera Cluster”

#22

Post by AlisdairO » Fri, Sep 18, 2015, 1:07 PM UTC

Edit: ignore this, it's addressing a completely different situation, and I clearly didn't read the article well enough. The code in the article writes all of the locations it reads, so true SI ought to keep you safe. My apologies!

Yet another edit: huh, it seems that InnoDB in RR doesn't rollback when you write to a row that's been written since you started the transaction. TIL.

--------

It's worth noting here that (AFAIR) Oracle's 'SERIALIZABLE' (actually SI) level suffers from this exact write skew vulnerability, so MySQL/MariaDB is not alone in this issue. As pointed out, SELECT FOR UPDATE is a commonly used remedy.

What it comes down to is that while re-reading data in a given transaction under SI will give you the same result, it doesn't guarantee that the data in the DB itself has stayed constant. If you want to guarantee that the data won't change, you need to lock it.

IIRC this also applies to PostgreSQL's REPEATABLE READ level.

Re: Clarification on “Call Me Maybe: MariaDB Galera Cluster”

#23

Post by dack » Fri, Sep 18, 2015, 1:09 PM UTC

acveilleux wrote: post #17

dack wrote: post #16

Earlier quoted context omitted.

I think in general when writing applications, you should assume that any data you keep in memory between SQL queries could become stale and change before the next query/update. Anytime you have to update multiple individual records and rely on calculating state from both of them at once, alarm bells should start going off. Yes, you can start dropping into special transactions but there's also a potential opportunity…

To some extent, isn't that the whole point of specifying the transaction isolation level you need? So you can make these assumptions?

Sure, but there are tradeoffs in performance then. I'm not saying transactions are bad (although maybe my strong wording in the parent implies that), just that, especially if you're using an ORM, you shouldn't make those assumptions by default.

Re: Clarification on “Call Me Maybe: MariaDB Galera Cluster”

#24

Post by moomin » Fri, Sep 18, 2015, 1:09 PM UTC

I think he's trying to define "corrupted" data as completely inaccessible data (because of file corruption, presumably). However, if I zip up a picture of PaulGraham.jpg and when I unzip it I get one of Iron Man, I think I'd be within my rights to call the data corrupt.

Re: Clarification on “Call Me Maybe: MariaDB Galera Cluster”

#25

Post by AlisdairO » Fri, Sep 18, 2015, 1:14 PM UTC

bboreham wrote: post #20

dack wrote: post #16

Earlier quoted context omitted.

I think in general when writing applications, you should assume that any data you keep in memory between SQL queries could become stale and change before the next query/update. Anytime you have to update multiple individual records and rely on calculating state from both of them at once, alarm bells should start going off. Yes, you can start dropping into special transactions but there's also a potential opportunity…

> Yes, you can start dropping into special transactions Transactions aren't "special" in SQL. You expect that reads and writes within a transaction are kept consistent, unless you have deliberately chosen a weaker serialization level.

It's pretty common for RDBMSs to default to read committed, which is arguably a mistake on the implementer's part, but does allow for some pretty serious inconsistency if you don't know what you're doing.

Re: Clarification on “Call Me Maybe: MariaDB Galera Cluster”

#26

Post by dack » Fri, Sep 18, 2015, 1:14 PM UTC

bboreham wrote: post #20

dack wrote: post #16

Earlier quoted context omitted.

I think in general when writing applications, you should assume that any data you keep in memory between SQL queries could become stale and change before the next query/update. Anytime you have to update multiple individual records and rely on calculating state from both of them at once, alarm bells should start going off. Yes, you can start dropping into special transactions but there's also a potential opportunity…

> Yes, you can start dropping into special transactions Transactions aren't "special" in SQL. You expect that reads and writes within a transaction are kept consistent, unless you have deliberately chosen a weaker serialization level.

Well, SERIALIZABLE isn't the default in Oracle, Postgres, or MySQL.. it's READ_COMMITTED, READ_COMMITTED, and REPEATABLE_READ respectively (unless the documentation i just looked up is out-of-date)

I'm just saying you still can't rely on it by default, and have to start reading the details of the isolation levels. If you're doing that all the time, it might be a reason to rethink the design (however, there are of course some exceptions)

Re: Clarification on “Call Me Maybe: MariaDB Galera Cluster”

#27

Post by dack » Fri, Sep 18, 2015, 1:16 PM UTC

andy_ppp wrote: post #15

dack wrote: post #13

Earlier quoted context omitted.

I would agree from a database perspective, it's an inconsistency. From the application user's perspective though, the information can get corrupted in the sense that the original intent of the transactions is no longer clear, and can't be recovered. I think it's not always obvious from which perspective Aphyr is writing (database or user), so the meaning could be somewhat ambiguous. While an unexpected inconsistency…

Would it not be preferable for things to break in a piece of banking software rather than be incorrect but no one notices there is a problem. I hope they are trying to fix these issues rather than be sensitive.

All databases that I know of have settings that allow you to control the consistency of your transactions in the documentation. I don't think it's necessarily the problem that this particular situation is inconsistent - I just think the documentation is the problem, since they claim one thing and then do another. But yeah, I totally agree they should fix either the implementation or documentation - the blog post is far from sufficient in educating their customers about this situation!

Re: Clarification on “Call Me Maybe: MariaDB Galera Cluster”

#28

Post by inversionOf » Fri, Sep 18, 2015, 1:17 PM UTC

AlisdairO wrote: post #22

Edit: ignore this, it's addressing a completely different situation, and I clearly didn't read the article well enough. The code in the article writes all of the locations it reads, so true SI ought to keep you safe. My apologies! Yet another edit: huh, it seems that InnoDB in RR doesn't rollback when you write to a row that's been written since you started the transaction. TIL. -------- It's worth noting here that (…

it doesn't guarantee that the data in the DB itself has stayed constant

By definition, snapshot isolation is supposed to guarantee that all reads are the consistent, committed data as of the begin transaction, and the commit will fail and rollback if any data altered within a snapshot isolation transaction was already changed. The response by Percona, unless I am reading it wrong, actually agrees that they are not truly implementing snapshot isolation. They then argue that the tester should have tested something totally different, but that doesn't address the snapshot isolation not actually being snapshot isolation.

As to locks, snapshot isolation/MVCC are there to avoid a sea of locks. The solution to a broken snapshot isolation level isn't simply to manually and programmatically demand locks. While that may work, it completely undermines the whole reason for SI.

Re: Clarification on “Call Me Maybe: MariaDB Galera Cluster”

#29

Post by MichaelGG » Fri, Sep 18, 2015, 1:20 PM UTC

Am I missing something or does this not address the main issue the original article raised: The documentation is simply incorrect. It claims to support SNAPSHOT ISOLATION but does not. The company knows this and even this article says the behaviour "is totally expected".

Seems like the first response should be to fix the docs and not claim capabilities beyond what's implemented.

(Also it was pretty clear from the original article that corrupted data meant the balances were incorrect, not that the file was corrupted like a bad checksum.)

Re: Clarification on “Call Me Maybe: MariaDB Galera Cluster”

#30

Post by numbsafari » Fri, Sep 18, 2015, 1:22 PM UTC

creshal wrote: post #2

> If you use this in a real life, the more obvious way to write these transactions is: Is it? Do ORMs really do that, or is it one of those "SQL was designed to be used this way, but nobody using SQL read the design documents" cases?

The point of SNAPSHOT ISOLATION is to, essentially, implement optimistic concurrency in a transparent fashion.

The fact the the OP recommends using lock manipulation, shows that he's already ceded the point about SNAPSHOT ISOLATION. That is, the whole point of supporting SI is to avoid lock twiddling and the associated complexity and overhead.

It's pretty clear that the OP is missing aphyr's point entirely.