Repeatable read versus read committed for InnoDB
There is a lot of documentation available on transaction isolation levels for InnoDB including the InnoDB transaction model, a description of the isolation levels, notes on locks, locks set by statements and notes on consistent non-locking reads.
There is another issue that has not been documented. The code that creates a read view (snapshot) for InnoDB transactions is a source of mutex contention on kernel_mutex. This code is run once per statement for read-committed and once per transaction for repeatable-read.
The function read_view_open_now is run to create a snapshot. It copies the list of uncommitted transactions into a per-transaction data structure. Memory is allocated from the per-transaction heap to store the copied data and that allocation might require a call to malloc. All of this work is done while holding kernel_mutex and other threads are unlikely to get much work done in InnoDB when kernel_mutex is locked.
Subscribe to feature request 49169 if you are interested in this. I am not sure if there is an easy fix. Work can be done to reduce the chance of allocating memory while copying the open transaction list. But splitting kernel_mutex into several locks might be hard.
When repeatable-read is used there are fewer calls to read_view_open_now and there is less contention on kernel_mutex. I ran sysbench to measure the difference in performance.
These are results from sysbench oltp using repeatable-read versus read-committed with MySQL 5.0.84. The numbers are transactions per second for 1, 2, 4, 8, 16, 32, 64, 128, 256 and 512 concurrent users. The MySQL server and sysbench clients ran on the same 8-core x86 server:
186 356 649 1137 1130 1106 1074 1066 1039 1004 repeatable-read
177 335 641 1111 1094 1075 1046 1011 957 886 read-committed
The sysbench command line:
sysbench --test=oltp --oltp-table-size=2000000 --max-time=60 \
--max-requests=0 --mysql-table-engine=innodb --db-ps-mode=disable \
--mysql-engine-trx=yes --oltp-read-only --oltp-dist-type=uniform \
--oltp-range-size=1000 --num-threads=$n --seed-rng=1 run
# use one of these
# transaction_isolation = READ-COMMITTED
# transaction_isolation = REPEATABLE-READ