Aria Architecture, Crash Safety, Temporary Work, and Operational Use Cases

Learning outcomes

ServiceHub needs a small read-heavy lookup table for diagnostic codes and an internal report occasionally spills a temporary result to disk. A developer sees Aria in SHOW ENGINES and assumes it is “InnoDB, but lighter.” That assumption is dangerous. MariaDB uses a pluggable storage-engine architecture, and Aria deliberately offers different transaction, locking, cache and recovery semantics from InnoDB.

Aria is MariaDB’s crash-safe successor to many MyISAM-style use cases. Its default PAGE row format can be crash-safe through the Aria transaction log, and MariaDB commonly uses Aria for internal on-disk temporary tables. But crash-safe is not the same as fully transactional: an Aria user table does not acquire InnoDB-style MVCC, row-level locking, foreign-key enforcement or rollback semantics merely because TRANSACTIONAL=1 appears in its DDL.

1. Ask the server what Aria actually promises

SELECT VERSION() AS server_version;SHOW ENGINES;SELECT ENGINE,SUPPORT,TRANSACTIONS,XA,SAVEPOINTS,COMMENTFROM information_schema.ENGINESWHERE ENGINE IN ('InnoDB','Aria','MyISAM','MEMORY','CONNECT')ORDER BY ENGINE;

The TRANSACTIONS, XA and SAVEPOINTS columns are a better correctness starting point than an engine description. A supported engine can still report no transaction support. Availability means the server can create or open tables with that engine; it does not certify that the engine meets your application’s atomicity, isolation, recovery, backup or Galera requirements.

SHOW VARIABLES LIKE 'aria_used_for_temp_tables';SHOW VARIABLES LIKE 'default_storage_engine';SHOW VARIABLES LIKE 'default_tmp_storage_engine';

On standard builds, aria_used_for_temp_tables is commonly ON, so internal on-disk temporary tables can use Aria while ordinary user tables still default to InnoDB. This distinction matters: the optimizer choosing Aria for an internal intermediate result does not imply you should convert OLTP business tables to Aria.

2. Build two Aria tables and inspect the row-format/crash-safety contract

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 diagnostic_codes (  code VARCHAR(16) NOT NULL PRIMARY KEY,  severity TINYINT NOT NULL,  description VARCHAR(200) NOT NULL) ENGINE=Aria ROW_FORMAT=PAGE TRANSACTIONAL=1;CREATE TABLE scratch_nonlogged (  id INT NOT NULL PRIMARY KEY,  note VARCHAR(100) NOT NULL) ENGINE=Aria ROW_FORMAT=DYNAMIC TRANSACTIONAL=0;SHOW CREATE TABLE diagnostic_codes\GSHOW CREATE TABLE scratch_nonlogged\GSHOW TABLE STATUS LIKE 'diagnostic_codes'\GSHOW TABLE STATUS LIKE 'scratch_nonlogged'\G

For Aria, PAGE is the row format associated with crash-safe logging. Setting TRANSACTIONAL=1 forces PAGE behavior and causes changes to be recorded in Aria’s log so completed statements can be recovered after a crash. TRANSACTIONAL=0 allows non-crash-safe behavior and formats that resemble MyISAM. The name is historically confusing: the option does not turn Aria into an InnoDB-equivalent transaction engine.

3. Deliberately wrong: treat TRANSACTIONAL=1 as rollback support

INSERT INTO diagnostic_codes VALUES ('NET-101',2,'Packet-loss threshold exceeded');START TRANSACTION;UPDATE diagnostic_codesSET severity=5WHERE code='NET-101';ROLLBACK;SHOW WARNINGS;SELECT * FROM diagnostic_codes WHERE code='NET-101';

The important observation is that the Aria change is not undone like an InnoDB change. Depending on the statement mix, MariaDB can emit warning 1196 stating that changes to non-transactional tables could not be rolled back. This is the clearest repair to the mental model: Aria crash logging protects recoverability of completed statements; it is not application transaction rollback.

If ServiceHub requires “update work order + insert audit row or neither happens,” put both correctness-critical rows in a transaction-capable engine such as InnoDB. Aria can still be appropriate for rebuildable lookup/reporting data whose correctness contract tolerates table-level locking and non-transactional writes.

4. Aria locking and concurrency

Aria belongs to the family of MariaDB engines that use table-level locking for ordinary conflicting writes. A single long writer can therefore affect unrelated rows in the same table. This can be perfectly acceptable for small read-heavy reference data, but it is a different concurrency model from InnoDB record/gap locks and MVCC.

Session A:LOCK TABLES diagnostic_codes WRITE;UPDATE diagnostic_codes SET severity=3 WHERE code='NET-101';-- keep the lock for a few secondsSession B:SELECT * FROM diagnostic_codes WHERE code='NET-101';-- observe that it waits behind the explicit write lockSession A:UNLOCK TABLES;Session B:-- the SELECT completes

Explicit LOCK TABLES is used only to make the table-level boundary obvious in a disposable lab. Production code should not add table locks merely because the engine already uses coarse internal locking. Measure concurrency requirements and choose the engine whose native model fits them.

5. Internal temporary work is a different use case from business durability

Complex GROUP BY, DISTINCT, sorting and materialization can create internal temporary tables. MariaDB can start them in memory and move them to on-disk Aria when size/type rules require it. The status counters Created_tmp_tables and Created_tmp_disk_tables help you observe this behavior at workload level.

SHOW SESSION STATUS LIKE 'Created_tmp%';SELECT severity, COUNT(*) AS code_countFROM diagnostic_codesGROUP BY severityORDER BY code_count DESC;SHOW SESSION STATUS LIKE 'Created_tmp%';

A counter increase does not prove that Aria itself caused a performance problem, and a disk temporary table is not automatically bad. It is evidence that the query crossed an execution boundary worth correlating with data volume, plan shape, memory limits and latency.

6. Health and recovery tooling are engine-specific

CHECK TABLE diagnostic_codes;CHECK TABLE scratch_nonlogged;

Aria has dedicated recovery/check tooling such as aria_chk, while server-level tools such as mariadb-check can check supported tables. Offline repair utilities require exact engine/file knowledge and normally require the server not to be using the table. Never copy an InnoDB recovery runbook onto Aria or vice versa.

7. Verification checklist and cleanup

1. Confirm Aria support and its TRANSACTIONS/XA/SAVEPOINTS flags.
2. Confirm aria_used_for_temp_tables on the actual server.
3. Create PAGE/TRANSACTIONAL=1 and DYNAMIC/TRANSACTIONAL=0 tables; inspect SHOW CREATE/TABLE STATUS.
4. Run the ROLLBACK experiment and explain the surviving change.
5. Run CHECK TABLE and record the output.
6. Keep the database for Lesson 2 or clean up with DROP DATABASE servicehub_engines_lab;.

Next, MyISAM shows the legacy version of this boundary: simple files, table locks and FULLTEXT history without transactions or foreign keys.

8. Crash-safe is a recovery property, not an application isolation level

It helps to separate three questions that are often compressed into the word “safe.” First, statement recovery asks whether a completed change can be reconstructed after an abnormal server stop. Aria PAGE tables with crash-safe logging address this class of problem. Second, transaction atomicity asks whether several statements can be committed or rolled back as one business unit. Aria user tables do not provide the same rollback contract as InnoDB. Third, concurrency isolation asks what one session can observe while another session modifies data. Aria’s table-oriented locking model is not InnoDB MVCC.

This separation prevents a subtle production mistake: a system may restart cleanly after a crash and still have application-level inconsistencies because a multi-table business transaction touched a non-transactional engine. Recovery successfully preserves what the engine considered completed; it cannot infer the application’s intended all-or-nothing boundary.

For an operational review, therefore, record Aria’s row format and crash-safe mode together with the business invariant that owns the table. If the invariant requires rollback across multiple writes, the engine decision has already failed before performance tuning begins.

Authoritative references

• MariaDB Documentation — Aria Storage Engine
• MariaDB Documentation — Aria Storage Formats
• MariaDB Documentation — Aria System Variables
• MariaDB Documentation — SHOW ENGINES