Good point, but imho anyone that relies on transactionnal properties to ensure validity of its operation should really not be using ORMs ( at least not for the sensitive operations).
You shouldn't need to read an ORM documentation to understand what kind of locking is happening at a given time. It should all be there right in your code.
This approach only works if your logic is codable in SQL format in the way this trivial balance change can be. If the change is happening in app business logic, this is always going to be a problem.
The fact the balance+=25 type approach works is kind of a hack, and I don't think Aphyr is wrong to call it "corrupted" (in this specific example, in one transaction, you could say that it is inconsistent - but you only need a few inconsistent operations in the same place and suddenly your data is complete rubbish).
This feels a bit like handwaving the problem away, to me. Yes, InnoDB has the problem, but with that backend you can set SERIALIZABLE isolation. You can't do that in Galera. So if you don't have the option of rewriting the SQL (either because it's not your SQL, or there is no SQL alternative available) then you're screwed.
Sure, my remark wasn't meant to dismiss aphyr blog. I completely agree that there is a big problem, and calling it corrupted seems valid to me as well.
It was more of a sidenote about orms and transactions in general.
They don't do that automatically. In sqlalchemy for example you still have to call an extra function explicitly. (http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09...) I know of only one developer who knew about "for update", but then again they just read the mysql book with chapter dedicated to locking behaviour.
ORMs might issue lock hints but in general they simply start the transaction with the desired isolation level and let the RDBMS take care of the locking of affected rows/tables by statements executed.
Lock hints are sometimes used by naive ORMs who think you can implement optimistic concurrency using row locking. Their users will find out eventually that this whole row-locking doesn't help anyone as it simply reverses the tables on 'first write wins' vs. 'last write wins': there's still someone who loses, so the locking doesn't solve the problem of no-one losing their changes while it does give slower performance (and on SQL Server even the risk of deadlocks).
What developers often overlook is that there are two types of transactions: business transactions and DB transactions. A business transaction can span multiple DB transactions and a DB transaction can span multiple SQL statements. They're not the same, seeing a business transaction as equal to a DB transaction makes things get messed up and often gives food to the need of explicit lock hints for some queries to e.g. get the false sense of being able to implement optimistic concurrency. If you consider a business transaction, locking doesn't make any sense: it can take some time to complete it, so you have to deal with the side effects of stale data: it immediately becomes apparent that e.g. optimistic concurrency has no place here, one needs other ways to avoid people overwriting work of each other.
TFA re-orders statements to get the desired locks in place, and it IS possible to do so, e.g. some ORMs offer when to start a transaction or implement Unit of works which allow you to specify which batches to execute first (e.g. first deletes, then inserts). However the developer using the ORM isn't working at that abstraction level, as the ORM offers an abstraction level above all that; so re-ordering statements to get the desired read locks or avoid dirty reads within a transaction is abusing knowledge of how the abstraction offered by the ORM internally works. IMHO one shouldn't do that: the code utilizing the ORM is db agnostic: expecting certain DB behavior in DB agnostic code is going to give unpleasant side effects when the DB agnostic code will be used to e.g. utilize another RDBMS instead of the current one.
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 for a better design.
Unfortunately, all that feels like a huge accidental complexity.
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.
The only assumption that was made is that Galera cluster's SNAPSHOT ISOLATION actually implements SNAPSHOT ISOLATION. How is this not an assumption that should be made by default? If the database user has specified a specific isolation level, then they have already selected a specific trade-off in performance vs consistency. Giving them a weaker isolation level in the name of performance is like giving someone a car when they asked for a train ticket.
> 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.
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)
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.
I do not quite like the usage of the word “corrupted” here. For me, the more correct word be to use is “inconsistent”.
Aren't we talking about situations in which a database tracking account balances creates money out of thin air, or vaporizes it unexpectedly?
I feel like Aphyr is always at pains to talk about the real-world implications of these findings --- not just how bad they are in sensitive applications, but also the kinds of places you can get away with these "inconsistencies".
A database maker would say it isn't the database that created the money out of thin air, but the faulty application code that didn't select the right isolation level.
Of course, whether it's wise for a database to default to anything except Serializable isolation is another matter.
The database maker's docs have said that their database provides Snapshot Isolation though, which suggests the user should be able to do what Aphyr did without a problem. That's pretty clearly not true, which means it isn't faulty application code. It is the database maker misleading the user.
Yeah, I found it strange that he says "it's not X, it's Y" but doesn't define either one himself.
My own definitions would agree with him: corrupted - you get exception while trying to read, or meaningless data; inconsistent - you get the right types, but wrong values. But I can only guess if his definitions agree with mine by accident, or are the same.
I think the Percona writer is focusing on "corruption" in terms of how I think most database folks imagine "corruption" - where a series of commands will erase a block of data, or make a block of data unrecoverable.
I agree that "inconsistent" is probably more mechanically accurate, but since in this case the side effect of the inconsistency is that you can't trust the contents of a block of data, it seems like a difference without a distinction.
Yup, inconsistency is a kind of data corruption, provided you expect it to be in a consistent state.
The emotional value of the latter is much stronger, though, and it sounds worse to the uninitiated, which is probably why Percona folks are trying to spin it that way.
For me, inconsistent tends to imply a silent error, and corruption tends to imply one that is immediately noticed.
I know that isn't accurate, but the tendency for it to be true, actually makes the word inconsistent upset me more than corruption. I know how to fix bad data, trying to track down a soft error that is hard to reproduce is the subject of my nightmares.
oh, but it is an important difference, i believe. any experienced developer will keep an audit log of transactions somewhere. a corrupted database can be completely lost. an inconsistent one can be later corrected by using the audit trail.
corruption is a really scary word to hear about a database. inconsistency is also bad, but corruption is worse, IMO.
P.S. but, i must add - a DB that says it's isolated, but really isn't, and then says "but these are strange transactions", or the famous "you're doing it wrong!", that's really not cool.
> any experienced developer will keep an audit log of transactions somewhere. a corrupted database can be completely lost. an inconsistent one can be later corrected by using the audit trail.
If you have a full audit trail you can rebuild the database from scratch so there is no difference between an inconsistent and a corrupted database.
In fact, since the audit trail ought store responses from the database, replaying it may not lead to a corruption but it may lead to an inconsistent database again, or it may be rejected by the system.
good points, but it all depends on the actual situation.
for starters, i was not thinking about blindly replaying it in the same system. of course that makes no sense. rather, after you find the bug, you use an alternate source of the same data to reconstruct the correct state AFTER you have fixed the bug. e.g. i had a case, where one table held state of a stock, while other held documents which were needed as proof of changes of the state. you can typically never just change things, this is not only a programmer practice, this is a standard accounting practice as well. had in my example the database gotten corrupt, that would be it [1]. but it was not, and i was able to, after finding the bug, locate exactly when and where it had happened, and correct it's effects as well.
of course, this type of save will often not be possible, but i just wanted to point out that it is sometimes possible, and makes a difference. ultimately, it's just not the same. and IMO, the guy writing the call me maybe blog should not bitch about it. it's a minor, honest mistake, that can in no way reflect badly on his reliably great work.
[1] while writing this, another thought came to mind - had it gotten corrupt, we would have used a backup :D not the end of the world also. but i think the distinction somewhat matters. a corrupt database is a headache for everyone, and freaks people out. business people too.
The assumption that you can "fix" the database and proceed as if nothing had happened is naive, IMNSHO.
Firstly, there's usually non-trivial amount of time until the inconsistency is discovered, that one modification may be easily followed by many additional modifications that are not simply commutative and "diffuse" the inconsistency to other records. For example if we're talking about bank accounts, you often get interests from that, which is not commutative with simple transfers (multiplication vs. addition). So you can't just compensate for that one error, you'd have to revert all the actions on all the involved accounts.
Secondly, databases are rarely isolated. If you're handling money, chances are you've already submitted the payments to an external payment processor, or perhaps sent data somewhere else (to a different system within the bank, for example). You can't (simply) fix this.
This is why people use constraints or higher transaction isolation levels, and also why the Galera response is amusing and also scary at the same time.
i am not saying it's not a problem, nor that it's always easy to fix. but it is fixable. banks will keep double records, and solutions will be found for corner cases (in the end, even write-offs). but it's simply a very different mode of failure. a corrupt database will typically fail to work, or? a system may fail due to this. this definitely costs money and nerves of all involved.
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 like this is certainly bad enough, I tend to agree with Vadim to reserve the word "corruption" for the situation in which the database actually stores garbage data such that it can't actually read back information properly.
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!
> 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 the point. You can't, or shouldn't, try to explain away these types of findings as irrelevant, or just an issue of semantics. Instead, I'd hope by now that technical folks on the receiving end of a Call Me Maybe analysis would have learned that there's precisely one correct way to respond: acknowledge the faults, clarify relevant documentation, and file (and link to) issues in public issue-trackers that will address the problems.
HashiCorp, CoreOS, and arguably Elastic played it correctly. Aerospike, Mesos, and now Percona, didn't. Shame.
Yes; it's got a bunch of typos and a subtext of barely concealed rage. (e.g. This is a good opportunity for Asphyr to start another FUD “Call Me Maybe: InnoDB”).
> 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.
As opposed to "pedantic"? Here's a quote from the article:
> The probability of data corruption scales with client concurrency, with the duration of transactions, and with the increased probability of intersecting working sets.
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.
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.
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.
EDIT: per grandparent comment edit, this comment is based on a misreading of the article and should be ignored. My apologies!
-------
> 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
Absolutely. What it doesn't guarantee is that data that you read and then use to update a different location has remained constant - which is what write skew is in the first place. What InnoDB and PostgreSQL call 'REPEATABLE READ' and Oracle calls SERIALIZABLE are in fact snapshot isolation.
edit: to clarify, Aphyr's original article describes RR as preventing basic write skew, which mainstream MVCC-based RR implementations simply don't do. Postgres does prevent it when using SSI (the SERIALIZABLE level), and lock-based implementations (RR on DB2 and SQL Server) do also.
You can argue this both ways: The MVCC-based RR implementations do conform to the (fuzzy) letter of the ANSI SQL standard law. They don't conform to Adya's formal definitions, but in fairness many of them existed before those formalisations did :-).
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.
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.)
Yeah, it's pretty clear that OP missed the point, especially in that his recommended solution is to use lock hints. They really should just update their documentation.
The docs are on the galeracluster.com page, which is owned and maintained by another company, so there's no way we could fix those. A staff member from this company (And one of the Galera authors) replied on the original 'Call me maybe' post indicating they would fix the docs, though.
I think the 'corruption vs inconsistency' debate could seem as nitpicking, but anybody who has been working long enough on databases has a very specific concept for each word, and, given transaction processing (distributed or otherwise) is such a complex topic, it does not help to use the wrong terminology.
Perhaps update the article to make a note that you don't control that site's docs? It wasn't immediately apparent that there's yet another party involved.
I'd suggest that the tone of the article be very clear that the problem described is accurate and isn't trying to be downplayed. Suggesting that Aphyr write another "FUD" article comes across as dismissive of the issue. And that'll make people read more into the corruption/inconsistent comment. (Unfair as it may be.)
Corruption is read by the database people as: "Your data is unreadable"; Inconsistency as: "Your data is wrong".
The article implies that inconsistent is better than corrupt, and that mixing up the terms paints a worse picture. The assumption is flawed. Inconsistent is as bad or worse than corrupted. It is a silent failure, and silent failures are worse than visible ones.
i'm kinda surprised by the prevalence of that attitude. in theory, yes, you are right. but that's really a bit of programmer purism. in practice, a DB that stops working can mean a sudden stop in doing business, which is a huge catastrophe. money lost every second.
inconsistency is horrible, and theoretically the same thing, you are right! but it's something you might be able to fix, without the whole system failing, with maybe just a limited number of people knowing what happened and not the whole company and/or all customers. this is kinda fucked up, but i think it counts. (and PS, accountants find these errors. it's their job, and they have experience enough not to trust any proof of correctness, and check everything twice.)
It's really case-specific. But it's not hard to see where an error page "SQL Connection Failed" is better than not recording user's orders, mis-crediting their account, etc. At least with a hard error it's obvious there's a problem and it must be fixed immediately. With inconsistencies, you might e.g. end up with customers expecting travel reservations only to show up and find this to not be the case.
By the time the accountant finds the error, the business could be irreparably harmed.
Maybe those dealing with low value, fluff data in their attempt to be the next social media breakout, but for those of us who work with data containing anything with real business value, inconsistent == corrupt. No other definition is sufficient.
So where are the definitions of those terms, actually? The article merely complains that Tkachenko's personal definitions of those two terms do not match Aphyr's, but that's merely a personal opinion.
I'd argue that "corrupted" is usually used in cases when the data get messed up due to external circumstances (say, disk error corrupting a page of data) or because of a clear bug (say, writing to incorrect place), while "consistency" is usually used for data violating some invariants (foreign keys, check constraints, unique constraints, or global constraints like "total sum of money is X").
But in the end this is utterly irrelevant because the database failed to provide correct data (to the extend implied by the advertised guarantees like isolation level, for example). For the user the end result is pretty much the same.
And actually, corrupted data tend to be detected much sooner, because when you can't read the disk pace you know there's an error and can address it. The inconsistency is often creepy and takes very long until someone spots that.
Besides being a super well-written and interesting series technically, the Call Me Maybe blogs have been very revealing as far as different organizations' response to criticism. Especially considering all of the target applications are open source, the project maintainers should be profusely thankful someone has taken the time for such thorough analysis, presumably much deeper than the maintainers themselves appear to have done at least on consistency behavior, to reveal bugs which should ultimately make it that much stronger.
How a project performs today tells you the zeroth derivative of its location.
Looking at the commit log tells you about the first derivative. How people react
to Call Me Maybe when its about their product gives you a lot of information
about the second derivative.
A common response to these has been: "But you're using it wrong!!!!"
If your product is so complex or so ill-specified that full time testers can't make it work correctly, it's probably difficult for your developers to even understand or fix the problems.
We ignore how much of software development has become "it works for me under all of my default assumptions as the developer of the product—ship it," especially when faced with business deadlines and business management focused around business objectives (and maybe not so much around software quality or correctness).
Something of a quibble here -- MariaDB with Galera Cluster is not Percona's product. Galera Cluster is from Codership and MariaDB is the MariaDB Foundation. Percona does resell Galera Cluster, so they're not an impartial third party, but it's funny to me to see people in this thread asking why Percona isn't fixing the documentation for Codership's product.
There are many approaches to data consistency, including for example Eventual consistency, not only in databases but in any parallel programming. Different CPU architectures for example for years provided different memory consistency models with trade-offs of performance and being usable for application programmers.
It is important however behavior in this case is clearly documented.
Galera would benefit having more clear documentation about what data consistency model exactly it provides.
At the same time Percona XtraDB Cluster, MariaDB Cluster can be used to built reliable applications assuming you're writing to their consistency model correctly.
Below are the tweets by Aphyr for the same thing (with language slightly toned down). I never thought I won't find their mention here on HN. :) Nevertheless, precise, to the point:
Buncha people giving me <filth> for calling data written through an invariant violation "corrupted state", like somehow it's not garbage.
If TCP checksums don't work right we don't call the packet "inconsistent." We call it corrupt. If a disk shuffles your file's bits? Corrupt.
I use the word corrupt to emphasize that not only has the system <messed> up, but you have no way to know your data is now <messed> up.
A lot of defensive talking around technical terms, mixed with a bunch of typos and topped off with unfair attacks.
Not very classy. And the point of Aphyr still stands I think. In default mode it is easy to get corrupt data with Galera Cluster. That InnoDB on a single instance can have the same problem makes it all the more troubling and I'm glad I moved away from MySQL a long time ago.
If I sat down at one of our database consoles and fired off a few queries and saw that hundreds of thousands of dollars had appeared out of nowhere, I'd tell my boss that the database was corrupt.
However, for the purposes of Aphyr's original article, it's a moot point specifically because Aphyr replaced the symbol with the substance[0]. He specifically states the conclusion, and in detail explains why it happened and what the ramifications are. Any quibbling about the word corruption is a distraction, because the underlying fault of the system is still present. The system claims to support snapshot isolation, but does not, and the result of that fact is that data will not be correct for users expecting snapshot isolation.
they are both a catastrophe, i never implied that inconsistency is not one. i'm merely involved in semantic bickering here :) both are bad in their own way, but which is worse will depend on the case. bear in mind that programmers make mistakes all the time. they can also cause such effects. which is why we often do things in a way where incorrect data can be corrected later. particularly in anything related to accounting, where the practice has existed for centuries. i'm really not interested in claiming that one catastrophe is better than the other, but a data corruption in a database can sometimes be more problematic.
edit - sorry, i just noticed i actually did say, in another thread here, that corruption is worse. i would like to take that back now :D
70 comments
[ 3.2 ms ] story [ 127 ms ] threadIs 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?
You shouldn't need to read an ORM documentation to understand what kind of locking is happening at a given time. It should all be there right in your code.
The fact the balance+=25 type approach works is kind of a hack, and I don't think Aphyr is wrong to call it "corrupted" (in this specific example, in one transaction, you could say that it is inconsistent - but you only need a few inconsistent operations in the same place and suddenly your data is complete rubbish).
This feels a bit like handwaving the problem away, to me. Yes, InnoDB has the problem, but with that backend you can set SERIALIZABLE isolation. You can't do that in Galera. So if you don't have the option of rewriting the SQL (either because it's not your SQL, or there is no SQL alternative available) then you're screwed.
It was more of a sidenote about orms and transactions in general.
I haven't seen any, but they do use optimistic locking.
ORMs might issue lock hints but in general they simply start the transaction with the desired isolation level and let the RDBMS take care of the locking of affected rows/tables by statements executed.
Lock hints are sometimes used by naive ORMs who think you can implement optimistic concurrency using row locking. Their users will find out eventually that this whole row-locking doesn't help anyone as it simply reverses the tables on 'first write wins' vs. 'last write wins': there's still someone who loses, so the locking doesn't solve the problem of no-one losing their changes while it does give slower performance (and on SQL Server even the risk of deadlocks).
What developers often overlook is that there are two types of transactions: business transactions and DB transactions. A business transaction can span multiple DB transactions and a DB transaction can span multiple SQL statements. They're not the same, seeing a business transaction as equal to a DB transaction makes things get messed up and often gives food to the need of explicit lock hints for some queries to e.g. get the false sense of being able to implement optimistic concurrency. If you consider a business transaction, locking doesn't make any sense: it can take some time to complete it, so you have to deal with the side effects of stale data: it immediately becomes apparent that e.g. optimistic concurrency has no place here, one needs other ways to avoid people overwriting work of each other.
TFA re-orders statements to get the desired locks in place, and it IS possible to do so, e.g. some ORMs offer when to start a transaction or implement Unit of works which allow you to specify which batches to execute first (e.g. first deletes, then inserts). However the developer using the ORM isn't working at that abstraction level, as the ORM offers an abstraction level above all that; so re-ordering statements to get the desired read locks or avoid dirty reads within a transaction is abusing knowledge of how the abstraction offered by the ORM internally works. IMHO one shouldn't do that: the code utilizing the ORM is db agnostic: expecting certain DB behavior in DB agnostic code is going to give unpleasant side effects when the DB agnostic code will be used to e.g. utilize another RDBMS instead of the current one.
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 for a better design.
Unfortunately, all that feels like a huge accidental complexity.
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.
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)
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.
Aren't we talking about situations in which a database tracking account balances creates money out of thin air, or vaporizes it unexpectedly?
I feel like Aphyr is always at pains to talk about the real-world implications of these findings --- not just how bad they are in sensitive applications, but also the kinds of places you can get away with these "inconsistencies".
Of course, whether it's wise for a database to default to anything except Serializable isolation is another matter.
doesn't get much clearer?
My own definitions would agree with him: corrupted - you get exception while trying to read, or meaningless data; inconsistent - you get the right types, but wrong values. But I can only guess if his definitions agree with mine by accident, or are the same.
I agree that "inconsistent" is probably more mechanically accurate, but since in this case the side effect of the inconsistency is that you can't trust the contents of a block of data, it seems like a difference without a distinction.
The emotional value of the latter is much stronger, though, and it sounds worse to the uninitiated, which is probably why Percona folks are trying to spin it that way.
I know that isn't accurate, but the tendency for it to be true, actually makes the word inconsistent upset me more than corruption. I know how to fix bad data, trying to track down a soft error that is hard to reproduce is the subject of my nightmares.
corruption is a really scary word to hear about a database. inconsistency is also bad, but corruption is worse, IMO.
P.S. but, i must add - a DB that says it's isolated, but really isn't, and then says "but these are strange transactions", or the famous "you're doing it wrong!", that's really not cool.
If you have a full audit trail you can rebuild the database from scratch so there is no difference between an inconsistent and a corrupted database.
In fact, since the audit trail ought store responses from the database, replaying it may not lead to a corruption but it may lead to an inconsistent database again, or it may be rejected by the system.
for starters, i was not thinking about blindly replaying it in the same system. of course that makes no sense. rather, after you find the bug, you use an alternate source of the same data to reconstruct the correct state AFTER you have fixed the bug. e.g. i had a case, where one table held state of a stock, while other held documents which were needed as proof of changes of the state. you can typically never just change things, this is not only a programmer practice, this is a standard accounting practice as well. had in my example the database gotten corrupt, that would be it [1]. but it was not, and i was able to, after finding the bug, locate exactly when and where it had happened, and correct it's effects as well.
of course, this type of save will often not be possible, but i just wanted to point out that it is sometimes possible, and makes a difference. ultimately, it's just not the same. and IMO, the guy writing the call me maybe blog should not bitch about it. it's a minor, honest mistake, that can in no way reflect badly on his reliably great work.
[1] while writing this, another thought came to mind - had it gotten corrupt, we would have used a backup :D not the end of the world also. but i think the distinction somewhat matters. a corrupt database is a headache for everyone, and freaks people out. business people too.
Firstly, there's usually non-trivial amount of time until the inconsistency is discovered, that one modification may be easily followed by many additional modifications that are not simply commutative and "diffuse" the inconsistency to other records. For example if we're talking about bank accounts, you often get interests from that, which is not commutative with simple transfers (multiplication vs. addition). So you can't just compensate for that one error, you'd have to revert all the actions on all the involved accounts.
Secondly, databases are rarely isolated. If you're handling money, chances are you've already submitted the payments to an external payment processor, or perhaps sent data somewhere else (to a different system within the bank, for example). You can't (simply) fix this.
This is why people use constraints or higher transaction isolation levels, and also why the Galera response is amusing and also scary at the same time.
but yes, the response here is bad.
While an unexpected inconsistency like this is certainly bad enough, I tend to agree with Vadim to reserve the word "corruption" for the situation in which the database actually stores garbage data such that it can't actually read back information properly.
Not to be pedantic, but I think you are using the word accurate when you mean precise.[2]
Communicating with just the written word is tough enough; fighting over which near-synonym to use is always fun to watch but rarely productive.
[1] http://tirania.org/blog/archive/2011/Feb-17.html
[2] https://news.ycombinator.com/item?id=10237199
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 the point. You can't, or shouldn't, try to explain away these types of findings as irrelevant, or just an issue of semantics. Instead, I'd hope by now that technical folks on the receiving end of a Call Me Maybe analysis would have learned that there's precisely one correct way to respond: acknowledge the faults, clarify relevant documentation, and file (and link to) issues in public issue-trackers that will address the problems.
HashiCorp, CoreOS, and arguably Elastic played it correctly. Aerospike, Mesos, and now Percona, didn't. Shame.
As opposed to "pedantic"? Here's a quote from the article:
> The probability of data corruption scales with client concurrency, with the duration of transactions, and with the increased probability of intersecting working sets.
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.
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.
-------
> 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
Absolutely. What it doesn't guarantee is that data that you read and then use to update a different location has remained constant - which is what write skew is in the first place. What InnoDB and PostgreSQL call 'REPEATABLE READ' and Oracle calls SERIALIZABLE are in fact snapshot isolation.
edit: to clarify, Aphyr's original article describes RR as preventing basic write skew, which mainstream MVCC-based RR implementations simply don't do. Postgres does prevent it when using SSI (the SERIALIZABLE level), and lock-based implementations (RR on DB2 and SQL Server) do also.
You can argue this both ways: The MVCC-based RR implementations do conform to the (fuzzy) letter of the ANSI SQL standard law. They don't conform to Adya's formal definitions, but in fairness many of them existed before those formalisations did :-).
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.)
The docs are on the galeracluster.com page, which is owned and maintained by another company, so there's no way we could fix those. A staff member from this company (And one of the Galera authors) replied on the original 'Call me maybe' post indicating they would fix the docs, though.
I think the 'corruption vs inconsistency' debate could seem as nitpicking, but anybody who has been working long enough on databases has a very specific concept for each word, and, given transaction processing (distributed or otherwise) is such a complex topic, it does not help to use the wrong terminology.
I'd suggest that the tone of the article be very clear that the problem described is accurate and isn't trying to be downplayed. Suggesting that Aphyr write another "FUD" article comes across as dismissive of the issue. And that'll make people read more into the corruption/inconsistent comment. (Unfair as it may be.)
The article implies that inconsistent is better than corrupt, and that mixing up the terms paints a worse picture. The assumption is flawed. Inconsistent is as bad or worse than corrupted. It is a silent failure, and silent failures are worse than visible ones.
inconsistency is horrible, and theoretically the same thing, you are right! but it's something you might be able to fix, without the whole system failing, with maybe just a limited number of people knowing what happened and not the whole company and/or all customers. this is kinda fucked up, but i think it counts. (and PS, accountants find these errors. it's their job, and they have experience enough not to trust any proof of correctness, and check everything twice.)
By the time the accountant finds the error, the business could be irreparably harmed.
Maybe those dealing with low value, fluff data in their attempt to be the next social media breakout, but for those of us who work with data containing anything with real business value, inconsistent == corrupt. No other definition is sufficient.
I'd argue that "corrupted" is usually used in cases when the data get messed up due to external circumstances (say, disk error corrupting a page of data) or because of a clear bug (say, writing to incorrect place), while "consistency" is usually used for data violating some invariants (foreign keys, check constraints, unique constraints, or global constraints like "total sum of money is X").
But in the end this is utterly irrelevant because the database failed to provide correct data (to the extend implied by the advertised guarantees like isolation level, for example). For the user the end result is pretty much the same.
And actually, corrupted data tend to be detected much sooner, because when you can't read the disk pace you know there's an error and can address it. The inconsistency is often creepy and takes very long until someone spots that.
> It is recommended that you avoid using SERIALIZABLE in Galera Cluster.
Aphyr:
> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
Eh.
Edit: username
If your product is so complex or so ill-specified that full time testers can't make it work correctly, it's probably difficult for your developers to even understand or fix the problems.
We ignore how much of software development has become "it works for me under all of my default assumptions as the developer of the product—ship it," especially when faced with business deadlines and business management focused around business objectives (and maybe not so much around software quality or correctness).
It is important however behavior in this case is clearly documented.
I think there is decent documentation about Innodb describing how transactions work in Innodb http://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-mo...
Galera would benefit having more clear documentation about what data consistency model exactly it provides.
At the same time Percona XtraDB Cluster, MariaDB Cluster can be used to built reliable applications assuming you're writing to their consistency model correctly.
Buncha people giving me <filth> for calling data written through an invariant violation "corrupted state", like somehow it's not garbage.
If TCP checksums don't work right we don't call the packet "inconsistent." We call it corrupt. If a disk shuffles your file's bits? Corrupt.
I use the word corrupt to emphasize that not only has the system <messed> up, but you have no way to know your data is now <messed> up.
Not very classy. And the point of Aphyr still stands I think. In default mode it is easy to get corrupt data with Galera Cluster. That InnoDB on a single instance can have the same problem makes it all the more troubling and I'm glad I moved away from MySQL a long time ago.
However, for the purposes of Aphyr's original article, it's a moot point specifically because Aphyr replaced the symbol with the substance[0]. He specifically states the conclusion, and in detail explains why it happened and what the ramifications are. Any quibbling about the word corruption is a distraction, because the underlying fault of the system is still present. The system claims to support snapshot isolation, but does not, and the result of that fact is that data will not be correct for users expecting snapshot isolation.
[0] - http://lesswrong.com/lw/nv/replace_the_symbol_with_the_subst...
edit - sorry, i just noticed i actually did say, in another thread here, that corruption is worse. i would like to take that back now :D
This may be another example of reactions to Aphyr's reports telling about the mental model of the developers.