Wie führe ich in einer Umgebung mit mehreren Sitzungen / Transaktionen eine Zeile sicher in eine Tabelle ein, die einen primären zusammengesetzten Schlüssel mit einem (manuellen) Inkrementschlüssel enthält.
Und wie bekomme ich den letzten erhöhten Wert von column_c , LAST_INSERT_ID() nicht den gewünschten Wert zurück.
Ich habe in SELECT FOR UPDATE ... INSERT und INSERT INTO SELECT gesucht, kann mich aber nicht entscheiden, welches ich verwenden soll.
Was ist der beste Weg dies zu erreichen in Bezug auf Transaktionssicherheit (Lock), Isolation Level und Performance-Standpunkt.
Update - Eine weitere Lösung des Problems
Nehmen wir an, zwei Transaktionen / Sitzungen versuchen, das gleiche column_a, column_b-Paar (Beispiel 1.1) gleichzeitig einzufügen. Wie mache ich das?
Führen Sie die Insert-Abfragen nacheinander aus. Die erste Einfügung (Transaktion 1) sollte zu einem zusammengesetzten Schlüssel von 1,1 führen, 1 , und der zweite (Transaktion 2) 1,1, 2 . Ich brauche eine Art Sperrmechanismus
Rufen Sie den column_c-Wert der Einfügung ab. Ich muss wahrscheinlich Variablen verwenden?
Tabellendefinition
%Vor%Daten angeben
%Vor%Nehmen Sie die Einfügung in die Select-Abfrage
auf %Vor%Sie können dafür eine gespeicherte Prozedur verwenden:
Ich bin nie auf diese Art von Problem gestoßen und wenn ich es jemals tue, würde ich Folgendes tun:
%Vor%Verwendung wäre:
%Vor%Ergebnis:
%Vor%gilt auch innerhalb einer Transaktion:
%Vor%Ich habe jedoch keine parallelen Transaktionen versucht!
Die Hoffnung ist, dass der FOR UPDATE stehen bleibt, bis er eine Sperre und den gewünschten c -Wert erhalten kann. Dann sollte der Rest der Transaktion reibungslos verlaufen.
Ich denke nicht, dass die Einstellung von transaction_isolation zählt, aber das ist es wert, studiert zu werden.
Nennen wir die Tabelle, die diese 3 Spalten enthält, als ThreeColumnTable , um Verwirrung zu vermeiden, die sich aus dem Namen ergibt, den Sie angegeben haben - table .
Es ist die Spalte column_c , die manuell inkrementiert wird. Ziehen Sie es heraus und verfolgen Sie den letzten Wert, der für diese Spalte in einer anderen Tabelle verwendet wird.
Lösungsschritte:
column_c verwendete Wert gespeichert wird. Nennen wir diese Tabelle LastUsedIdTable . LastUsedIdTable enthält nur drei Spalten:
ThreeColumnTable ); column_c ); 121 ). LastUsedIdTable ausführt. Dieser Proc liest den letzten Wert, der in Ihrem Fall für column_c verwendet wird. Erhöhen Sie es. Geben Sie den erhöhten Wert an Sie zurück. (Natürlich können Sie auch jedes Mal eine direkte Abfrage durchführen. Stored Procedure ist eine bessere Wahl.) column_c für Sie eingefroren, weil die Stored Procedure den Wert in LastUsedIdTable für die Zeile ThreeColumnTable für immer erhöht hat. Jeder, der dem ThreeColumnTable eine weitere Zeile hinzufügen möchte, ruft den Stored Proc auf und erhält den nicht kollidierenden und inkrementierten Wert, auch wenn Sie noch nicht mit dem Einfügen Ihres vorherigen Wertes in Ihr table . Demonstration der Durchführbarkeit der Lösung:
Beachten Sie, dass Sie simultane n -Anfragen zum Einfügen in ThreeColumnTable haben, damit die Demonstration verallgemeinert wird.
Alle n Anfragen müssen zuerst die Stored Procedure aufrufen. Da der Stored Proc eine Transaktion auf dem LastUsedIdTable verwendet, wird zu einem Zeitpunkt nur 1 Anfrage auf die Zeile für ThreeColumnTable zugreifen, die momentan wie folgt aussieht:
Nun wird die erste Anfrage diese Zeile sperren und 122 als Wert erhalten, und den Wert in der Tabellenzeile auf 122 aktualisieren. Bis die n Anfragen beendet sind, Die Zeile LastUsedIdTable ThreeColumnTable sieht folgendermaßen aus:
Nun sind diese n -Anfragen bereits auf dem Weg, die Einfügung in ThreeColumnTable durchzuführen. Aber da sie alle ihre eigenen eindeutigen Werte von column_c haben, ist ihre Einfügung frei von Konflikten, unabhängig von der Reihenfolge, in der das Einfügen stattfindet! Möglicherweise haben Sie zuerst den Wert 121 + n und zuletzt 122 eingefügt. Tatsächlich brauchen Sie das Tupel column_a, column_b, column_c nicht einmal, weil column_c immer eindeutig ist.
Diese Lösung eignet sich sowohl für parallele als auch für serielle Anfragen. Mit nur sehr geringen (bis zu vernachlässigbaren) Leistungseinbußen wäre dies der beste Punkt für Transaktionssicherheit, Isolationsstufe und Leistung, die Sie zu erreichen versuchen.
Wichtig: Verwenden Sie LastUsedId , um den letzten Wert aller Primärschlüsselspalten in allen Tabellen Ihrer Datenbank zu speichern. Da diese Tabelle nur so viele Zeilen enthält wie die Anzahl der Schlüssel in Ihrer gesamten Datenbank, die Sie manuell inkrementieren möchten, enthält sie nur eine begrenzte und feste Anzahl von Zeilen. Dies wird schnell und rennfrei sein.
Sie können einen Dummy-Wert für column_c verwenden, um die Kombination (column_a, column_b) für andere Einfügungen zu sperren, wodurch insbesondere sichergestellt wird, dass sie auch dann gesperrt ist, wenn für diese Kombination noch keine Zeile existiert.
Das erste insert wird die exakte Kombination sperren ( column_a, column_b) , aber es blockiert keine anderen Werte, so dass Sie andere Kombinationen einfügen können, während die erste Transaktion ausgeführt wird.
Es funktioniert mit jeder Transaktionsebene, weil select max() korrekt ist (und korrekt Lückensperre), selbst wenn eine andere Sitzung Zeilen der gleichen Kombination (außer der gesperrten Zeile mit column_c = 0 ) nach der ersten Einfügung aktualisiert ; Wenn Sie jedoch READ UNCOMMITTED oder SERIALIZABLE verwenden, wird der temporäre Wert (mit column_c = 0 ) natürlich für andere Sitzungen angezeigt. Falls Sie dies stört, verwenden Sie eine höhere Stufe (z. B. die Standardeinstellung beibehalten).
@c wird, wie erforderlich, Ihre letzte ID enthalten, @a und @b werden durch Ihre Werte ersetzt und müssen keine Variablen sein.
Wenn Sie diesen Code in eine gespeicherte Prozedur schreiben, denken Sie daran, dass MySQL verschachtelte Transaktionen nicht unterstützt. Daher ist es immer riskant, eine Transaktion in einer Prozedur zu starten (und insbesondere zu committen), also sollten Sie die Transaktionsverarbeitung durchführen außerhalb des Verfahrens.
on duplicate key ist nur dazu da, um es narrensicher zu machen. Wenn alles funktioniert, brauchen Sie es nicht, aber es stellt sicher, dass der Code auch dann funktioniert, wenn jemand (manuell) eine ungültige Zeile mit column_c = 0 hinzugefügt hat und sie dort belassen hat. Oder, wenn jemand den Code in eine Prozedur einfügt, jemand ihn aufgerufen hat, ohne zuerst eine Transaktion zu starten, und eine andere Sitzung diese Kombination zur gleichen Zeit einfügt, kann dies zu einem doppelten Schlüsselfehler führen (für update ) und somit könnte zu einer Rest-Zeile mit column_c = 0 führen (die Sie natürlich in einem Exception-Handler in Ihrer Prozedur löschen könnten). Es könnte von Interesse sein, sich (durch weinene Nutzer) zu informieren, wenn eine solche Situation aufgetreten ist, sodass Sie on duplicate key (zumindest für Testzwecke) entfernen möchten.
Option 1
Dies sollte atomar sein und scheint die korrekten Werte einzufügen:
%Vor% Dabei sind :column_a und :column_b Ihre neuen Werte.
Wenn Sie die Funktion LAST_INSERT_ID() verwenden möchten, funktioniert sie leider nur mit AUTO_INCREMENT Werten.
Sie könnten einen Ersatz-Primärschlüssel hinzufügen:
%Vor% Und führen Sie die gleiche INSERT Abfrage oben aus. Jetzt wird Ihr LAST_INSERT_ID() auf die neu eingefügte Zeile verweisen.
Wenn Sie einen Ersatzschlüssel hinzufügen, kann es sinnvoll sein, erneut zu prüfen, ob column_c noch erforderlich ist.
Option 2
Sie können möglicherweise auch einen Ersatzschlüssel hinzufügen, indem Sie eine Benutzervariable innerhalb einer einzelnen Verbindung / Transaktion / Prozedur verwenden:
%Vor%Option 3
Wenn Sie nur diese Spalten in Ihre Tabelle einfügen oder aktualisieren, können Sie manuell eine Namenssperrung durchführen. Simuliert Datensatzsperren mit GET_LOCK() innerhalb einer einzelnen Transaktion.
Starten Sie eine Transaktion.
Wählen Sie einen bestimmten Namen für die Zeilen, die Sie sperren möchten. z.B. %Code%. Dabei ist 'insert_table_name_aaa_bbb' der Wert für column_a und 'aaa' ist der Wert für column_b.
Aufruf von 'bbb' , um den Namen SELECT GET_LOCK('insert_table_name_aaa_bbb',30) zu sperren. Es wird 1 zurückgegeben und die Sperre festgelegt, wenn der Name verfügbar ist, oder 0 zurückgegeben, wenn die Sperre nach 30 Sekunden nicht verfügbar ist (der zweite Parameter ist die Zeitüberschreitung). .
Führen Sie Ihre 'insert_table_name_aaa_bbb' und SELECT Abfragen hier durch.
Verwenden Sie INSERT , wenn Sie fertig sind.
Übernehmen Sie die Transaktion.
Seien Sie sich bewusst; Durch erneutes Aufrufen von DO RELEASE_LOCK('insert_table_name_aaa_bbb') in einer Transaktion wird die zuvor festgelegte Sperre aufgehoben. Diese genannte Sperre gilt auch nur für dieses Szenario oder wenn der genaue Name verwendet wird. Die Sperre gilt nur für den Namen!
GET_LOCK() docs