Replication and High Availability

TideSQL stores data on one node. Replication and clustering come from the layers above the engine, and within those layers TideSQL is a first-class participant rather than a generic pass-through. It carries the binlog capabilities MariaDB replication needs, it commits through the two-phase protocol that keeps a replica crash-safe, and it participates directly in Galera certification, including the cross-node conflict resolution that keeps a multi-master cluster from diverging.

MariaDB replication

A TideSQL table replicates like any other. The engine is both row and statement binlog capable, so binlog_format of ROW, STATEMENT, or MIXED all work, with row-based replication the natural fit for an optimistic engine because it ships the resulting row images rather than re-running a statement whose outcome depends on commit ordering.

Commits go through the binlog two-phase protocol. The engine prepares durably before the binlog writes, and it commits in binlog order through the group-commit hook, so a crash between the binlog and the engine recovers to a consistent point and a replica built from that binlog is consistent with the primary. The prepare phase runs the same conflict checks as a commit, so with the binlog enabled a write-write conflict is reported when the transaction prepares. Commit behavior is covered in Transactions and Isolation.

Nothing engine-specific has to be enabled. A table created with ENGINE=TIDESDB on the primary replicates to a replica running the same engine through the standard replication configuration.

Galera cluster

TideSQL advertises the Galera replication capability, so a table created with ENGINE=TIDESDB in a wsrep-enabled server is replicated to every node in the cluster and certified in the global order the provider assigns. Galera certifies only write sets, it never fails two write sets from the same node against each other, and it certifies a transaction before the engine commits it, so the engine has to settle every conflict Galera cannot see before the transaction replicates. The sections below cover what it does for the cluster.

Certification keys

The engine appends a certification key for every row a transaction changes, built from the primary key and from each unique index, so a write to the same row or the same unique value on another node is caught as a conflict rather than silently diverging the replicas. A key with a NULL part constrains nothing and is skipped. A table with no unique key certifies each of its ordinary indexes instead, and a table with no certifiable key at all appends a table-level exclusive key, which serializes its writes across the cluster. When an update changes a key, the keys of both the before and the after image are appended, so the row’s old identity stays covered. Inserts and deletes append their keys as exclusive keys. An update appends an update key for a key that stays the same and an exclusive key for a unique key it changes, as InnoDB does, so a parent row updated in place on one node orders itself after a child row referencing it written on another, rather than conflicting with it.

A child row written under a foreign key also certifies a reference key on the parent row it depends on, the same key the parent’s delete or key change certifies as exclusive, so a parent deleted on one node and a child of it inserted on another conflict in certification. A transaction that cascades is applied on the other nodes on its own rather than in parallel with other write sets.

Conflicts between nodes

A cross-node conflict is resolved the way InnoDB resolves it, without a pessimistic lock. When an applier replays a cluster write that collides with a local transaction that has not committed, the engine brute-force aborts that local transaction through the server before it certifies, so the losing side rolls back cleanly instead of committing a write the other nodes cannot apply. A lock-free store parks no row lock for the applier to block on, so the engine keeps a small in-memory record of each uncommitted write, keyed by the row’s primary key and by each non-NULL unique value it writes, and uses it to find and abort the local loser. The applier holds those keys while it applies, so a local write to one of them meanwhile is refused. The same record covers a local transaction holding the other end of a foreign key, and a local transaction at REPEATABLE READ or SERIALIZABLE that read a key the applier writes or scanned a range holding it. An applier waits for the transactions it aborted to finish rolling back before it commits, since until then their reads would refuse its writes. A transaction at those levels that reads a row an applier has written but not yet committed is not aborted, it waits for that applier before it prepares, so the prepare finds the row newer than what it read and refuses the transaction as it would any stale read. An aborted transaction fails its next operation, its commit included, and the application on the losing side sees a deadlock error and retries, the same outcome InnoDB produces for the same race. An applier is never refused and never gives up on transient contention inside the store.

Conflicts on the same node

Two transactions on the same node never conflict in certification, so the engine settles them before either replicates. Under Galera a local write transaction is prepared before Galera certifies it, and the prepare is the engine’s own conflict check together with a durable record of the writes, so a transaction that loses to another on this node is refused before anything reaches the other nodes, and a certified transaction commits without checking again. At SNAPSHOT, which a session at the default REPEATABLE READ runs as, and at SERIALIZABLE, that check covers every write-write conflict, and the losing transaction fails at COMMIT exactly as it does without Galera. At the levels below SNAPSHOT a commit does not check its writes against other writers, so the engine refuses the losing write itself while its statement runs, as InnoDB’s row lock would, when another live transaction on the node wrote the same row or unique value, or when that row or value was committed after what the writer could have read. Without this, two writers of one row would each commit here and then replay on the other nodes as a duplicate insert or as an update of a row that is gone.

SERIALIZABLE

At SERIALIZABLE the engine validates that nothing the transaction read, and no range it scanned, changed after its snapshot. Under Galera that validation runs when the transaction prepares, before certification, so a transaction whose read went stale is refused at COMMIT exactly as without Galera, and a certified one cannot fail it afterwards. A prepared transaction’s reads refuse any write to what it read until it ends, which would refuse an applier, so the engine records the keys a local REPEATABLE READ or SERIALIZABLE transaction reads and the ranges it scans, and a cluster write to any of them aborts it and waits for it to end. Galera itself certifies only write sets, so SERIALIZABLE holds on each node, and across nodes the cluster gives snapshot isolation.

Transactions across engines

A transaction can write TidesDB and InnoDB tables together under Galera. The server certifies a transaction once for every engine taking part in its commit that carries Galera’s replication flag, and a transaction certified twice stops the node. So the engine takes part in commits without the flag, while its tables keep it, which is what makes their rows replicate. A transaction that writes only TidesDB is certified as it commits, and one that also writes InnoDB is certified once, with the keys and rows of both engines in one write set, and both engines commit it or neither does.

Commits after certification

A transaction Galera has certified is committed by every other node, so it must commit here too. The conflicts above are all refused before certification, so only a hard failure inside the store, such as an I/O error, can still fail such a commit. The node then stops with an error rather than fall out of step with the cluster or stop committing, and it rejoins through a state transfer. A large statement that runs as a bulk operation commits as a whole under Galera rather than in batches, since a batch committed before the statement’s write set was certified would stay on this node alone if the statement then failed.

Cluster position and state transfer

The cluster position is kept in a dedicated internal column family, __tidesdb_wsrep_meta, and recorded with every commit, as InnoDB records it in each commit. A transaction that already has its position when the engine commits it, an applier above all, records it in that same commit. A local transaction is prepared before it is certified and so before it has a position, so it writes a pending marker in its commit and records its position right after, and a marker left by a crash between the two makes the position unknown. The server’s own checkpoints at view changes, DDL and state transfers are recorded the same way. Every other engine reports only the position of its own last commit, and the server keeps whichever engine answers last, so when a node starts the engine works out the node’s real position and hands it to every engine through the server, and the answer is the same whichever speaks last. A node that shut down cleanly, or crashed and recovered its position through the server’s --wsrep-recover step as InnoDB does, rejoins by an incremental transfer while the write sets it is missing are still in the donor’s cache. A node whose position is unknown rejoins by a full state transfer, since replaying write sets it already holds would fail. An engine with no stored position leaves the others untouched.

A full state transfer has to carry the store, which lives outside the server’s data directory where the transfer methods do not look. While the donor holds its tables flushed and locked for the transfer, the engine writes a consistent copy of the store into #tidesdb_sst inside the data directory, which the rsync method carries with everything else, and removes it when the donor resumes. Every commit records its position, so a copy taken while the cluster is busy carries the exact position of the rows it holds. The joiner puts the copy in place as its store before the store opens. The store directory and the data directory must be on one filesystem for that last step. A transfer that arrived without a complete copy leaves TidesDB refusing to start, with an error naming the directory, rather than running without its rows. Use wsrep_sst_method=rsync, the one method that carries the store. mysqldump also works, since it recreates every table and row through SQL, given an account for the transfer in wsrep_sst_auth as with any engine. mariabackup copies only the files it knows and not the store, so the engine records an identity both in the store and in a file in the data directory, and a node whose data directory arrived without its store refuses to start TidesDB rather than run with missing or stale rows.

Auto-increment

The engine generates auto-increment values on the grid auto_increment_offset and auto_increment_increment describe, so with wsrep_auto_increment_control every node generates values no other node does.

What an application sees under conflict

On a single node, an optimistic write-write conflict surfaces at COMMIT as error 1180 wrapping handler error 149 (deadlock), or mid-statement as a deadlock (1213), and the application retries, described in Limitations. Under Galera a conflict between two transactions on the same node surfaces the same way at SNAPSHOT and SERIALIZABLE, and at the levels below SNAPSHOT as a deadlock (1213) on the losing write. Across a cluster the losing transaction of a cross-node conflict is aborted and reaches the client as a deadlock, which an application should handle by retrying the transaction. Autocommit statements are retried by the server automatically, up to wsrep_retry_autocommit times. The winner’s row stands identically on every node, so the replicas stay converged.