Menu
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?
339
The transaction log in MySQL is not enabled by default and must be enabled in order to log transactions. To determine if the transaction log is active you can use the “show binary logs” statement: SHOW BINARY LOGS; If binary logging is disabled you will receive an error stating “you are not using binary logging”.
Begin transaction by issuing the SQL command BEGIN WORK. Issue one or more SQL commands like SELECT, INSERT, UPDATE or DELETE. Check if there is no error and everything is according to your requirement. If there is any error, then issue a ROLLBACK command, otherwise issue a COMMIT command.
You can use show innodb status (or show engine innodb status for newer versions of mysql) to get a list of all the actions currently pending inside the InnoDB engine. Buried in the wall of output will be the transactions, and what internal process ID they're running under.
A transaction in MySQL is a sequential group of statements, queries, or operations such as select, insert, update or delete to perform as a one single work unit that can be committed or rolled back.
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.
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.
199
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
