Understanding MySQL Write‑Set Based Parallel Replication and Last‑Commit Handling

The article introduces MySQL's write‑set based parallel replication, which aims to reduce the last_commit value compared to the traditional COMMIT_ORDER method, thereby achieving better parallel replay on replica servers.

To enable this mode, the master must be configured with two parameters introduced in MySQL 5.7.22:

transaction_write_set_extraction=XXHASH64

binlog_transaction_dependency_tracking=WRITESET

Writeset definition : It is a std::set<uint64> that stores hash values of each row modified in a transaction. The hash is derived from the primary key or unique key of the row, using the algorithm specified by transaction_write_set_extraction .

Each row contributes two possible formats:

Binary format of the key value

String format of the key value

After InnoDB modifies a row, the formatted data is hashed and inserted into the writeset. The pseudo‑code for this process is represented by the function add_pke , which iterates over table indexes, extracts key values, and writes them into the writeset.

Writeset history map stores the latest transaction sequence number for each hash value, enabling conflict detection across transactions. It is defined as:

typedef std::map<uint64,int64> Writeset_history; // map implementation
Writeset_history m_writeset_history;

The map is ordered by hash values and may be cleared when its size exceeds the limit set by binlog_transaction_dependency_history_size (default 25000). When the capacity is exceeded, the oldest sequence number is recorded in m_writeset_history_start and the map is cleared.

The core algorithm that computes the new last_commit resides in Writeset_trx_dependency_tracker::get_dependency . A simplified version of the loop is:

int64 last_parent = m_writeset_history_start;
for (std::set<uint64>::iterator it = writeset->begin(); it != writeset->end(); ++it) {
    Writeset_history::iterator hst = m_writeset_history.find(*it);
    if (hst != m_writeset_history.end()) {
        if (hst->second > last_parent && hst->second < sequence_number)
            last_parent = hst->second;
        hst->second = sequence_number; // update seq number for the hash
    } else {
        if (!exceeds_capacity)
            m_writeset_history.insert(std::pair<uint64, int64>(*it, sequence_number));
    }
}
commit_parent = std::min(last_parent, commit_parent);

Three dependency‑tracking modes are supported:

ORDER_COMMIT : uses only the original commit order.

WRITESET : invokes m_writeset.get_dependency after the order‑based calculation, adjusting last_commit based on writeset conflicts.

WRITESET_SESSION : additionally considers the previous transaction in the same session, preventing parallel replay of transactions from the same session.

Example MySQL session to view table definition:

mysql> use test;
Database changed
mysql> show create table jj10 \G
*************************** 1. row ***************************
Table: jj10
Create Table: CREATE TABLE `jj10` (
  `id1` int(11) DEFAULT NULL,
  `id2` int(11) DEFAULT NULL,
  `id3` int(11) NOT NULL,
  PRIMARY KEY (`id3`),
  UNIQUE KEY `id1` (`id1`),
  KEY `id2` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Inserting a row generates four hash elements, e.g.:

$1 = "PRIMARY?test?4jj10?4\200\000\000$?4"
$2 = "PRIMARY?test?4jj10?436?2"
$3 = "id1?test?4jj10?4\200\000\000$?4"
$4 = "id1?test?4jj10?436?2"

If a table lacks primary or unique keys, the writeset cannot be built, and the system falls back to the original ORDER_COMMIT behavior.

Drawbacks of the writeset approach include the need to compare every hash against the history map, additional memory consumption for the writeset itself and its historical map, and the overhead of maintaining the map size.

Overall, the write‑set based parallel replication can significantly reduce last_commit and improve replica parallelism, but it should be used only when the memory cost is acceptable and other latency‑reduction techniques have been considered.