mysql_insert_id

[Toni]

Hallo,

habe eine Frage zu mysql_insert_id.

Gibt es eine Möglichkeit alle IDs des letzten Insert Statements abzufangen?

Hintergrund:
Ich möchte mit einem Insert mehrere Values einfügen, also

INSERT INTO test(bla) VALUES (1),(2),(3)

mysql_insert_id würde mir ja nur den AUTO_INCREMENT vom Value (3) liefern. Ich bräuchte aber von jedem Value das AUTO_INCREMENT.

Klar ich könnte den INSERT auch durch eine foreach laufen lassen. Das wäre allerdings ein Performancekiller, da es ohne Weiteres mehrere Hundert Values sein könnten.

Da ich diese TABLE für diesen Vorgang auch nicht exclusiv sperren kann, würde es mir auch nichts nutzen, den alten AUTO_INCREMENT zwischenzuspeichern und dann von da an solange zu inkrementieren bis ich die Anzahl der Values erreicht habe.

Hat da jemand eine Idee zu?

MFG
Toni

[Alexander Brock]

Hallo,

Ich möchte mit einem Insert mehrere Values einfügen, also

INSERT INTO test(bla) VALUES (1),(2),(3)

Du könntest nach dem insert die ids abfragen:

select id from test where bla in (1,2,3)

Da hast du halt zwei Abfragen statt ein paar hundert.

Gruß
Alexander Brock

[Toni]

ja daran hab ich auch schon gedacht - mein Beispiel ist stark vereinfacht.

Eigentlich sieht es eher so aus
INSERT INTO test(col_1,col_2,col_3,col_4,...) VALUES (val_1a, val_1b, val_1c, val_1d), (val_2a, val_2b, val_2c, val_2d), (val_3a, val_3b, val_3c, val_3d), ...

Keine der col_xy ist UNIQUE, daher würde ich keine Garantie haben, genau diesen eingefügten Datensatz bei einer SELECT zu erwischen, auch wenn ich so suchen lassen würde:

SELECT * FROM test WHERE col_1 = 'val_1a' AND col_2 = 'val_1b' AND col_3 = 'val_1c' AND col_4 = 'val_1d'

Der UNIQUE wäre halt der AUTO_INCREMENT, ich hoffe du verstehst worauf ich hinaus möchte.

[Tom]

Hello,

INSERT INTO test(bla) VALUES (1),(2),(3)

Du könntest nach dem insert die ids abfragen:

select id from test where bla in (1,2,3)

Da hast du halt zwei Abfragen statt ein paar hundert.

Die sind aber auch nicht automatisch atomar gekapselt. Man müsste also schon genau darüber nachdenken, ob ggf. ein anderer Prozess schon wieder mit den Daten herumgespielt hat.

Da hilft dann wieder die eigene eindeutige Transaktionsnummer im Tabellendesign. Solange die noch gesetzt ist, darf kein anderer Prozess mit dem Datensatz arbeiten.

Die Entfernung der Transaktionsnummer ist immer der letzte Schritt, wenn alle datenverändernden Arbeiten erledigt sind.

Alternativ könnte man sich auch mit einem (virtuellen) Lock auseinandersetzen.

http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html
http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_get-lock

Und hier sind noch einige Stellen für Dedlfix:

http://dev.mysql.com/doc/refman/5.1/en/internal-locking.html
http://dev.mysql.com/doc/refman/5.1/en/table-locking.html
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_concurrent_insert

Das Thema ist wohl bei MySQL noch nicht wirklich durch, denke ich.
Während andere Systeme teilweise Row-Locking beim Insert unterstützen, operiert MySQL immer noch mit Table-Locking.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[dedlfix]

Hi!

INSERT INTO test(bla) VALUES (1),(2),(3)
Du könntest nach dem insert die ids abfragen:
select id from test where bla in (1,2,3)
Da hast du halt zwei Abfragen statt ein paar hundert.
Die sind aber auch nicht automatisch atomar gekapselt. Man müsste also schon genau darüber nachdenken, ob ggf. ein anderer Prozess schon wieder mit den Daten herumgespielt hat.

Hier braucht man eigentlich gar nicht weiterzudenken, weil das nicht die Lösung für das eigentliche Problem war (was aber kein Vorwurf an Alexander Brock ist, denn die etwas sehr vereinfachten Darstellung von Toni war missverständlich).

Da hilft dann wieder die eigene eindeutige Transaktionsnummer im Tabellendesign. Solange die noch gesetzt ist, darf kein anderer Prozess mit dem Datensatz arbeiten.
Die Entfernung der Transaktionsnummer ist immer der letzte Schritt, wenn alle datenverändernden Arbeiten erledigt sind.

Baust du hier das vorhandene Locking-System mit Daten nach?

Alternativ könnte man sich auch mit einem (virtuellen) Lock auseinandersetzen.

http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html
http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_get-lock

Ich sehe das nicht als Alternative sondern als eine sinnvolle Lösung an. Wenn es denn aufgrund einer konkreten Aufgabenstellung überhaupt notwendig ist. Wenn es hingegen wirklich auf transkationsfeste Vorgänge ankommt, sollte man nicht am falschen Ende sparen und lieber InnoDB verwenden, als sich mit irgendwelchen Metadaten Transaktionen für Arme nachzubauen.

Und hier sind noch einige Stellen für Dedlfix:

http://dev.mysql.com/doc/refman/5.1/en/internal-locking.html
http://dev.mysql.com/doc/refman/5.1/en/table-locking.html
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_concurrent_insert

Das Thema ist wohl bei MySQL noch nicht wirklich durch, denke ich.
Während andere Systeme teilweise Row-Locking beim Insert unterstützen, operiert MySQL immer noch mit Table-Locking.

Hast du die verlinkten Stellen auch gelesen? Je ein Zitat vom ersten und zweiten Link:

"MySQL uses table-level locking for MyISAM, MEMORY, and MERGE  tables, and row-level locking for InnoDB tables."

"To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB and NDBCLUSTER."

Es hat also nichts mit "noch" zu tun sondern mit einer Design-Entscheidung pro Geschwindigkeit bei der MyISAM-Engine.

Lo!

[Tom]

Hello,

Hast du die verlinkten Stellen auch gelesen? Je ein Zitat vom ersten und zweiten Link:

Ja.

"MySQL uses table-level locking for MyISAM, MEMORY, and MERGE  tables, and row-level locking for InnoDB tables."

Das war bekannt, dass InnoDB einiges besser macht, aber es ist eben nicht "die normale MySQL-Engine". Was macht denn InnoDB schlechter? Sollte man nicht generell darauf umsteigen? Meine ganzen alten Projektchen sind alle noch mit MyISAM. Das einzige, was mir einfiele, wäre die Sicherbarkeit von MyISAM en Bloc. Da gibt es wohl bei InnoDB irgendwelche Probleme? Untersucht habe ich das aber noch nicht.

"To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB and NDBCLUSTER."

Es hat also nichts mit "noch" zu tun sondern mit einer Design-Entscheidung pro Geschwindigkeit bei der MyISAM-Engine.

Derartige Aussagen (es wäre eine Designentscheidung) haben sie bei anderen Dingen auch schon gamcht, wenn sie noch nicht soweit waren, es gebacken zu bekommen ;-P

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[dedlfix]

Hi!

Hast du die verlinkten Stellen auch gelesen? Je ein Zitat vom ersten und zweiten Link:
Ja.

Und warum behauptest du dann, MySQL könne kein row-level locking, anstatt zwischen den Engines zu unterscheiden?

"MySQL uses table-level locking for MyISAM, MEMORY, and MERGE  tables, and row-level locking for InnoDB tables."
Das war bekannt, dass InnoDB einiges besser macht, aber es ist eben nicht "die normale MySQL-Engine". Was macht denn InnoDB schlechter?

Darum ging es doch gar nicht, aber wenn du schon darauf ausweichen willst: Ich jedenfalls behaupte nicht, dass InnoDB schlechter sei. Für das typische Zielpublikum sind die Features einfach nicht notwendig. Und wenn du was "schlechteres" sehen willst: InnoDB kann keine Fulltext Search Indexes.

Es gibt aus dem Jahre 2008 (MySQL 5.0 als aktuelle Version) einen Vergleich zwischen MyISAM und InnoDB, der auch einen Benchmark zwischen beiden verlinkt. Da geht InnoDB in den meisten Fällen als schneller hervor. Allerdings wurde da nur Lesen getestet, und nicht, was praxisrelevanter wäre, Lese- und Schreiboperationen gemeinsam. InnoDB hat eine Menge mehr Features, aber die bekommt man nicht zu Nulltarif. Es ist ressourcenhungriger, zum Beispiel was den Festplattenzugriff und Speicherverbrauch anbelangt. Der Artikel ist von Mai 2004, also kann er nur MySQL 4.0 meinen, doch auch der erstverlinkte bestätigt das. Auch administrativ gibt es Unterschiede. MyISAM kann man nach einem read lock und einem Leeren der Puffer (flush tables) tabellenfein und am Stück kopieren. InnoDB kann man mehr oder weniger nur mit einem Dump sichern.

Sollte man nicht generell darauf umsteigen? Meine ganzen alten Projektchen sind alle noch mit MyISAM. Das einzige, was mir einfiele, wäre die Sicherbarkeit von MyISAM en Bloc. Da gibt es wohl bei InnoDB irgendwelche Probleme? Untersucht habe ich das aber noch nicht.

Ich gebe keine generellen Empfehlungen bei Dingen, die man sinnvollerweise nur individuell entscheiden kann. Wenn dir die Features von InnoDB zusagen und Eigenschaften wie Ressourcenhunger für dich keine Nachteile sind - nur zu. Bei schwachbrüstigen Maschinen oder wenn viele sich die Leistung teilen müssen, wird wohl MyISAM samt der bekannten Feature-Abstriche das Mittel der Wahl sein.

Derartige Aussagen (es wäre eine Designentscheidung) haben sie bei anderen Dingen auch schon gamcht, wenn sie noch nicht soweit waren, es gebacken zu bekommen ;-P

Ja und? Entwickelst du von Anfang an 100 Prozent funktionierende eierlegende Wollmichsäue? Auch du wirst beim ersten Entwurf Überlegungen machen, welche Features die Masse benötigt und welche nur Spezialisten brauchen, welche sich einfach und welche sich nur vergleichsweise aufwendig implementieren lassen. Und beim Implementieren hast auch du garantiert nicht die Erfahrung und das Können, alles effektiv und effizient erschaffen zu können.

Lo!

[dedlfix]

Hi!

Gibt es eine Möglichkeit alle IDs des letzten Insert Statements abzufangen?

Nein, jedenfalls nicht auf einfache Art und Weise. Aber vielleicht brauchst du gar nicht alle explizit zu kennen.

Ich möchte mit einem Insert mehrere Values einfügen, also
INSERT INTO test(bla) VALUES (1),(2),(3)
mysql_insert_id würde mir ja nur den AUTO_INCREMENT vom Value (3) liefern.

Nein, es liefert bei Mehrfach-Inserts den Wert des _ersten_ eingefügten Wertes, also hier von (1).

Ich bräuchte aber von jedem Value das AUTO_INCREMENT.

Vermutlich brauchst du das dann nur noch hochzuzählen. "Vermutlich" deswegen, weil ich keine Aussage fand, dass ein Multi-Insert atomar abgearbeitet wird, also kein anderer Prozess dazwischenfunken kann. Ich vermute das aber, auch im Hinblick auf Replikationen zu Slaves, bei denen die Daten nicht auseinanderlaufen dürfen. Siehe auch den folgenden Satz aus der Dokumentation zu LAST_INSERT_ID(): "If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server."

Lo!

[Toni]

OK, es is also der erste Wert - wieder was gelernt :)

Mmh es gibt ja noch 2 andere Möglichkeiten.
Wenn ich das DELAYED angebe, wartet der mit dem Insert ja, bis die Table nicht mehr genutzt wird. Also sollten dann alle Values hintereinander eingefügt werden. Allerdings könnte das ja eine Weile dauern...
Oder das HIGH_PRIORITY, wenn ich das richtig verstanden habe, werden andere INSERTs auf diese Tabelle in eine Qeue gehangen, bis der HIGH_PRIORITY INSERT durch ist. Oder verstehe ich das gerade falsch?
http://dev.mysql.com/doc/refman/5.1/en/insert.html

[dedlfix]

Hi!

OK, es is also der erste Wert - wieder was gelernt :)

Danke gleichfalls. Ich hatte den Fall noch nicht und las das gerade im Handbuch.

Mmh es gibt ja noch 2 andere Möglichkeiten.
Wenn ich das DELAYED angebe, wartet der mit dem Insert ja, bis die Table nicht mehr genutzt wird. Also sollten dann alle Values hintereinander eingefügt werden. Allerdings könnte das ja eine Weile dauern...

Der INSERT wartet, aber dein Query-Aufruf nicht. Du bekommst kein dir nützliches Ergebnis über LAST_INSERT_ID(), weil du nicht benachrichtigt wird, wann das INSERT bearbeitet wurde. DELAYED scheidet also schonmal komplett aus.

Oder das HIGH_PRIORITY, wenn ich das richtig verstanden habe, werden andere INSERTs auf diese Tabelle in eine Qeue gehangen, bis der HIGH_PRIORITY INSERT durch ist. Oder verstehe ich das gerade falsch?

Siehe auch Concurrent Inserts. Ich verstehe vor allem den Satz

"If there are multiple INSERT statements, they are queued and performed in sequence, concurrently with the SELECT statements."

so, dass INSERTs sich generell nicht in die Quere kommen und die Konkurrenz-Situation nur zwischen dem aktuellen Insert und irgendwelchen SELECTs zu sehen ist. LOW/HIGH_PRIORITY regelt nur diese Situation, hat also nichts mit der Atomarität der Inserts zu tun.

Lo!

[Toni]

Hehe, ich glaub jetzt hats geklingelt :)

Nen multiple Insert muss atomar sein. Der läuft doch in einer Transaktion, also wenn es meinetwegen nen Duplicate Key gibt, wird doch ein Rollback ausgeführt, und kein Value wird geschrieben. Erst wenn alle Values drinnen stehen, kommt doch der Commit. D.h. da kann nix dazwischen funken.

Jemand anderer Meinung?

[dedlfix]

Hi!

Nen multiple Insert muss atomar sein. Der läuft doch in einer Transaktion, also wenn es meinetwegen nen Duplicate Key gibt, wird doch ein Rollback ausgeführt, und kein Value wird geschrieben. Erst wenn alle Values drinnen stehen, kommt doch der Commit. D.h. da kann nix dazwischen funken.

Jemand anderer Meinung?

Ja, MySQL. MyISAM ist keine transaktionale Storage-Engine. Ein Multiple-Insert bricht beim Duplicate Key einfach ab. Was drin ist, ist drin, der Rest nicht.

Lo!

[Toni]

Stimmt das is ja nur bei InnoDB - ach Mist :)

"Normally, you do not need to lock tables, because all single UPDATE statements are atomic; no other session can interfere with any other currently executing SQL statement."

mmh und nen INSERT fällt da nicht mit rein.

Also werde ich wohl einen Lock setzen müssen.

[dedlfix]

Hi!

"Normally, you do not need to lock tables, because all single UPDATE statements are atomic; no other session can interfere with any other currently executing SQL statement."
mmh und nen INSERT fällt da nicht mit rein.

Ich denke auch INSERTs müssen atomar sein, sonst wäre das Auto-Increment-Handling im Hinblick auf Replication nicht sinnvoll realisierbar, aber es steht eben nicht explizit da, oder ich hab es nur nicht gefunden.

Lo!

[Toni]

Naja nen Single Insert ist es sicherlich, jedenfalls hab ich dazu auch nix gefunden. Aber da MyISAM nicht transaktionssicher ist, gilt auch ACID nicht. Hast du ja selber schon erklärt ("was drinnen steht, steht drinnen"). Ach ich weiß auch nicht mehr weiter *gg* Ich werde einfach mal im Developer Forum nachfragen, vllt wissen die was genaueres.

[Tom]

Hello,

Nen multiple Insert muss atomar sein. Der läuft doch in einer Transaktion, also wenn es meinetwegen nen Duplicate Key gibt, wird doch ein Rollback ausgeführt, und kein Value wird geschrieben. Erst wenn alle Values drinnen stehen, kommt doch der Commit. D.h. da kann nix dazwischen funken.

Jemand anderer Meinung?

Ja, MySQL. MyISAM ist keine transaktionale Storage-Engine. Ein Multiple-Insert bricht beim Duplicate Key einfach ab. Was drin ist, ist drin, der Rest nicht.

Darum würde ich solche Multiinserts bei MySQL, wenn ich sie denn verwenden würde, auch immer mit einer eindeutigen Transaktionsnummer versehen, also im Tabellendesign eine zusätzliche Spalte berücksichtigen. Dann kann man hinterher die Datensätze wieder selektieren.

Im Interesse der Datenintegrität würde ich aber eher auf Multiinserts verzichten.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg