[shariqm]

"MVCC can't stop two competing transactions debiting the same account pair (i.e., I have two linked accounts A & B with $100 in each and I launch two simultaneous transactions to transfer $150 away from both: afterwards, I'll end up with -$50 in both or some garbage data)."

That isn't true. In a pure MVCC world you could detect such issues, specifically that two trxs were opened and they both tried to update the same row. This would generate a conflict and one transaction could just get shot. Snapshot-isolation/MVCC describes this issue.

However what InnoDB/mysql and Clustrix actually do is use MVCC for read-write conflicts and two-phase-locking for write-write conflicts which trivially fixes your proposed problem.

[MichaelSalib]

To clarify the example, the two simultaneous transactions are updating different rows: one updates account A's row while the other updates account B's. There is no way for MVCC to recognize this conflict: it will happily corrupt your data.

[shariqm]

Sorry, I'm not following. If you have 2 rows storing how much money you have in 1 account then your data isn't normalized and serializability isn't going to solve your problem, if you could be (even) more explicit, I might be able to answer.

[pbailis]

I think we're conflating Snapshot Isolation and MVCC (e.g., Snapshot-isolation/MVCC). MVCC is a general concurrency control mechanism, not an isolation level. Coupling MVCC and Snapshot Isolation is like saying "using locks provides serializability," which is not true in general--it depends on how you use the locks.

That said, Snapshot Isolation can lead to anomalies like Example 1.2: http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.142... If you perform additional synchronization (e.g., 2PL, as you describe), then you can get serializability.

[MichaelSalib]

Yes, you're totally correct. I was thinking of SI.

[MichaelSalib]

The idea is that each account has a balance. Say I have a checking and savings account: there's one row for each and the bank will kindly let either of them get a negative balance provided the sum of both balances is positive.

You may not care for this schema design, but I think most people would be surprised that Oracle silently corrupts data and violates constraints here.

[akeefer]

I'm not sure that people who use relational databases would be surprised that there can be data-level race conditions. For example, if you write code like: 1) User requests transfer in the UI 2) Query the DB to see if the transfer is legal 3) If so, update the database.

you have to think about race conditions even if the database implements serializability. First of all, you have to have enough of a clue to do steps 2 and 3 inside the same database transaction, or else the isolation level doesn't help you in the least. Everyone who deals with an RDBMS has to worry about transaction boundaries all the time anyway to avoid race conditions, so it's not too much of a leap to assume that people who are used to worrying about transaction boundaries will have to have some idea of what kind of problems those transaction boundaries will prevent for them. Most databases in production systems don't do full-on serializability as an isolation level (and if they do, it's too expensive to turn on), so pretty much every engineer's real-world experience is that if they do a query against table A, then an update against table B based on that data, that they can have a race condition and they need to lock A or materialize potential conflicts.

I guess it's possible that my personal experience or the engineers that I work with are not a representative sample, but if you're working with sensitive issues like money transfers you tend to be really, really super-paranoid about it, and there's no way that something like that is overlooked by a halfway competent team.