… an example: “InnoDB provides REPEATABLE READ for read-only SELECT, but it behaves as if you use READ COMMITTED for all write queries, in… row doesn’t exist anymore. You can read it (REPEATABLE READ) but you can modify it (READ COMMITED). I open a new transaction again…, you can read the row with the value andy, but for queries that write data, the isolation is READ COMMITED so you can…

… FOR UPDATE. InnoDB provides REPEATABLE READ for read-only SELECT, but it behaves as if you use READ COMMITTED for all write queries, in… level (considering only the two most common isolation levels, REPEATABLE READ and READ COMMITTED). What is a gap lock? A gap lock is… can deal with phantom reads and your binary log is in row format, changing the ISOLATION to READ COMMITTED will help you to…

… use READ COMMITTED for all write queries, in spite of your chosen transaction isolation level” Statement 2: “changing the ISOLATION to READ COMMITTED will help you to avoid all those extra locks” But if MySQL behaves as if READ COMMITTED is used… difference? Whether ISOLATION is set to READ COMMITTED or REPEATABLE READ, MySQL would still behave as if READ COMMITTED is used according to statement 1…

… example the isolation level is READ-COMMITTED. but as i know, when the isolation level is READ-COMMITTED, the phantom reads are always exist, and not related to the select type,but only up to whether the another transaction commit. because the READ-COMMITTED means read the latest result that it could.

… to READ COMMITTED or REPEATABLE READ? I’m just confused by the statement in the article: “it behaves as if you use READ COMMITTED for… ISOLATION to, MySQL is just going to behave as if READ COMMITTED is being used. Maybe you could edit that statement to…

Andy, With READ COMMITTED InnoDB doesn’t take additional locks on the gap, improving … the gap. Compare this example with the following one using READ COMMITTED: session1> delete from t where i=25; Query OK, 1…(26); Query OK, 1 row affected (0.00 sec) session1> commit; session2> commit; session1> select * from t; +——+ | i | +——+ | 21 | | 26 | | 30 | +——+ No…

… use READ COMMITTED for all write queries, in spite of your chosen transaction isolation level” is that transactions will be REPEATABLE READ until the first write (or SELECT FOR UPDATE) happens, at which point the transaction is implicitly converted to READ COMMITTED. I…

Peter, The first example is READ COMMITTED. I’ve changed the isolation level to get that example of a phantom read. With REPEATABLE READ the rows and gaps gets locked and then you get the lock wait timeout.

… mean when you say that writes behave as if in READ COMMITTED. My testing shows that the SELECT … FOR UPDATE statement itself…