REPEATABLE READ vs READ COMMITTED

There are four SQL transaction isolation levels supported by InnoDB: REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED, and SERIALIZABLE. Because READ UNCOMMITTED and SERIALIZABLE are rarely used, I am going to outline the distinction between READ COMMITTED and REPEATABLE READ. Perhaps I will follow up with SERIALIZABLE and READ UNCOMMITTED if there is interest.

REPEATABLE READ:
The state of the database is maintained from the start of the transaction. If you retrieve a value in session1, then update that value in session2, retrieving it again in session1 will return the same results. Reads are repeatable. Repeatable Read.

session1> BEGIN;
session1> SELECT firstname FROM names WHERE id = 7;
Aaron

session2> BEGIN;
session2> SELECT firstname FROM names WHERE id = 7;
Aaron
session2> UPDATE names SET firstname = 'Bob' WHERE id = 7;
session2> SELECT firstname FROM names WHERE id = 7;
Bob
session2> COMMIT;

session1> SELECT firstname FROM names WHERE id = 7;
Aaron

READ COMMITTED:
Within the context of a transaction, you will always retrieve the most recently committed value. If you retrieve a value in session1, update it in session2, then retrieve it in session1 again, you will get the value as modified in session2. It reads the last committed row. Read Committed.

session1> BEGIN;
session1> SELECT firstname FROM names WHERE id = 7;
Aaron

session2> BEGIN;
session2> SELECT firstname FROM names WHERE id = 7;
Aaron
session2> UPDATE names SET firstname = 'Bob' WHERE id = 7;
session2> SELECT firstname FROM names WHERE id = 7;
Bob
session2> COMMIT;

session1> SELECT firstname FROM names WHERE id = 7;
Bob

Make sense?

Tags: , ,

6 Responses to “REPEATABLE READ vs READ COMMITTED”

  1. Nico Says:

    Hi,

    thanks for the quick overview!

    My questions about this is there is a huge difference in performance about those two isolation levels.
    Can you explain why?
    I know to use any of those two isolation levels depends what you are trying to achieve but besides that what is better for server which doesn’t have much memory or big storage?

    thx
    nico

  2. Aaron Says:

    Hi Nico,

    The performance differences between RR and RC is complicated and has less to do with hardware and more to do with internal contention. There are a few things to consider. Internally, READ COMMITTED requires much less in the way of locking and mutexes, so raw transactional throughput should be higher in certain cases. Also, because there is less versioning required in RC, the undo space should see less activity. The undo space is stored on disk, so that could improve I/O performance. However, the flip side of that is that when running in RC mode, only row based replication is supported, which can result in much greater disk activity when writing the binary logs.

    Percona did a tpcc benchmark on RC vs RR a couple years ago and found the total transactional throughput to be roughly the same.

    In my personal experience, I have switched OLAP servers to RC because analytics activity patterns such as INSERT … INTO SELECT were blocking updates from the slave SQL thread. I also will be blogging shortly (on the ideeli technical blog) about a major problem that we fixed at ideeli by setting specific transactions to run in RC.

  3. Aaron Says:

    I found a couple other articles that discuss the performance differences w/ RC vs RR in InnoDB with very interesting results:

    http://serge.frezefond.free.fr/?p=269
    https://www.facebook.com/note.php?note_id=244956410932

  4. heta Says:

    yup that makes sense, thanks for explaining :)

  5. Jackson Says:

    Hi Aaron, thanks for your post :)

    You mentioned in a previous comment that you have solved some problems at ideeli setting specific transactions to RC. Can you show me the links to this posts?

    Thanks in advance.

  6. Aaron Says:

    I discussed that over at the ideeli Tech Blog: http://insatiabledemand.ideeli.com/post/18850015294/diagnosing-mysql-auto-inc-and-gap-locks

Leave a Reply