How MySQL Commits and Recovers

Following a previous post on Postgres recovery(for fun), I decided to do the same for MySQL, this time tracing the commit path as well and pointing to the relevant source locations. Both systems guarantee durability and recovery but they differ in how they implement it. Postgres has a write-ahead log (WAL) and a transaction's changes are flushed to it before the commit is acknowledged to the user. InnoDB, MySQL's default storage engine, calls its write-ahead log the redo log and it gives the same guarantee: a transaction's redo is flushed to disk before the commit is acknowledged. When the binary log is enabled, MySQL does this in a two-phase commit (2PC) fashion. In the first phase(prepare), InnoDB marks the transaction as prepared and its redo is flushed. Next, the transaction is written to the binlog and fsynced. Finally, InnoDB commits and the client is acknowledged.

Postgres Recovery on Startup

During startup, Postgres checks the current state of the database stored on disk on the path $PGDATA/global/pg_control. If the database was shutdown cleanly(DB_SHUTDOWNED state) it skips recovery(See StartupXLOG → InitWalRecovery (xlogrecovery.c, PG17)).

If we had earlier crashed or a signal file(recovery.signal / standby.signal) or backup label is detected, the recovery process is initiated until the database is brought to a consistent state. A secondary check involving the positions of the redo and checkpoint positions is also performed: When the two positions are equal, Postgres looks at the state state != DB_SHUTDOWNED and if true recovery can proceed. If redo position is beyond the checkpoint position, the process panics and startup halts. If the redo record falls behind checkpoint position, the recovery process proceeds(however, if the checkpoint record is a shutdown record, the startup process panics. A clean shutdown should always write to disk a valid redo record.). Finally, matching record positions and a clean shutdown with no signal file or backup label skips the recovery process.

MySQL Recovery

In MySQL's case we'll track how MySQL achieves durability: what happens when a query is issued up to when it is committed. Then we'll detail the process of recovery. The post assumes Innodb as the storage engine and log_bin is set. It also only covers the case for internal XA transactions.

Query handling

When a query is issued from a client connection the normal query dispatch code path is followed(from versions 8.0): handle_connection-> do_command -> dispatch_command -> dispatch_sql_command -> mysql_execute_command. Things get interesting from here on. The mysql_execute_command function contains a large switch statement for dispatching various SQL commands(SELECT, CREATE, ALTER, DROP etc) to their specific handlers located in sql/sql_<command>.cc files. For example, a simple insert query would normally follow Sql_cmd_dml::execute -> Sql_cmd_insert_values::execute_inner methods in sql/sql_select.cc and sql/sql_insert.cc respectively. This path ultimately does the write(write_record -> handler::ha_write_row -> ha_innobase::write_row) to the in-memory page buffer and writes the undo record too. The redo is written to the log buffer when each mini-transaction commits.

The query is later written to the in-memory binlog cache(binlog_cache_data buffers a transaction's binlog events until they are written to the binary log at commit. The data is held by Binlog_cache_storage which keeps it in memory up to binlog_cache_size and spills to a temporary file if the transaction exceeds that) by thd->binlog_query for statement format. Row format is handled in in the handler call itself: handler::ha_write_row calls binlog_log_row, which adds a Write_rows_log_event to the binlog cache. In this mode thd->binlog_query only flushes the pending rows event and writes no query text.

Two-Phase Commit(2pc)

MySQL has a feature for XA transactions. Basically it allows for distributed transactions processing in MySQL. It is based on X/Open CAE document. Simply put, it defines a resource manager and transaction manager. The resource manager handles storage resources while the transaction manager controls transaction processing in the various storage engines. The transaction manager issues commit/rollback to all of the storage engines involved in the transaction. In this manner, either the transaction succeeds or is aborted for all storage engines. 2PC is only available if there are more than one storage engine participating in a transaction performing modifications to tables. Read-only transactions skip it.

For internal XA, at least two storage engines are involved in the transaction at a minimum. The binlog acts as a storage engine, as well as the transaction manager. The resource owner(Innodb) is the other storage engine. With the conditions for 2PC met, transaction commit involves 2 phases: a prepare phase and a commit phase.

Prepare Phase

In this phase the transaction's undo logs are marked as prepared. The call graph is something like this mysql_execute_command -> trans_commit_stmt -> ha_commit_trans -> MYSQL_BIN_LOG::prepare -> ha_prepare_low -> innobase_xa_prepare. In a mini-transaction, InnoDB sets the undo segment state to TRX_UNDO_PREPARED and writes the XID into the undo log header. Then transitions the transaction (trx_t) to TRX_STATE_PREPARED. The redo generated by these changes is flushed to storage according to innodb_flush_log_at_trx_commit, unless the binlog is enabled, in which case the flush is deferred to the flush stage of binlog group commit.

If the prepare phase completes successfully i.e trx->state == TRX_STATE_PREPARED, the transaction can only be committed or rolled back going forward. After a crash, transactions in active state can be automatically rolled back during recovery. Transactions in prepared state are explicitly checked against the binlog. If there is an XID match MySQL(using the binlog as the coordinator) commits the transaction, it's rolled back otherwise.

Commit Phase

This phase is where changes generated by the query transaction are stored durably to permanent storage. The call graph is something like this mysql_execute_command -> trans_commit_stmt -> ha_commit_trans -> MYSQL_BIN_LOG::commit -> ordered_commit. The doc on ordered_commit method clearly provide details on the commit process and the 4 stages involved. We cover only stages 1-3.

Stage 1 involves flushing the Innodb redo logs, from the earlier call to write_row. The redo flush deferred from prepare happens here and ha_flush_logs is called at this point. Next, GTIDs are generated for all queued transactions. Data stored in binlog session caches are then flushed to the binary log.

In stage 2, based on the value of sync_binlog, fsync is called on the binlog to force writes to disk.

Stage 3 does the actual commit by calling ha_commit_low. All sessions data in the storage engines that are part of the transaction are committed in this stage by these calls to process_commit_stage_queue -> finish_transaction_in_engines -> ha_commit_low. The transaction is completed when the mini-transaction(mtr) is committed: Call to mtr_commit which copies data from the mtr log into the shared log buffer . The dirtied pages are then added to the flush list in add_dirty_blocks_to_flush_list and the final commit lsn is generated.

Recovery

MySQL always runs recovery at every server start. This can however be controlled by srv_force_recovery which maps to the user-facing setting innodb_force_recovery. A value equal to or greater than 4 puts the server in read-only mode and table modifications are not allowed. These are only intended to help a user try to get a corrupted database up so that they are able to dump tables data for use outside the damaged server. The options are:

Constant	Value	Effect
SRV_FORCE_IGNORE_CORRUPT	1	Ignore corrupt pages during startup
SRV_FORCE_NO_BACKGROUND	2	Prevent main and purge threads from running
SRV_FORCE_NO_TRX_UNDO	3	Prevent transaction rollback after recovery
SRV_FORCE_NO_IBUF_MERGE	4	Prevents insert(change) buffer merge operations from running. It also stops InnoDB from calculating table statistics. Might corrupt secondary indexes
SRV_FORCE_NO_UNDO_LOG_SCAN	5	Do not look at undo logs when starting the database: InnoDB will treat even incomplete transactions as committed
SRV_FORCE_NO_LOG_REDO	6	Don't roll-forward the log in recovery. Potentially leaves the database in an inconsistent state

A value of zero lets recovery run the normal way and prevents database corruption.

The offline MySQL tool innodb_controldata I made can be helpful for displaying the current state of the database server.

MySQL startup begins in sql/mysqld.cc and the function init_server_components is called to initialize different parts of the server. The recovery process is started as part of the data dictionary initialization. The startup path is usually

init_server_components -> dd::init -> Dictionary_impl::init -> bootstrap::DDSE_dict_init -> innobase_ddse_dict_init -> innobase_init_files -> srv_start

Recovery begins when recv_recovery_from_checkpoint_start is called. Redo is skipped when roll-forward is turned off i.e innodb_force_recovery == SRV_FORCE_NO_LOG_REDO. The process reads and compares the two checkpoint header slots and picks the one with the highest checkpoint number. If there are more records beyond this checkpoint record in the redo log, they are written to pages in the buffer pool which will then be flushed to disk later. The initialization process is aborted if the last LSN to be recovered is still less than the checkpoint record's LSN, even after scanning to the end of the log.

Once the redo log recovery is complete, binlog recovery follows. See MYSQL_BIN_LOG::open_binlog -> binlog::Binlog_recovery::recover -> ha_recover -> (recover_prepared_in_tc_one_ht, recover_one_ht) -> (innobase_xa_recover_prepared_in_tc, innobase_xa_recover). In this case, for internal XA, XIDs of transactions that were completely written to the binlog before a system crash are collected in a list and sent to the storage engines. The server via innobase_xa_recover gets a list of prepared transactions from the storage engine(Innodb) and checks this against the XIDs list from the binlog. If any of Innodb's prepared transactions appears in the XID list they are committed and rolled back otherwise.