Choose a Storage Engine Deliberately and Avoid Cross-Engine Transaction Assumptions

Learning outcomes

ServiceHub now has five engine choices on one server. SQL lets a transaction touch more than one table regardless of engine, which creates a dangerous illusion: the transaction syntax is uniform, but the guarantees underneath are not. A rollback can revert InnoDB while leaving an Aria/MyISAM/MEMORY/CONNECT change outside the same atomicity boundary.

1. Start with guarantees, not engine names

This matrix is intentionally qualitative. Exact capabilities evolve by version and optional plugin. Always confirm the actual server using SHOW ENGINES, INFORMATION_SCHEMA.ENGINES and engine documentation before deployment.

2. Build the mixed-engine rollback experiment

DROP DATABASE IF EXISTS servicehub_engines_lab;CREATE DATABASE servicehub_engines_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub_engines_lab;CREATE TABLE technicians (  technician_id BIGINT NOT NULL PRIMARY KEY,  display_name VARCHAR(80) NOT NULL,  active BOOLEAN NOT NULL DEFAULT TRUE) ENGINE=InnoDB;INSERT INTO technicians VALUES (1,'Ava Chen',1),(2,'Mina Patel',1),(3,'Noah Smith',1);CREATE TABLE account_balance_innodb (  account_id BIGINT NOT NULL PRIMARY KEY,  balance DECIMAL(12,2) NOT NULL) ENGINE=InnoDB;CREATE TABLE account_balance_myisam (  account_id BIGINT NOT NULL PRIMARY KEY,  balance DECIMAL(12,2) NOT NULL) ENGINE=MyISAM;INSERT INTO account_balance_innodb VALUES (10,100.00);INSERT INTO account_balance_myisam VALUES (10,100.00);START TRANSACTION;UPDATE account_balance_innodb SET balance=balance-25 WHERE account_id=10;UPDATE account_balance_myisam SET balance=balance-25 WHERE account_id=10;ROLLBACK;SHOW WARNINGS;SELECT 'InnoDB' AS engine,balance FROM account_balance_innodb WHERE account_id=10UNION ALLSELECT 'MyISAM',balance FROM account_balance_myisam WHERE account_id=10;

The expected outcome exposes the correctness boundary: the InnoDB row returns to 100.00 while the MyISAM row remains 75.00, with MariaDB warning that non-transactional changes could not be rolled back. SQL transaction syntax coordinated session state and metadata locks, but it could not add rollback capability to a non-transactional storage engine.

3. Why cross-engine transactions are operationally expensive even when they are legal

MariaDB can coordinate work involving different engines, and binary logging can require extra coordination such as two-phase commit ordering when transactional and non-transactional changes mix. That machinery helps logging consistency; it does not upgrade the weaker engine’s rollback semantics. Application developers must still design around the weakest guarantee in the unit of work.

A useful rule is to keep one business invariant inside one well-understood transactional boundary. If a durable business operation requires atomic changes across tables, those tables should normally use engines that provide the needed transaction semantics. External systems should be integrated through explicit messaging/outbox/idempotency patterns rather than hidden behind a table abstraction and assumed atomic.

4. Backup and recovery must be evaluated per engine

This is why “mariadb-dump completed successfully” or “mariadb-backup finished” is not the end of backup design. The runbook must state which engines exist, which external files/sources are outside the backup, what locks/snapshots were used and how the restored service is verified.

5. Replication and Galera are engine-specific too

Binary-log replication can carry statements/row events involving multiple engines, but recovery and concurrency semantics at each node still depend on the destination engine. For Galera, current MariaDB guidance treats InnoDB as the fully supported storage engine for replicated data. MyISAM and Aria replication modes are explicitly experimental and should not be relied upon as normal production guarantees.

SHOW VARIABLES LIKE 'wsrep_on';SHOW VARIABLES LIKE 'wsrep_mode';SHOW VARIABLES LIKE 'default_storage_engine';

A standalone server may show no active wsrep provider or wsrep_on=OFF; that is expected. The purpose is to establish that “table works on standalone MariaDB” is not proof it belongs in a Galera workload. Chapter 15 will test clustering separately with the exact provider/topology.

6. Decision matrix for ServiceHub

The word “may” is deliberate. Engine choice is a requirements decision, not a ranking. Even InnoDB can be the wrong tool for analytics/federation if another engine or system better matches the workload, but leaving InnoDB should be an explicit architectural decision with a tested failure model.

7. Deliberately wrong: choose per-table engines from a benchmark spreadsheet

Selecting the fastest engine for each table can create a system whose transaction, backup and HA boundaries nobody can explain. A 5% synthetic read improvement is not worth partial rollback of a financial operation or an un-restorable external dependency. The repair is an engine approval record that includes correctness first and performance only after the contract is acceptable.

8. Chapter lab: prove the weakest guarantee and clean up

1. Run the mixed InnoDB/MyISAM rollback experiment and save the result.
2. Query INFORMATION_SCHEMA.ENGINES for all engines available on your server.
3. For each available non-InnoDB engine, write one durability/locking/backup limitation.
4. If CONNECT was installed, document its external file/source ownership and uninstall only if your disposable-lab plan calls for it.
5. Drop the lab with DROP DATABASE servicehub_engines_lab;.

Chapter 08 completes the storage-engine decision layer. Chapter 09 returns to access paths—B-tree, generated/expression patterns, FULLTEXT, spatial and vector indexing—while preserving the same rule: an index or engine is useful only when its semantics and observed workload behavior justify it.

9. Observability must expose engine boundaries before an incident

A heterogeneous server should make engine choice visible in routine inventory, not discover it during recovery. Periodically query information_schema.TABLES by engine and alert on unexpected changes. Schema-review tooling can reject a new non-InnoDB table unless an architecture record names its owner, durability contract, backup method and HA implications. This is especially important because an accidental ENGINE=MyISAM or ENGINE=MEMORY can look perfectly healthy in ordinary CRUD tests.

Incident runbooks should also identify engine-specific diagnostics: InnoDB status/transaction views, Aria/MyISAM check/repair tools, MEMORY rebuild sources, and CONNECT’s external dependency owner. The operational goal is not to standardize every engine; it is to make the different failure models impossible to overlook.

Authoritative references

• MariaDB Documentation — Storage Engines Overview
• MariaDB Documentation — Choosing the Right Storage Engine
• MariaDB Documentation — Error 1196
• MariaDB Documentation — START TRANSACTION
• MariaDB Documentation — Galera Known Limitations