Question what is better isolation level is poping up again and again. Recently it was discussed in InnoDB : Any real performance improvement when using READ COMMITED isolation level ? and in Repeatable read versus read committed for InnoDB .
Serge in his post explains why READ COMMITED is better for TPCC load, so
why don’t we take tpcc-mysql bencmark and check on results.
I took 3 different datasets 1000w (100GB of data), 300w (30GB) and 10w (1GB) for box with 32GB of RAM and buffer_pool 26GB. Latest case 10w is interesting as I expect a lot of contention on row level having small dataset.
I used as usually tpcc-mysql benchmark with 16 and 32 (for 10w) concurrent users.
Also I had binary log enabled on RBR mode (as READ-COMMITED does not support STATEMENT based replication in 5.1), and everything was run under XtraDB-9 ( based on InnoDB-plugin 1.0.6)
So there are results:
For 1000w:
There READ-COMMITED seems more preferable, however difference is very small.
For 300w:
I would say both modes are even there, there is no winner for me.
for 10w:
Almost indentical resutls in this case.
However, however.
For 10w run I got 60 Deadlock errors “1213, Deadlock found when trying to get lock; try restarting transaction” in READ-COMMITED mode, and 0 Deadlocks in REPEATABLE-READ mode.
I understand that 60 deadlocks for total 2704687 transactions can be ignored, but it seems you have better chance to get DEADLOCK in READ-COMMITED then in REPEATABLE-READ.
So both modes looks even for me, though some facts to consider:
