The difference of Isolation level – Read Committed vs Repeatable Read

MySQL’s default isolation level is “Repeatable Read“. But Oracle’s default isolation level is “Read Committed“. In this post, I will show you the difference by example.

Test environment : MySQL 5.7

Test table

create table lock_test(
col1 varchar(10),
col2 varchar(20),
primary key(col1));

insert into lock_test values('1', 'A');
insert into lock_test values('2', 'B');
insert into lock_test values('3', 'C');

Test scenario 1 – Read Committed

Seq	Session 1	Session 2
1	set session transaction isolation level read committed; start transaction;	set session transaction isolation level read committed; start transaction;
2	select * from lock_test where col1 = ‘1’; => return value : ‘A‘
3		update lock_test set col2 = ‘AA‘ where col1 = ‘1’;commit;
4	select * from lock_test where col1 = ‘1’; => return value : ‘AA‘
5	commit;

Test scenario 2 – Repeatable Read

Seq	Session 1	Session 2
1	set session transaction isolation level REPEATABLE READ; start transaction;	set session transaction isolation level REPEATABLE READ; start transaction;
2	select * from lock_test where col1 = ‘1’; => return value : ‘AA‘
3		update lock_test set col2 = ‘AAA‘ where col1 = ‘1’;commit;
4	select * from lock_test where col1 = ‘1’; => return value : ‘AA‘
5	commit;
6	select * from lock_test where col1 = ‘1’; => return value : ‘AAA‘

Notice that in the 4th step, session 1’s query result is still “AA” although session 2 already updated it into “AAA”.

Session 1 gets the new value after the transaction has finished.

Conclusion

• In Read Committed mode, query result for the same record can change within a transaction.
• In Repeatable Read mode, query result for the same record doesn’t change within a transaction.