Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • 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?

    • 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 b

      • 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.