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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.