MySQL
Transaktion

Starten Sie die Transaktion

Eine Transaktion ist eine sequentielle Gruppe von SQL-Anweisungen wie select, insert, update oder delete, die als eine einzige Arbeitseinheit ausgeführt wird.

Mit anderen Worten, eine Transaktion ist niemals abgeschlossen, es sei denn, jede einzelne Operation in der Gruppe ist erfolgreich. Wenn eine Operation innerhalb der Transaktion fehlschlägt, schlägt die gesamte Transaktion fehl.

Die Banktransaktion ist das beste Beispiel, um dies zu erklären. Betrachten Sie eine Übertragung zwischen zwei Konten. Um dies zu erreichen, müssen Sie SQL-Anweisungen schreiben, die Folgendes tun

1. Überprüfen Sie die Verfügbarkeit des angeforderten Betrags im ersten Konto
2. Den angeforderten Betrag vom ersten Konto abziehen
3. Zahlen Sie es auf das zweite Konto ein

Wenn einer dieser Prozesse fehlschlägt, sollte das Ganze in den vorherigen Zustand zurückgesetzt werden.

ACID: Eigenschaften von Transaktionen

Transaktionen haben die folgenden vier Standardeigenschaften

• Atomizität: stellt sicher, dass alle Operationen innerhalb der Arbeitseinheit erfolgreich abgeschlossen werden; Andernfalls wird die Transaktion zum Zeitpunkt des Scheiterns abgebrochen, und vorherige Vorgänge werden in ihren früheren Zustand zurückgesetzt.
• Konsistenz: Stellt sicher, dass die Datenbank den Status bei einer erfolgreich abgeschlossenen Transaktion ordnungsgemäß ändert.
• Isolation: Ermöglicht Transaktionen unabhängig voneinander und transparent zu sein.
• Dauerhaftigkeit: Stellt sicher, dass das Ergebnis oder die Wirkung einer festgeschriebenen Transaktion bei einem Systemausfall erhalten bleibt.

Transaktionen beginnen mit der Anweisung START TRANSACTION oder BEGIN WORK und enden entweder mit einer COMMIT oder einer ROLLBACK Anweisung. Die SQL-Befehle zwischen der Anfangs- und der Endanweisung bilden den Großteil der Transaktion.

START TRANSACTION;
SET @transAmt = '500';
SELECT @availableAmt:=ledgerAmt FROM accTable WHERE customerId=1 FOR UPDATE;
UPDATE accTable SET ledgerAmt=ledgerAmt-@transAmt WHERE customerId=1;
UPDATE accTable SET ledgerAmt=ledgerAmt+@transAmt WHERE customerId=2;
COMMIT;

Mit START TRANSACTION bleibt Autocommit deaktiviert, bis Sie die Transaktion mit COMMIT oder ROLLBACK beenden. Der Autocommit-Modus kehrt dann in den vorherigen Zustand zurück.

Das FOR UPDATE zeigt (und sperrt) die Zeile (n) für die Dauer der Transaktion.

Während die Transaktion nicht festgeschrieben ist, steht diese Transaktion für andere Benutzer nicht zur Verfügung.

Allgemeine an der Transaktion beteiligte Verfahren

• Beginnen Sie die Transaktion mit dem SQL-Befehl BEGIN WORK oder START TRANSACTION .
• Führen Sie alle Ihre SQL-Anweisungen aus.
• Prüfen Sie, ob alles Ihren Anforderungen entspricht.
• Wenn ja, geben Sie den Befehl COMMIT . Andernfalls geben Sie den Befehl ROLLBACK aus, um den vorherigen Zustand ROLLBACK .
• Prüfen Sie auch nach COMMIT auf Fehler, wenn Sie Galera / PXC verwenden oder möglicherweise verwenden.

COMMIT, ROLLBACK und AUTOCOMMIT

AUTOCOMMIT

MySQL schreibt automatisch Anweisungen ein, die nicht Teil einer Transaktion sind. Die Ergebnisse von UPDATE , DELETE oder INSERT Anweisungen, denen keine BEGIN oder START TRANSACTION Anweisung vorangestellt ist, sind sofort für alle Verbindungen sichtbar.

Die Variable AUTOCOMMIT ist standardmäßig auf true gesetzt. Dies kann auf folgende Weise geändert werden:

--->To make autcommit false
SET AUTOCOMMIT=false;
--or
SET AUTOCOMMIT=0;

--->To make autcommit true
SET AUTOCOMMIT=true;
--or
SET AUTOCOMMIT=1;

Zum Anzeigen des AUTOCOMMIT Status

SELECT @@autocommit;

VERPFLICHTEN

Wenn AUTOCOMMIT auf false gesetzt ist und die Transaktion nicht festgeschrieben ist, sind die Änderungen nur für die aktuelle Verbindung sichtbar.

Nachdem die COMMIT Anweisung die Änderungen an der Tabelle COMMIT , ist das Ergebnis für alle Verbindungen sichtbar.

Wir betrachten zwei Verbindungen, um dies zu erklären

Verbindung 1

--->Before making autocommit false one row added in a new table
mysql> INSERT INTO testTable VALUES (1);

--->Making autocommit = false
mysql> SET autocommit=0;

mysql> INSERT INTO testTable VALUES (2), (3);    
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
|   1 |
|   2 |
|   3 |
+-----+

Verbindung 2

mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
|   1 |
+-----+
---> Row inserted before autocommit=false only visible here

Verbindung 1

mysql> COMMIT;
--->Now COMMIT is executed in connection 1
mysql> SELECT * FROM testTable;
    +-----+
    | tId |
    +-----+
    |   1 |
    |   2 |
    |   3 |
    +-----+

Verbindung 2

mysql> SELECT * FROM testTable;
    +-----+
    | tId |
    +-----+
    |   1 |
    |   2 |
    |   3 |
    +-----+
--->Now all the three rows are visible here

ROLLBACK

Wenn bei der Abfrageausführung ein ROLLBACK aufgetreten ist, wurden die Änderungen mit ROLLBACK in rückgängig gemacht. Siehe die Erklärung unten

--->Before making autocommit false one row added in a new table
mysql> INSERT INTO testTable VALUES (1);

--->Making autocommit = false
mysql> SET autocommit=0;

mysql> INSERT INTO testTable VALUES (2), (3);    
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
|   1 |
|   2 |
|   3 |
+-----+

Jetzt führen wir ROLLBACK

--->Rollback executed now
mysql> ROLLBACk;

mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
|   1 |
+-----+
--->Rollback removed all rows which all are not committed

Sobald COMMIT ausgeführt wird, verursacht ROLLBACK nichts

mysql> INSERT INTO testTable VALUES (2), (3);    
mysql> SELECT * FROM testTable;
mysql> COMMIT;
+-----+
| tId |
+-----+
|   1 |
|   2 |
|   3 |
+-----+

--->Rollback executed now
mysql> ROLLBACk;

mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
|   1 |
|   2 |
|   3 |
+-----+
--->Rollback not removed any rows

Wenn AUTOCOMMIT auf true gesetzt ist , sind COMMIT und ROLLBACK unbrauchbar

Transaktion mit JDBC-Treiber

Transaktion mit JDBC-Treiber wird verwendet, um zu steuern, wie und wann eine Transaktion festgeschrieben und zurückgesetzt werden soll. Die Verbindung zum MySQL-Server wird mithilfe des JDBC-Treibers hergestellt

Der JDBC-Treiber für MySQL kann hier heruntergeladen werden

Beginnen wir mit dem Herstellen einer Verbindung zur Datenbank mithilfe des JDBC-Treibers

Class.forName("com.mysql.jdbc.Driver");  
Connection con = DriverManager.getConnection(DB_CONNECTION_URL,DB_USER,USER_PASSWORD);
--->Example for connection url "jdbc:mysql://localhost:3306/testDB");

Zeichensätze : Gibt an, welchen Zeichensatz der Client zum Senden von SQL-Anweisungen an den Server verwendet. Es gibt außerdem den Zeichensatz an, den der Server zum Senden von Ergebnissen an den Client verwenden soll.

Dies sollte beim Herstellen der Verbindung zum Server erwähnt werden. Die Verbindungszeichenfolge sollte also lauten:

jdbc:mysql://localhost:3306/testDB?useUnicode=true&characterEncoding=utf8

Weitere Informationen zu Zeichensätzen und Kollatierungen finden Sie hier

Wenn Sie offene Verbindung, die AUTOCOMMIT - Modus standardmäßig auf true gesetzt ist, werden das sollte falsch geändert Transaktion zu starten.

con.setAutoCommit(false);

Sie sollten die Methode setAutoCommit() immer direkt nach dem Öffnen einer Verbindung aufrufen.

Verwenden Sie andernfalls START TRANSACTION oder BEGIN WORK , um eine neue Transaktion zu starten. Wenn Sie START TRANSACTION oder BEGIN WORK , müssen Sie AUTOCOMMIT false nicht ändern. Das wird automatisch deaktiviert.

Jetzt können Sie mit der Transaktion beginnen. Nachfolgend finden Sie ein vollständiges Beispiel für eine JDBC-Transaktion.

package jdbcTest;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;


public class accTrans {

    public static void doTransfer(double transAmount,int customerIdFrom,int customerIdTo) {

        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
 
        try {
            String DB_CONNECTION_URL = "jdbc:mysql://localhost:3306/testDB?useUnicode=true&characterEncoding=utf8";

            Class.forName("com.mysql.jdbc.Driver");  
            con = DriverManager.getConnection(DB_CONNECTION_URL,DB_USER,USER_PASSWORD);

            --->set auto commit to false
            con.setAutoCommit(false);
            ---> or use con.START TRANSACTION / con.BEGIN WORK

            --->Start SQL Statements for transaction
            --->Checking availability of amount
            double availableAmt    = 0;
            pstmt = con.prepareStatement("SELECT ledgerAmt FROM accTable WHERE customerId=? FOR UPDATE");
            pstmt.setInt(1, customerIdFrom);
            rs = pstmt.executeQuery();
            if(rs.next())
                availableAmt    = rs.getDouble(1);

            if(availableAmt >= transAmount)
            {
                ---> Do Transfer
                ---> taking amount from cutomerIdFrom
                pstmt = con.prepareStatement("UPDATE accTable SET ledgerAmt=ledgerAmt-? WHERE customerId=?");                        
                pstmt.setDouble(1, transAmount);
                pstmt.setInt(2, customerIdFrom);
                pstmt.executeUpdate();

                ---> depositing amount in cutomerIdTo
                pstmt = con.prepareStatement("UPDATE accTable SET ledgerAmt=ledgerAmt+? WHERE customerId=?");                        
                pstmt.setDouble(1, transAmount);
                pstmt.setInt(2, customerIdTo);
                pstmt.executeUpdate();

                con.commit();
            }
            --->If you performed any insert,update or delete operations before 
            ----> this availability check, then include this else part
            /*else { --->Rollback the transaction if availability is less than required
                con.rollback();
            }*/

        } catch (SQLException ex) {
            ---> Rollback the transaction in case of any error
            con.rollback();
        } finally {
            try {
                if(rs != null)  rs.close();
                if(pstmt != null) pstmt.close();
                if(con != null) con.close();
            }
        }
    }
 
    public static void main(String[] args) {
        doTransfer(500, 1020, 1021);
        -->doTransfer(transAmount, customerIdFrom, customerIdTo);
    }
}

Die JDBC-Transaktion stellt sicher, dass alle SQL-Anweisungen innerhalb eines Transaktionsblocks erfolgreich ausgeführt werden. Wenn eine der SQL-Anweisungen innerhalb des Transaktionsblocks fehlgeschlagen ist, wird der gesamte Transaktionsblock abgebrochen und zurückgesetzt.