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 ! :-)

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

  • I like the default mode as READ COMMITTED. By default every instance I start, always will ensure that isolation is set to READ COMMITTED. Majority of the applications does not need to be in the repeatable read, and read committed is the way to scale without lock contention on multi core systems.

    I like the idea to make READ COMMITTED as default in future releases

  • krteQ

    But be careful, don’t try this before version 5.1.37

    http://bugs.mysql.com/bug.php?id=45357

  • It’s all about perspective… the reason for Oracle defaulting to READ COMMITTED is also historical, and related to its architecture.
    Using REPEATABLE READ in Oracle has several drawbacks.

    While there’s a case for using READ COMMITTED in MySQL sometimes (such as Oracle compatibility), it’s important to realise that each choice has consequences, with both good and “bad” things, but what is good and bad depends entirely on your specific needs.

    For instance, statement based replication works very well overall, and particularly for long-distance replication and update statements that affect many rows it’s very efficient. Rather than copying say a million row changes, it only needs to transmit the update. In such an environment, that’s a critical advantage.
    So, it all depends…

  • Andy

    What about going 1 step further and using READ UNCOMMITTED (dirty reads)?

    For typical web usage such as blog or forum, is there any danger in using READ UNCOMMITTED?

    Will using READ UNCOMMITTED prevent the usage of (row-based) replication?

  • nobody

    this is why using “READ COMMITTED” with mysql can be a really bad idea:

    If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.

    With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.

    =>READ COMMITTED doesn’t give you consistent results in a transaction!

    Databases implement the isolation levels differently (from ask Tom):

    Oracle explicitly supports the READ COMMITTED and SERIALIZABLE isolation levels as they’re defined in the standard. However, this doesn’t tell the whole story. The SQL standard was trying to set up isolation levels that would permit various degrees of consistency for queries performed at each level. REPEATABLE READ is the isolation level that the SQL standard claims will guarantee a read-consistent result from a query. In the SQL standard definition, READ COMMITTED doesn’t give you consistent results, and READ UNCOMMITTED is the level to use to get nonblocking reads.

    However, in Oracle Database, READ COMMITTED has all of the attributes required to achieve read-consistent queries. In other databases, READ COMMITTED queries can and will return answers that never existed in the database. Moreover, Oracle Database also supports the spirit of READ UNCOMMITTED. The goal of providing a dirty read is to supply a nonblocking read, whereby queries are not blocked by, and do not block, updates of the same data. However, Oracle Database doesn’t need dirty reads to achieve this goal, nor does it support them. Dirty reads are an implementation other databases must use to provide nonblocking reads.

  • krteQ

    As far as performance is concerned, you should do some real-application benchmark. For example, in this case READ COMMITED has lower performance at various number of concurrent clients:

    http://www.facebook.com/notes/mysqlfacebook/repeatable-read-versus-read-committed-for-innodb/244956410932

  • Justin Swanhart

    As nobody mentioned, the semantics of actual database implementations of each isolation varies. Oracles READ COMMITTED and InnoDB’s REPEATABLE READ really have the same semantics. Using READ COMMITTED on InnoDB can result in:

    1) performance issues due to the fact that a new snapshot must be created /for each statement/:
    http://bugs.mysql.com/bug.php?id=49169

    2) inconsistent results in a transaction unless you use SELECT … FOR UPDATE

    I don’t see any advantage to READ COMMITTED on InnoDB.

Leave a Reply

  

  

  


7 × = seven

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">