InnoDB is an ACID-compliant transactional storage engine for MySQL that uses MultiVersion Concurrency Control (MVCC). It has been the default storage engine for MySQL since version 5.5.5
InnoDB is an ACID-compliant transactional storage engine for MySQL that uses MultiVersion Concurrency Control (MVCC) to achieve nonblocking SELECTs and very high concurrency. It has been the default storage engine for MySQL since version 5.5.5
InnoDB's MVCC supports four levels of Transaction Isolation
- READ-UNCOMMITTED : allows a transaction to see uncommitted changes made by other transactions. This isolation level allows dirty reads, non-repeatable reads, and phantoms to occur.
- READ-COMMITTED : allows a transaction to see changes made by other transactions only if they've been committed. Uncommitted changes remains invisible. This isolation level allows non-repeatable reads, and phantoms to occur.
- REPEATABLE READ (default) : ensure that is a transaction issues the same SELECT twice, it gets the same result both times, regardless of committed or uncommitted changesmade by other transactions. In other words, it gets a consistent result from different executions of the same query. In some database systems, REPEATABLE READ isolation level allows phantoms, such that if another transaction inserts new rows,in the inerbal between the SELECT statements, the second SELECT will see them. This is not true for InnoDB; phantoms do not occur for the REPEATABLE READ level.
- SERIALIZABLE : completely isolates the effects of one transaction from others. It is similar to REPEATABLE READ with the additional restriction that rows selected by one transaction cannot be changed by another until the first transaction finishes.
You can set the Transaction Isolation Level Globally, Session-Wide, or just for one Transaction:
SET GLOBAL TRANSACTION ISOLATION LEVEL isolation_level;
SET SESSION TRANSACTION ISOLATION LEVEL isolation_level;
SET TRANSACTION ISOLATION LEVEL isolation_level;
InnoDB Architecture
The basic infrastructure of InnoDB centers around three major files
- ibdata1, or System Tablespace (See
InnoDB Architecture
) - ib_logfile0 (See
InnoDB Architecture
) - ib_logfile1 (See
InnoDB Architecture
)
In conjunction with memory structures, ibdata1 processes info for 6 basic data structures
- Table Data Pages
- Table Index Pages
- Table MetaData (List of Tablespace IDs + Misc Info)
- MVCC Records
- Rollback Segments
- Undo Space
- Double Write Buffer (Allows Background Page Writes)
- Insert Buffer (For Collecting/Processing Changes to Secondary Indexes)
InnoDB Configurations can accommodate the following
- Separating Table Data and Index Pages from the System Tablespace
- Storing the System Tablespace in a Raw Disk Partition
- Creating Multiple System Tablespace Files
- Creating Multiple Log Files
- and more...
There is important cache known as the InnoDB Buffer Pool. As of MySQL 5.5, you can configure multiple buffer pool instances. Prior to MySQL 5.5, there is only one buffer pool instance.