MariaDB auto-increment after kill -9: the table counter keeps 10, the sequence jumps from 1003 to 2001
| | |

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.*

MariaDB auto-increment after kill -9: the table counter keeps 10, the sequence jumps from 1003 to 2001
MariaDB 10.11.18: the table counter survives a crash, the sequence cache does not.

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

1/5 PostgreSQL

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

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *