En bref : j’ai fait un kill -9 sur mariadbd : l’auto-increment MariaDB a survécu (compteur à 10 avant et après, sans trou), mais un objet SEQUENCE perd son cache de 1000 à chaque redémarrage, propre ou non : 3 devient 1001, 1003 devient 2001. Pour une clé de substitution simple, prendre AUTO_INCREMENT. Ne prendre une séquence que pour partager un compteur, en réduisant son cache.
*MariaDB 10.11.18, InnoDB, innodb_autoinc_lock_mode=1, instance jetable sous Rocky Linux 10.2. Partie 2 sur 5. Toutes les sorties viennent de mes propres tests.*

Deux façons d’obtenir un auto-increment MariaDB
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 sur INSERT existe dans MariaDB. NEXTVAL(s1) et NEXT VALUE FOR s1 sont équivalents ; LASTVAL(s1) donne la dernière valeur générée dans la session courante.
SHOW CREATE TABLE montre où vit le compteur :
`id` int(11) NOT NULL AUTO_INCREMENT, ... ) ENGINE=InnoDB AUTO_INCREMENT=3 ...
Pour la table adossée à la séquence, le défaut est DEFAULT nextval(shop.s1).
Rollback et échecs consomment des valeurs
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
Avec une violation d’unicité : id 1, puis ERROR 1062 (23000): Duplicate entry 'x' for key 'code', puis l’insertion suivante reçoit 3. L’auto-increment MariaDB se comporte comme les autres moteurs de cette série.
Les insertions en masse font leurs propres trous
Avec innodb_autoinc_lock_mode=1, un INSERT ... SELECT de 5 lignes, puis une insertion d’une seule ligne :
ids_after_bulk: 1,2,3,4,5 id_of_next_single_insert: 8
Les id 6 et 7 ont été alloués à l’instruction en masse et jamais utilisés. Seul le résultat est testé ici, pas le mécanisme d’allocation ; c’est simplement une source de trous de plus.
Concurrence : l’ordre des id n’est pas l’ordre des commits
A insère en premier et valide environ 4 secondes plus tard, B insère en second et valide aussitôt :
B got id 2 B: committed 08:20:45.327 A got id 1 A: committed 08:20:47.825
La ligne de A s’affiche après celle de B à cause de la mise en tampon des sorties ; l’insertion de A est passée en premier, d’où l’id 1, et la table finit avec 1=A, 2=B. Ne pas utiliser le plus grand id vu comme curseur de polling.
Une valeur explicite fait avancer le compteur
C’est la différence principale avec 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'); -- stocké comme 102
Le compteur suit toujours la plus grande valeur. Insérer 0 ou NULL demande une valeur générée (sauf si NO_AUTO_VALUE_ON_ZERO est dans sql_mode ; non testé ici).
Le CACHE d’une séquence est global, pas par session
CREATE SEQUENCE s_cache CACHE 10; same session twice : 1 2 new session : 3 another new session: 4
Contrairement à PostgreSQL, les sessions partagent le cache ; des appels successifs depuis des sessions différentes sont consécutifs. Le cache est une réservation côté serveur, et c’est lui qui est perdu au redémarrage (ci-dessous). Le défaut est cache 1000 :
SHOW CREATE SEQUENCE s1; CREATE SEQUENCE `s1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB
Limites et CYCLE
ERROR 4084 (HY000): Sequence 'shop.s_small' has run out
Après ALTER SEQUENCE s_small CYCLE, la valeur suivante est 1. Pour un AUTO_INCREMENT sur TINYINT positionné à 126 : lignes 126 et 127, puis ERROR 167 (22003): Out of range value for column 'id' at row 1.
Redémarrage et crash
Trois lignes insérées, la dernière supprimée, redémarrage propre. Pour le compteur de table :
AUTO_INCREMENT before the restart: 4 AUTO_INCREMENT after the restart: 4 new id = 4
Pour une séquence au cache par défaut (valeurs 1, 2, 3 consommées avant) :
sequence after the restart: 1001
Cinq lignes de plus, la dernière supprimée (le compteur vaut donc 10 alors que le plus grand id est 8), puis 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
Le compteur de table est persistant à travers le crash (le mécanisme n’est pas testé ici) ; il n’y a même pas de trou par rapport à l’état d’avant le crash, et un dernier id supprimé n’est pas réutilisé. La séquence perd à chaque fois le reste de son cache : 3 devient 1001 après un redémarrage propre, 1003 devient 2001 après le crash.
Où lire l’état
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
Une séquence apparaît dans information_schema.tables avec table_type = SEQUENCE ; SELECT * FROM s1 renvoie ses paramètres, dont next_not_cached_value.
Documentation officielle
MariaDB, site du projet
AUTO_INCREMENT, documentation MariaDB
CREATE SEQUENCE, documentation MariaDB
Sequence overview, documentation MariaDB
*Non testé ici : innodb_autoinc_lock_mode 0 et 2, NO_AUTO_VALUE_ON_ZERO, auto_increment_increment/offset (Galera et multi-maître), ALTER TABLE ... AUTO_INCREMENT= sur une table chargée, MySQL 8 (historique de persistance différent).*
Toutes les parties de la série
2/5 MariaDB
3/5 Oracle (à paraître le 24/11)
4/5 SQL Server (à paraître le 1/12)
5/5 TL;DR : les quatre moteurs comparés (à paraître le 8/12)
Articles liés
Dans les fichiers d’une base de données (1/3) : InnoDB, de MySQL à MariaDB
Dans les fichiers d’une base de données (2/3) : PostgreSQL
Dans les fichiers d’une base de données (3/3) : Oracle, et les trois côte à côte
Laisser un commentaire