InnoDB REDO log

this is “usual” transactional log as we know it on other databases – but only for InnoDB engine “innodb_log_file_size” – defines size, default is 48MB “innodb_log_files_in_group” – defines number of files, default...

Binary log

This log is not transactional log as we know it on other databases Contains info about all operations with impact on database content is independent on engines, stores all operations modifying data, also for engines without transactions is optional, must be turned on...

Isolation Levels

Standard defines 4 levels, InnoDB implements all of them: read uncommited read commited repeatable read (default) serializable But different levels have different impact on performance, mainly because of locks. In reality read uncommited level is implemented badly or...

Multiversion Concurrency Control

InnoDB does not use simple row locking mechanism. It stores old versions of changed rows in “rollback segment” or “undo space” (like Oracle) MVCC allows to avoid row locks in some cases + have lower overhead. Attention – different...

Transactions

There are two default MySQL storage engines which use transactions – InnoDB and NDB Cluster. Also some third-party engines have transactions – like XtraDB or PBXT MySQL uses AUTOCOMMIT mode by default (SHOW VARIABLES LIKE ‘AUTOCOMMIT’). This...