Skip to content

InnoDB : Why not use the same isolation level as ORACLE ?

By default InnoDB uses REPEATABLE READ as its isolation level. So this is the isolation level used with innoDB by almost all MySQL users.

The default isolation level of Oracle is READ COMMITTED. READ COMMITTED is the mode widely used by Oracle users. This mode incurs less penalty on the server scalability by allowing to support more concurrency.

The reason why InnoDB use REPEATABLE READ as its default is historical. This is a related to the way MySQL replication was developed . MySQL replication until 5.1 functioned with a statement based replication mechanism. This means that statements that occurs on the master server are replayed on the slave server. The statement base replication mode does not permit to use the READ COMMITTED isolation level. In that case replication will not guaranty consistency between the slave and the master.

The MySQL widely used statement based replication has some major drawbacks. The first one is that to guaranty the same effect of statements on slave as on master innoDB need to be careful when generating id through the autoincrement mechanism. The second impact is that InnoDB also needs to avoid phantoms to guaranty the same effect of a statement on the master and slave.

To generate consecutive autoincrement id during a multi rows insert a table level lock is necessary.

To handle phantom issue InnoDB has implemented a mechanism that consist in locking gaps.

UPDATE ... WHERE or a DELETE ... FROM ... WHERE requires InnoDB to set an exclusive next-key lock on every record the search encounters. This can dramatically impact concurrency on the server by forbidding inserts into the gaps.
INSERT ... SELECT will put a shared lock on all the selected rows. This basically prevent any update on the selected rows. This also seriously impact concurrency. This case is very common when reports are run on an OLTP server.
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.
SELECT ... FROM ... LOCK IN SHARE MODE sets shared next-key locks on all index records the search encounters

Oracle does not behave that way. The Oracle replication mechanism is a row based mechanism. None of the blocking locks mentioned above with InnoDB will happen with Oracle.

Can we have with MySQL the same behavior as Oracle users ?

Yes we can ! Since version 5.1 MySQL offers a row based replication mode closed to what ORACLE is doing. This basically allow to run MySQL in READ COMMITTED isolation level. This can really benefit to MySQL performance and scalability. To achieve that it is mandatory to use Row based replication. This suppress most restrictions related to MySQL statement based replication. The READ COMMITTED isolation level relax most of the locking issues impaired by the REPEATABLE READ MODE. This seems anyway to be used rarely by MySQL 5.1 users !

I have no figures to prove performance improvement impacts of READ COMMITED isolation level with InnoDB,
so you should give it a try ! 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *