I'm sorry but I havent't found a better wording for the title.

Given the table a:

    id INT NOT NULL auto_increment PRIMARY KEY,
    ref VARCHAR(10) NOT NULL,
    val INT NOT NULL

INSERT INTO a(ref, val) VALUES ('ten', 10);

And two transactions Tx1 and Tx2 with default isolation level, whose queries are issued in the following order, is the result shown in step [*] of transaction Tx2expected?


            Tx2: START TRANSACTION;

            [^] Tx2: SELECT * FROM a WHERE id='1';
            (without FOR UPDATE)
                "id"    "ref"   "val"
                "1"     "ten"   "10"

    "id"    "ref"   "val"
    "1"     "ten"   "10"

            Tx2: SELECT * FROM a WHERE id='1' FOR UPDATE;

Tx1: UPDATE a SET v=20 WHERE id;


            Tx2: (locked on 'SELECT * FROM a WHERE id='1' FOR UPDATE')
                "id"    "ref"   "val"
                "1"     "ten"   "20"

            [*] Tx2: SELECT * FROM a WHERE id='1';
            (without FOR UPDATE)
                "id"    "ref"   "val"
                "1"     "ten"   "10"

[*]: Shouldn't this give the updated row value 20 for column val?

UPDATE: If I remove the query [^], then [*] yields the value 20.

This is on 5.6.39; queries were issued using third party client - HeidiSQL - though I guess that shouldn't matter.

  • Do you mean that you run [*] immediately after the select ... for update returned? And then you get a different value than the previously finished select? – a_horse_with_no_name Feb 5 at 9:15
  • @a_horse_with_no_name, yes, I reported the exact sequence of queries issued in each transaction. – watery Feb 5 at 9:19
  • That indeed sounds strange. What happens when you use read committed instead of the default repeatable read? – a_horse_with_no_name Feb 5 at 9:32
  • @a_horse_with_no_name, with that isolation level I get the expected value of 20 - in the meantime I added a small update. – watery Feb 5 at 9:38

A guess:

"Repeatable Read" means that the identical SELECT will produce identical results throughout the transaction. But, apparently, the FOR UPDATE clause is enough to make the two selects not 'identical'.

Under the covers... There is a clock (sequence number) ticking away. Whenever a row is modified, it is copied over. Now there are two copies of that row, one with the old clock, one with the new. (See "History list".) Your first SELECT is associated with the older clock value; the second SELECT, not being identical to the first, has a newer clock value, hence linked to a different copy of the row.

After the transaction if finished (COMMIT or ROLLBACK), the extra copy of the row is tossed. (I think this is a background task for COMMIT, but a "do it now" task for ROLLBACK.) Meanwhile, other connections pick the appropriate row from the history list for each row based on their isolation_mode. Roughly speaking, READ_COMMITTED goes to the old end of the list; READ_UNCOMMITTED ("dirty read") goes to the new end.

Things are optimized on the "optimistic" assumption that COMMITs are more likely than ROLLBACKs. Hence, the comments in the previous paragraph, and the slowness of ROLLBACK.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.