MySQL
Transakcja

Rozpocznij transakcję

Transakcja to sekwencyjna grupa instrukcji SQL, takich jak select, insert, update lub delete, która jest wykonywana jako jedna jednostka pracy.

Innymi słowy, transakcja nigdy nie zostanie zakończona, chyba że każda pojedyncza operacja w grupie zakończy się powodzeniem. Jeśli jakakolwiek operacja w ramach transakcji zakończy się niepowodzeniem, cała transakcja zakończy się niepowodzeniem.

Transakcja bankowa będzie najlepszym przykładem na wyjaśnienie tego. Rozważ przeniesienie między dwoma kontami. Aby to osiągnąć, musisz napisać instrukcje SQL, które wykonują następujące czynności

1. Sprawdź dostępność żądanej kwoty na pierwszym koncie
2. Odlicz żądaną kwotę z pierwszego konta
3. Zdeponuj na drugim koncie

Jeśli ktokolwiek ten proces zawiedzie, całość powinna zostać przywrócona do poprzedniego stanu.

ACID: właściwości transakcji

Transakcje mają następujące cztery standardowe właściwości

• Atomowość: zapewnia pomyślne zakończenie wszystkich operacji w jednostce roboczej; w przeciwnym razie transakcja zostanie przerwana w momencie awarii, a poprzednie operacje zostaną przywrócone do poprzedniego stanu.
• Spójność: zapewnia, że baza danych poprawnie zmienia stany po pomyślnie zatwierdzonej transakcji.
• Izolacja: umożliwia niezależne i przejrzyste transakcje.
• Trwałość: zapewnia, że wynik lub efekt popełnionej transakcji utrzymuje się w przypadku awarii systemu.

Transakcje rozpoczynają się od instrukcji START TRANSACTION lub BEGIN WORK a kończą albo instrukcją COMMIT albo ROLLBACK . Polecenia SQL między instrukcją początkową a końcową stanowią większość transakcji.

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;

Dzięki START TRANSACTION automatyczne zatwierdzanie pozostaje wyłączone, dopóki nie zakończysz transakcji za pomocą COMMIT lub ROLLBACK . Tryb automatycznego zatwierdzania powraca następnie do poprzedniego stanu.

FOR UPDATE wskazuje (i blokuje) wiersz (-y) na czas trwania transakcji.

Chociaż transakcja pozostaje niezatwierdzona, ta transakcja nie będzie dostępna dla innych użytkowników.

Ogólne procedury dotyczące transakcji

• Rozpocznij transakcję, wydając polecenie SQL BEGIN WORK lub START TRANSACTION .
• Uruchom wszystkie instrukcje SQL.
• Sprawdź, czy wszystko jest wykonywane zgodnie z wymaganiami.
• Jeśli tak, wydaj polecenie COMMIT , w przeciwnym razie wydaj polecenie ROLLBACK aby przywrócić wszystko do poprzedniego stanu.
• Sprawdź błędy, nawet po COMMIT jeśli używasz lub może w końcu użyć Galera / PXC.

COMMIT, ROLLBACK i AUTOCOMMIT

AUTOCOMMIT

MySQL automatycznie zatwierdza wyciągi, które nie są częścią transakcji. Wyniki dowolnej UPDATE , DELETE lub INSERT , które nie poprzedzono BEGIN lub START TRANSACTION będą natychmiast widoczne dla wszystkich połączeń.

AUTOCOMMIT zmienna AUTOCOMMIT ma wartość true . Można to zmienić w następujący sposób:

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

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

Aby wyświetlić status AUTOCOMMIT

SELECT @@autocommit;

POPEŁNIĆ

Jeśli AUTOCOMMIT wartość false, a transakcja nie została zatwierdzona, zmiany będą widoczne tylko dla bieżącego połączenia.

Po zatwierdzeniu zmian w tabeli przez instrukcję COMMIT wynik będzie widoczny dla wszystkich połączeń.

Rozumiemy dwa połączenia, aby to wyjaśnić

Połączenie 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 |
+-----+

Połączenie 2

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

Połączenie 1

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

Połączenie 2

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

ROLLBACK

Jeśli coś poszło nie tak podczas wykonywania zapytania, ROLLBACK służy do przywrócenia zmian. Zobacz wyjaśnienie poniżej

--->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 |
+-----+

Teraz wykonujemy ROLLBACK

--->Rollback executed now
mysql> ROLLBACk;

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

Po wykonaniu COMMIT ROLLBACK nic nie spowoduje

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

Jeśli AUTOCOMMIT jest zestaw prawdą, COMMIT i ROLLBACK jest bezużyteczny

Transakcja przy użyciu sterownika JDBC

Transakcja przy użyciu sterownika JDBC służy do kontrolowania, w jaki sposób i kiedy transakcja powinna zostać zatwierdzona i wycofana. Połączenie z serwerem MySQL jest tworzone za pomocą sterownika JDBC

Sterownik JDBC dla MySQL można pobrać tutaj

Zacznijmy od uzyskania połączenia z bazą danych za pomocą sterownika JDBC

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");

Zestawy znaków : Wskazuje, jakiego zestawu znaków klient użyje do wysłania instrukcji SQL na serwer. Określa również zestaw znaków, którego serwer powinien używać do wysyłania wyników z powrotem do klienta.

Należy o tym wspomnieć podczas tworzenia połączenia z serwerem. Ciąg połączenia powinien być taki,

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

Zobacz to, aby uzyskać więcej informacji na temat zestawów znaków i zestawień

Po otwarciu połączenia tryb AUTOCOMMIT jest domyślnie ustawiony na true , który należy zmienić na false, aby rozpocząć transakcję.

con.setAutoCommit(false);

Powinieneś zawsze wywoływać setAutoCommit() zaraz po otwarciu połączenia.

W przeciwnym razie użyj START TRANSACTION lub BEGIN WORK aby rozpocząć nową transakcję. Używając START TRANSACTION lub BEGIN WORK , nie musisz zmieniać wartości AUTOCOMMIT false . To zostanie automatycznie wyłączone.

Teraz możesz rozpocząć transakcję. Zobacz pełny przykład transakcji JDBC poniżej.

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);
    }
}

Transakcja JDBC upewnij się, że wszystkie instrukcje SQL w bloku transakcji zostały wykonane pomyślnie, jeśli jedna z instrukcji SQL w bloku transakcji nie powiedzie się, przerwij i przywróć wszystko w bloku transakcji.