CODEWITHSUNDEEP
mysqltransactionsinnodbisolation-level
Frank J
Frank J

Reputation: 1706

Repeatable Read isolation level SELECT vs UPDATE...WHERE

maybe you can shed light on something for me here:

DB = MySQL 5.7

Storage engine: InnoDB

Isolation level: Repeatable Read

Following table:

---------------
|   MyTable   |
---------------
| PK | Concur |
---------------
| 3  |   2    |
---------------

I have no transaction going at this point in time and I select this record like

SELECT * FROM MyTable WHERE PK = 3

and store the result in my program.

I start now a DB transaction. An outside process increments Concur for the record with PK = 3 from 2 to 3 after my transaction has started.

I have not yet read again from that table inside my transaction.

I issue the following query from inside my transaction:

UPDATE MyTable SET Concur = 3 WHERE PK = 3 AND Concur = 2

This will succeed with 0 records affected. So clearly, it evaluates against the data that has been altered AFTER my transaction has started. Still in the transaction I subsequently query:

SELECT * FROM MyTable WHERE PK = 3

which will return me the record with PK = 3 and Concur = 2 which are the values from before the transaction.

Why do SELECT and UPDATE ... WHERE behave differently, what am I missing?

I would have expected that the UPDATE ... WHERE statement either fails directly instead of succeeding with 0 records affected, or alternatively it succeeds there with 1 record affected and then blows at the COMMIT afterwards, but not this mix and match.

Any insight here?

Upvotes: 7

Views: 3869

Answers (3)

Rick James
Rick James

Reputation: 142540

Signal your intent with FOR UPDATE:

BEGIN;
SELECT ... FOR UPDATE;
...
(no other thread can change that row until you `COMMIT` or `ROLLBACK`)
...
COMMIT;

On the other hand...

BEGIN;
...
(At this point another thread modifies the row...)
...
SELECT ... FOR UPDATE;  -- you are blocked until they COMMIT or ROLLBACK

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562951

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly: If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.

The important condition is if you change the rows, your consistent read is "refreshed" so it includes the change you just made.

But if you UPDATE, it's always made to the most recent version of the row, not the version your transaction's consistent read can view. Therefore your UPDATE may have no net effect, if another transaction has already made that change. That's the situation you observed.

Therefore your transaction issued an UPDATE but didn't change the row.

This is perhaps not how you would like InnoDB to behave, but it is nevertheless how it does behave.

Upvotes: 7

nacho
nacho

Reputation: 5396

To get affected by the Repeatable Read isolation level, you must be inside the same transaction. This means that your two selects must be inside a transaction, so they are not afected by wherever you change in your DB from an external transaction.

So, as you say, I have no transaction going at this point in time and I select this record like SELECT * FROM MyTable WHERE PK = 3 and store the result in my program. you are making a transaction with just one statement. After that, you start a transaction with your Update.

What you should do is

 START TRANSACTION
      SELECT * FROM MyTable WHERE PK = 3
      ------                                        START TRANSACTION
      -----                                             UPDATE MyTable SET Concur = 3 WHERE PK = 3 AND Concur = 2                                            
      -------                                       END TRANSACTTION
      -------
      -------
      SELECT * FROM MyTable WHERE PK = 3
 END TRANSACTION

In the left side, you have your select transaction, and in the right side your update. The left transaction is not afected by the update in this way.

Upvotes: 0

Related Questions