MySQL transaction journaling

Question

I am working on a project for which we are required to use "transaction journaling" in our DBMS (MySQL). We have already switched to using InnoDB in order to use transactions for another requirement. I am trying to understand what transaction journaling is. I have been searching for over a day now, including reading through MySQL documentation. Maybe I am just not searching for the right keywords, I am not sure. Or maybe "transaction journaling" is inappropriate terminology.

From what I understand, database transaction journaling is similar to a journaling file system in that changes are made to a journal before they are committed to the file system. From what I've read, it sounds like the InnoDB engine stores transactions in some kind of journal before they are committed to disk. Does this sound accurate? If so, where is the transaction journal? Is it ib_logfile0 and ib_logfile1?

Enrich

Answer 1

You are definitely on the right track here.

Whenever InnoDB does a transaction that has to be committed, it is done as a two-phase commit. Transaction is written in these logs first. Then, they are committed from there.

This helps a great deal in the event of the MySQL crash or server crash.

When you restart mysql, all uncommitted entries in ib_logfile0 and ib_logfile1 are replayed as part of the crash recovery of InnoDB to bring InnoDB to a harmonious state (This is Consistent and Durable parts of ACID Compliance)

If you delete ib_logfile0 and ib_logfile1 and start mysql, any uncommitted transaction that those files contained are lost. During the crash recovery cycle, if the log files are missing, they are regenerated based on the innodb_log_file_size setting.

Please see the MySQL Documentation for a detailed explanation of InnoDB.

@karatedog the MVCC part of InnoDB happens within the system tablespace, better known as ibdata1. Whatever data appear to be before the start of a transaction is recorded to allow others who access the needed rows to have a view of the data before any updates were imposed. This is allows for what is called a REPEATABLE-READ. This falls under the I of ACID compliance, I meaning Isolation. I wrote posts about this in the DBA StackExchange in regard to various scenarios where transaction isolation is good, bad, or ugly.

• read before write transaction
• Will these two queries result in a deadlock if executed in sequence?
• Is raid 5 suitable for mysql installation?

As for MyISAM, crash recovery is not automatic. It crashes rather easily. That's why the SQL command REPAIR TABLE exists. That's is also why the MySQL utility myisamchk has the -r option to perform REPAIR TABLE for MyISAM tables that are not online.

MariaDB and Aria have been attempts to make a crash-safe storage engine as a replacement for MyISAM.

Thenceforth

I understand that these log files are for crash recovery but isn't the MVCC mechanism responsible for a "transaction" to happen properly when no crash occurs? MyISAM has some degree of crash recovery while it does not have transactions at all. – Melodist

Thank you for your detailed and timely response. I am a little confused about replaying the entries in the logs. If there was a failure in the middle of a transaction, before the commit was issued, I would think you wouldn't want to replay the entries since transactions are all-or-nothing (you wouldn't want a portion of the transaction to be committed). Or are you saying they would be replayed only if there was a commit, but the failure occurred before the transaction was committed to the database, but after it was saved in the log files? – Enrich

Journal entries are only created for transactions that update or modify the database. They get deleted once the entries have been successfully committed in the database proper. It is possible to detect that a journal entry has already been committed, hence the update(s) recorded in the journal only get executed once ! – Glittery