MariaDB AUTO_INCREMENT and sequences: what survives a restart or a crash, tested
MariaDB auto-increment versus SEQUENCE objects on 10.11: rollback and bulk-insert gaps, explicit values, cache of 1000 and what a crash costs.
Short answer: I ran kill -9 on mariadbd: MariaDB auto-increment survived (counter at 10 before and after, no gap), but a SEQUENCE object loses its cache of 1000 at every restart, clean or not: 3 became 1001, 1003 became 2001. For a plain surrogate key, use AUTO_INCREMENT. Use a sequence only if you need to share a counter, and lower its cache.
*MariaDB 10.11.18, InnoDB, innodb_autoinc_lock_mode=1, throw-away instance on Rocky Linux 10.2. Part 2 of 5. Every output comes from my own test runs.*

Two ways to get a MariaDB auto-increment value
CREATE TABLE t_auto (id INT AUTO_INCREMENT PRIMARY KEY, v VARCHAR(20)) ENGINE=InnoDB;
INSERT INTO t_auto(v) VALUES ('a');
SELECT LAST_INSERT_ID(); -- 1
INSERT INTO t_auto(v) VALUES ('b') RETURNING id; -- 2
CREATE SEQUENCE s1;
CREATE TABLE t_seq (id INT PRIMARY KEY DEFAULT NEXTVAL(s1), v VARCHAR(20)) ENGINE=InnoDB;
INSERT INTO t_seq(v) VALUES ('a') RETURNING id; -- 1
SELECT NEXTVAL(s1), LASTVAL(s1); -- 2, 2
SELECT NEXT VALUE FOR s1; -- 3
RETURNING on INSERT is available in MariaDB. NEXTVAL(s1) and NEXT VALUE FOR s1 are the same thing; LASTVAL(s1) is the last value generated in the current session.
SHOW CREATE TABLE shows where the counter lives:
`id` int(11) NOT NULL AUTO_INCREMENT, ... ) ENGINE=InnoDB AUTO_INCREMENT=3 ...
For the sequence-backed table, the default is DEFAULT nextval(shop.s1).
Rollback and failed inserts consume values
BEGIN; INSERT INTO t_auto(v) VALUES ('rolled back'); SELECT LAST_INSERT_ID(); -- 3
ROLLBACK;
INSERT INTO t_auto(v) VALUES ('after rollback'); SELECT LAST_INSERT_ID(); -- 4
With a unique violation: id 1, then ERROR 1062 (23000): Duplicate entry 'x' for key 'code', then the next insert gets 3. MariaDB auto-increment behaves like every other engine in this series, although its counter persists across restarts.
Bulk inserts leave holes of their own
With innodb_autoinc_lock_mode=1, INSERT ... SELECT of 5 rows, then one more single-row insert:
ids_after_bulk: 1,2,3,4,5 id_of_next_single_insert: 8
The bulk statement took ids 6 and 7 and never used them. I tested only the result, not the mechanism, but it is another source of gaps.
Concurrency: order of ids is not order of commits
A inserts first and commits about 4 seconds later, B inserts second and commits at once:
B got id 2 B: committed 08:20:45.327 A got id 1 A: committed 08:20:47.825
A’s line appears after B’s because of output buffering; A’s insert ran first, hence id 1, and the table ends with 1=A, 2=B. Do not use the highest id seen as a polling cursor, because commit order differs from id order.
An explicit value moves the counter
This is the main difference with PostgreSQL:
INSERT INTO t_x(id, v) VALUES (2, 'explicit 2');
INSERT INTO t_x(v) VALUES ('default 1'); -- 3
INSERT INTO t_x(v) VALUES ('default 2'); -- 4
INSERT INTO t_x(id, v) VALUES (100, 'explicit 100');
INSERT INTO t_x(v) VALUES ('after 100'); -- 101
INSERT INTO t_x(id, v) VALUES (0, 'explicit 0'); -- stored as 102
The counter always follows the highest value, so a manual id of 100 moves everything after it. Inserting 0 or NULL asks for a generated value (unless you set NO_AUTO_VALUE_ON_ZERO in sql_mode; not tested here).
Sequence CACHE is global, not per session
CREATE SEQUENCE s_cache CACHE 10; same session twice : 1 2 new session : 3 another new session: 4
Unlike PostgreSQL, sessions share the cache, so calls from different sessions get consecutive values. The cache is a server-side reservation, and it is what you lose when the server restarts (below). The default is cache 1000:
SHOW CREATE SEQUENCE s1; CREATE SEQUENCE `s1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB
Limits and CYCLE
ERROR 4084 (HY000): Sequence 'shop.s_small' has run out
Once ALTER SEQUENCE s_small CYCLE has run, the next value is 1. For an AUTO_INCREMENT on a TINYINT set to 126: rows 126 and 127, then ERROR 167 (22003): Out of range value for column 'id' at row 1.
Restart and crash
Three rows inserted, the last deleted, clean restart. For the table counter:
AUTO_INCREMENT before the restart: 4 AUTO_INCREMENT after the restart: 4 new id = 4
For a sequence with the default cache (values 1, 2, 3 consumed before):
sequence after the restart: 1001
Five more rows, the last deleted (so the counter is 10 while the highest id is 8), then kill -9:
ids before the crash: 1,2,4,5,6,7,8 AUTO_INCREMENT before the crash: 10 AUTO_INCREMENT after the crash: 10 new id = 10 sequence before the crash: 1002 1003 sequence after the crash: 2001 ids now: 1,2,4,5,6,7,8,10
The table counter is persistent across the crash (the mechanism is not tested here); there is not even a gap compared to the pre-crash state, and a deleted last id is not reused. The sequence loses the rest of its cache when the server restarts: 3 becomes 1001 after a clean restart, 1003 becomes 2001 after the crash.
Where to read the state
SELECT table_name, table_type, auto_increment FROM information_schema.tables WHERE table_schema='shop'; SELECT * FROM s1\G -- next_not_cached_value: 1001, cache_size: 1000, cycle_option: 0 ... SHOW CREATE SEQUENCE s1\G
A sequence appears in information_schema.tables with table_type = SEQUENCE; SELECT * FROM s1 also returns its parameters, including next_not_cached_value.
Official documentation
MariaDB, project site
AUTO_INCREMENT, MariaDB documentation
CREATE SEQUENCE, MariaDB documentation
Sequence overview, MariaDB documentation
*Not tested here: innodb_autoinc_lock_mode 0 and 2, NO_AUTO_VALUE_ON_ZERO, auto_increment_increment/offset (Galera and multi-master), ALTER TABLE ... AUTO_INCREMENT= on a loaded table, MySQL 8 (different persistence history).*
All parts of the series
2/5 MariaDB
3/5 Oracle (coming 24 Nov)
4/5 SQL Server (coming 1 Dec)
5/5 TL;DR: the four engines compared (coming 8 Dec)
Related articles
Inside database data files (1/3): InnoDB, from MySQL to MariaDB
Inside database data files (2/3): PostgreSQL
Inside database data files (3/3): Oracle, and all three side by side