InnoDB & Isolation Levels - Aren't repeatable reads a bad thing?

I was reading about InnoDB's isolation levels and it makes sense for the most part, but what I don't get is why are unrepeatable reads a bad thing? Shouldn't it be the other way around?

For examples:

So say we had a stock column for selling products and each time someone bought an item we would take 1 item from the column, with Repeatable read or serializable isolation levels wouldn't this corrupt the data integrity?

For example:

TX A: start transaction;
TX B: set session transaction isolation level repeatable read;
TX B: start transaction;
TX A: select stock from products;               -- val = 8
TX B: select stock from products;               -- val = 8
TX A: update products set stock = stock - 1;    -- val = 7
TX B: select stock from products;               -- val = 8
TX A: commit
TX B: select stock from products;               -- val = 8, incorrect!
TX B: commit;
TX B: select stock from products;               -- val = 7

Unless it uses the CORRECT value when running an update?

InnoDB isolation levels are not "bad" or "good", it depends on what your application requires in terms of isolation.

In your example, by using repeatable read, you make sure that once the first select has been done, no other transaction can modify the state of the database, from the point of view of that transaction. In other words, it is as if you had run "START TRANSACTION WITH CONSISTENT SNAPSHOT" on before your 5th line. This may be required in some cases, and let me put you an example:

TX A: START TRANSACTION;
TX A: SELECT * FROM products WHERE stock BETWEEN 4 AND 6; -- I see rows 4 and 6 only,
                                                          --  and I want to update those only   
TX B: START TRANSACTION;
TX B: INSERT INTO products (stock) VALUES (5);

TX A: UPDATE products SET stock = 3 WHERE stock BETWEEN 4 AND 6;
TX A: COMMIT; -- race condition
TX B: COMMIT;

By setting InnoDB's REPEATABLE READ, phantom reads disappear, because TX B will be locked at the insert until A commits. However, if we are doing READ COMMITTED, we will get different outputs depending if A or B commits first, something that violates the ACID properties, and that our application may not expect.

In particular, STATEMENT-based replication requires no phantom reads, that is why READ COMMITTED is the default level, and has even a stricted behaviour than READ COMMITTED in other databases (where serializable is needed to get the InnoDB behaviour). If the first select was an update, executing those transactions independently would lead to different results (drifting the slave). Please note that transactions are written to the binlog atomically and serially.

I agree with you that that may not be needed in most applications, because you will handle those cases at application level. If that is the case, use ROW-based replication and set the default transaction isolation level to READ COMMITTED. You will also get in most cases better concurrency.

StackExchange Database Administrators Q#84057, answer score: 5