INSERT IGNORE in Verbindung mit einer auto_increment-Spalte in einer InnoDB-Tabelle. Diese Kombination führt bei mehrfachem Einfügen des gleichen Datensatzes zu Schweizer ID-Käse. Warum das so ist und wie Ihr das vermeiden könnt, seht Ihr im folgenden Artikel.

Vor kurzem habe ich für eine Datenbanken-Lehrveranstaltung einen möglichst realitätsnahen Test-Datensatz zusammengestellt. Dafür habe ich über eine API einfach direkt Artikel eines Produktkatalogs angefordert und in MySQL gespeichert. Diese API lieferte allerdings viele der Produkte mehrfach zurück und natürlich wollte ich keine Duplikate speichern. Was macht man in so einem Fall? Natürlich – man verwendet INSERT IGNORE. Diese spezielle Art des INSERTS gibt beim Versuch, einen bereits in der Tabelle vorhandenen Datensatz einzufügen, nur eine Warning zurück und fügt den neuen Datensatz nicht ein.

Dabei fiel mir auf, dass bei einem INSERT IGNORE der Zähler für das auto_increment auch erhöht wird, wenn die einzufügende Zeile ignoriert bzw. verworfen wird. Es wird also in Wirklichkeit kein weiterer Datensatz hinzugefügt, trotzdem wird der auto_increment Zähler weitergezählt. Dieses Verhalten führte zu einer großen, durchlöcherten ID-Spalte. Im folgenden Beispiel kann man gut erkennen, wie nach zwei „erfolglosen“ Einfüge-Operationen (der Hitchhiker ist in der Tabelle schon vorhanden), für das nächste eingefügte Element die ID 5 vergeben wird, obwohl es in Wirklichkeit erst das dritte eingefügte Element ist.

CREATE TABLE `products` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`product_name` varchar(100) NOT NULL,
PRIMARY KEY (`product_id`),
UNIQUE KEY `idx_name_unique` (`product_name`)
) ENGINE=InnoDB;

INSERT IGNORE INTO products(product_name)
VALUES ("Hitchhiker's Guide");
INSERT IGNORE INTO products(product_name)
VALUES ("Romeo and Juliet");
INSERT IGNORE INTO products(product_name)
VALUES ("Hitchhiker's Guide");
INSERT IGNORE INTO products(product_name)
VALUES ("Hitchhiker's Guide");
INSERT IGNORE INTO products(product_name)
VALUES ("Faust I");

SELECT * FROM products;
+------------+--------------------+
| product_id | product_name       |
+------------+--------------------+
| 1          | Hitchhiker's Guide |
| 2          | Romeo and Juliet   |
| 5          | Faust I            |
+------------+--------------------+

Doch wie kommen nun die Löcher wirklich in den Käse? Der Grund für dieses Verhalten ist, dass InnoDB bereits vor dem Einfügen des Datensatzes den auto_increment Zähler erhöht und damit zu diesem Zeitpunkt einfach noch nicht weiß, ob der Datensatz danach wirklich eingefügt wird oder nicht. InnoDB verwendet standardmäßig den „consecutive lock mode“ für auto_increment Werte. Das bedeutet, dass für jede INSERT-Operation zu Beginn der Operation eine Sperre (siehe auch Mutex) auf den Zähler gesetzt und dieser erhöht wird. Damit wird sichergestellt, dass IDs nicht mehrfach vergeben werden. Alternativ gibt es noch den „traditional lock mode“, der für die Dauer jeder Einfügeoperation eine Sperre auf die gesamte Tabelle setzt. Damit wir der auto_increment Zähler wirklich nur dann erhöht, wenn wirklich ein neuer Datensatz eingefügt wurde (siehe auch MySQL Reference Manual).

Grundsätzlich solltet Ihr euch also nie auf dichte IDs verlassen, wenn Ihr die Daten mit InnoDB, auto_increment und INSERT IGNORE einfügt. Das Gleiche gilt auch für INSERT … ON DUPLICATE UPDATE, auch dort werden von InnoDB die auto_increments fälschlicherweise erhöht.

Falls Ihr trotzdem dichte IDs generieren möchtet (bei mir waren es zugegebenermaßen kosmetische Gründe, die mich dazu veranlasst haben), gibt es die folgenden Möglichkeiten:

• Auf MyISAM wechseln, dort werden in einem solchen Szenario dichte IDs erzeugt
• den InnoDB Lock-Mode auf „traditional“ umstellen mittels
  innodb_autoinc_lock_mode = 0 in der my.cnf oder beim Starten des MySQL-Dienstes. Allerdings muss ich auch erwähnen, dass dieser Locking-Mode natürlich über eine wesentlich schlechtere Performance verfügt, da die Tabelle für jede Einfüge-Operation gesperrt werden muss.