use Perl

• I would imagine something like this: (Score:1)

  by Adrian (66)

  Fred and Mary start with:

  1 A
  2 B
  3 C

  Fred starts a transaction and updates the first two rows so the table that looks like:

  1 X
  2 Y
  3 C

  In the mean time Mary has started and finished a transaction that updates the third row:

  1 A
  2 B
  3 Z

  which means that Fred is seeing the "old" version of row three, and the table as a whole reflects something that will never have existed in the database.

  Make sense?
  • Re:I would imagine something like this: (Score:1)

    by btilly (5037) on 2007.09.04 10:30 (#57422) Journal

    Your imagination is quite likely wrong in this instance.

    I don't have a MySQL database to play with, but if that was Oracle then by default in your situation, Fred will see a Z in the third row. The reason is that consistent reads are consistent at the statement level, not the transaction level. So you see changes that have been committed after your transaction started, but before your statement began.

    The technical reason for this is that with Oracle's implementation, consistent reads have to be done by reading a special data segment called rollback. If you try to do a consistent read with data that has scrolled off of the rollback segment you get a "rollback segment too old" error. Nothing can really be done about that error, and rollback segments generally scroll off pretty quickly on transactional databases. So Oracle chose a default that minimizes how often you get that error.

    My understanding is that InnoDB closely mirrors Oracle's implementation, so I wouldn't be surprised if they have the same behaviour.

    I should note that your expectation would be correct for PostgreSQL. It does work from the beginning of the transaction until past versions of data are cleaned up with a vacuum command.

    I should further note that at one point I knew Oracle's implementation well enough to believe that its guarantee wasn't quite guaranteed. I convinced myself and a couple of DBAs that if, during a statement, one user committed a change to a row, then a second user updated and committed that row again, then you read it, you'd get the first user's version of the row. However I never tested this theory, so my theory may be wrong. (Something about not having sufficient access to an Oracle database, and not considering it important enough to set up a database I did have access to.) I have no opinion on whether InnoDB mirrors Oracle closely enough to mirror this possible flaw. However I'm quite sure that PostgreSQL does not suffer from this potential flaw.

    Reply to This

    Parent

    • Re: (Score:1)

      by perrin (4270)

      It depends on the isolation level you're using. The default level for InnoDB is a bit safer than the default for Oracle. It is REPEATABLE READ, which does keep a consistent snapshot from the beginning of the transaction. To get something closer to Oracle's default, you can use READ COMMITTED, which only guarantees consistency on a statement level. Both databases support SERIALIZABLE, which does extra locking and prevents some tricky cases like phantom rows.