3 ms·

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 th

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.

inversionOf 11y ago
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.
- AlisdairO 11y ago
  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 :-).

inversionOf 11y ago

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.

AlisdairO 11y ago
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 :-).