Shulou(Shulou.com)05/31 Report--

This article focuses on "how to understand the two-stage commit of MySQL transactions". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to understand the two-stage commit of MySQL transactions.

Purpose of ⒈ two-stage submission: to address the consistency of participants (redo log & binlog)

The principle of ⒉ two-stage submission: it actually prevents partial submission by participants (resource managers) (in binlog and redo log, if redo log is ready before submission, but binlog is not ready, binlog may fail to commit directly; if binlog is ready, redo log is not ready, it will result in submission loss)

The two phases of ⒊ two-stage commit:

The first stage is the prepare phase, in which the TM (transaction manager) issues prepare instructions to the RM (resource manager), RM operates, and then returns the success information to TM.

The second stage: for the transaction commit or rollback phase, if TM receives all RM success messages, TM issues a commit instruction to RM; otherwise, a rollback instruction is issued

Evolution of ⒋ Binlog/Redo XA in the MySQL version:

⑴ version 5.5:

The first phase of ① (innodb prepare)

Hold prepare_commit_mutex

Change the undo state to the prepare state

Return redo write/fsync to disk

Binlog doesn't do anything.

② Phase II (commit)

Write/sync_binlog

Innodb_commit (release prepare_commit_mutex mutexes after writing the COMMIT flag)

A concurrency flaw here is the mutex prepare_commit_mutex, which runs through the two stages of submission.

⑵ version 5.6:

The first phase of ① (innodb prepare)

Hold prepare_commit_mutex

Change the undo state to the prepare state

Return redo write/fsync to disk

Binlog doesn't do anything.

Release prepare_commit_mutex

② Phase II (commit)

Divided into three queues, divided into three small stages (each stage is a queue, each queue is protected by a mutex, and there is a leader transaction leading operation)

Flush phase: leader writes the binlog of a group of things to IOcache

Sync phase: change the binlog sync disk

Commit phase: submit according to the parameter binlog_order_commits

The advantage of being divided into three stages is that the previous mutex is split and concurrency is increased.

But redo log still cannot be submitted in groups.

⑶ version 5.7:

The first phase of ① (innodb prepare)

Hold prepare_commit_mutex

Change the undo state to the prepare state

Record lsn to lsn of thd data structure

Binlog does nothing (here, if GTID is enabled, get the starting logical clock of lock_interval, which is used for MTS playback)

Release prepare_commit_mutex

② Phase II (submission phase)

Divided into three queues, divided into three small phases (each stage is a queue, each queue is protected by a mutex, and is operated by the leader transaction thread)

⑴ flush stage phase:

The leader transaction thread collects the flush queue, finds the largest LSN, and then disks the redo log write/flush to the largest LSN

Write binlog, writing binlog to the IO cache

⑵ sync phase:

Brush binlog into disk

⑶ commit phase:

Submit according to the setting of parameter binlog_order_commits

Advantages: change the write/sync of the historical redo in the prepare phase to flush state, so that the group submission of redo can be carried out.

⒌ Binlog/Redo XA in the parameter adjustment:

Flush phase:

Previous versions of binlog_max_flush_queue_time:5.7.9 are available, waiting time for flush queues

Sync phase:

Binlog_group_commit_sync_delay: the time to wait before entering the sync phase

Binlog_group_commit_sync_no_delay_count:binlog_group_commit_sync_delay milliseconds until binlog_group_commit_sync_no_delay_count transactions are collected, make a group commit

Commit phase:

Binlog_order_commits: controls whether the binlog is submitted sequentially

At this point, I believe you have a deeper understanding of "how to understand the two-stage commit of MySQL transactions". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.