In my previous post "InnoDB : Why not use the same isolation level as ORACLE" I suggested that like ORACLE it might now be a good idea to use READ COMMITTED as the default isolation level for InnoDB.
Marc Callaghan made interesting measures that illustrate a case where READ COMMITTED mode degraded performance.
("Repeatable read versus read committed for InnoDB ")
Can we draw conclusions from what has been measured ?
Let us look in more detail to what has been measured. The workload is generated by sysbench :
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
The following statements are generated :
SELECT c from sbtest where id=3530
SELECT c from sbtest where id between 6248 and 7247
SELECT c from sbtest where id between 1945 and 2944 order by c
SELECT DISTINCT c from sbtest where id between 7650 and 8650 order by c
SELECT SUM(K) from sbtest where id between 7226 and 8225
Statements are only selects which access rows based on id or range, sometimes with aggregated sum, distinct and order by.
There is no insert update or delete as sysbench is run with oltp-read-only option.
What workload does benefit of the the READ COMMITTED isolation level. The READ COMMITTED isolation level allows to avoid extra locking.
First we need to understand what kind of locks InnoDB generate when run in REPEATABLE READ isolation level:
INSERT ... SELECT ... put a shared next key lock on all the selected rows.
UPDATE ... WHERE ... set exclusive next-key lock on every record the search encounters.
DELETE ... FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.
SELECT ... FROM ... LOCK IN SHARE MODE sets shared next-key locks on all index records the search encounters.
SELECT ... FROM ... FOR UPDATE sets exclusive next-key locks on all index records the search encounters and also on the corresponding clustered index records if a secondary index is used in the search.
It should be noted that InnoDB minimizes gap locking by using record-only locks in UNIQUE searches.
The workload generated by sysbench contains none of the statements that would have benefited from READ COMMITTED isolation level. So no benefit is to be expected. A slight degradation is even measured. This is related to the fact that being in READ COMMITTED we call once per statement the read view (snapshot) code. This code is run once per transaction for REPEATABLE READ. Calling this code is a source of mutex contention on kernel_mutex. This is what is measured by this sysbench test.
What is a good workload that would really benefit from READ COMMITTED isolation level ?
Here are some example of statements run with REPEATABLE READ isolation level. It is base on a dbt2/TPC C like data model.
It shows evidence of generated locks that blocks other statements :
UPDATE CUSTOMER SET C_DATA='MODIFIED' WHERE C_ID > 3000 AND C_D_ID=10 AND C_W_ID=3; // will do next key lock and blocked the next insert because of next key lock above 3000
INSERT INTO CUSTOMER (C_ID,C_D_ID,C_W_ID ) VALUES (4000,10,3); // the lock set above is an inhibitive lock which prevent insert into the customer table
UPDATE CUSTOMER SET C_DATA='MODIFIED' WHERE C_W_ID=3 AND C_D_ID=10 ; // will block the next one as only part of the primary key is used and all the corresponding gap are locked!
INSERT INTO CUSTOMER (C_ID,C_D_ID,C_W_ID ) VALUES (6003,10,3);
INSERT INTO SUMMARY (AGREGATE) SELECT AVG(OL_QUANTITY*I_PRICE) FROM ORDER_LINE,ITEM WHERE I_ID=OL_I_ID AND OL_DIST_INFO LIKE '%H%I%';
// the previous statement put a shared lock on all the read rows.
UPDATE ORDER LINE SET OL_AMOUNT=3.5 WHERE OL_DIST_INFO LIKE '%H%'; // This update is blocked by the lock on the records that have been selected.
DELETE FROM ORDER_LINE WHERE OL_I_ID=80824; // will block next one as one column has the same value though it is not part of the primary key and is not indexed!!!
INSERT INTO ORDER_LINE (OL_O_ID,OL_D_ID,OL_W_ID,OL_NUMBER,OL_I_ID) VALUES (7777,23,2,5,80824); // It will in fact block any insert as ol_i_id is not indexed !
INSERT INTO ORDER_LINE (OL_O_ID,OL_D_ID,OL_W_ID,OL_NUMBER,OL_I_ID) VALUES (7777,23,2,5,80825); // This one is also blocked
We must notice that none of this blocking behavior exist with Oracle. Hopefully we can now do the same with InnoDB.
What will be the impact on performance ?
When MySQL is run with the READ COMMITTED isolation level the next-key lock that forbid inserts in gaps disappears. All of the above update or delete statement will no more block the inserts.
The shared locks set by "INSERT ... SELECT ..." do not exist anymore. This means that a report computing aggregate and based on "INSERT ... SELECT ..." can run and the selected rows can be updated meanwhile. This is important even on a slave as an "INSERT ... SELECT ..." blocking all updates on all the selected rows can be very damaging as it will increase the replication lag for the slave.
Being in READ COMMITTED means also there is no need to keep as much undos as in REPEATABLE READ. Only a single statement need to be consistent. 2 consecutive select do not see the same data if some data has been committed in between.
READ COMMITTED isolation level can be set for a single instance. If this instance is part of replication then replication should be set up in row based mode to allow READ COMMITTED isolation level.
But I agree that what really mind is real life and your particular workload.
So the question we all have is will my particular workload benefit from the READ COMMITTED isolation level ?
The only case where you will for sure not benefit from the READ COMMITTED isolation level is when your are only running selects. This is the case tested in Marc Callaghan sysbench test. I agree with Mark that it would be great if MySQL dev team or the community could address this contention issue on kernel_mutex.
For other cases it is worth checking. Of course MySQL will not turn READ COMMITTED as the default isolation level as it would impact users upgrading version.
But as users it sounds now as a reasonable choice and worth testing. One last point I must acknowledge is that not being the default isolation level READ COMMITTED code path is much less tested than the REPEATABLE READ one. So be careful.
Any feedback on positive or negative impact on recent version is welcomed.