MySQL
Transazione

Avvia transazione

Una transazione è un gruppo sequenziale di istruzioni SQL come selezionare, inserire, aggiornare o eliminare, che viene eseguito come una singola unità di lavoro.

In altre parole, una transazione non sarà mai completa a meno che ogni singola operazione all'interno del gruppo abbia esito positivo. Se una qualsiasi operazione all'interno della transazione fallisce, l'intera transazione fallirà.

La transazione bancaria sarà il miglior esempio per spiegare questo. Considera un trasferimento tra due account. Per ottenere ciò è necessario scrivere istruzioni SQL che eseguono le seguenti operazioni

1. Verifica la disponibilità dell'importo richiesto nel primo account
2. Detrarre l'importo richiesto dal primo account
3. Depositalo in secondo conto

Se qualcuno di questi processi fallisce, l'intero dovrebbe essere ripristinato allo stato precedente.

ACID: Proprietà delle transazioni

Le transazioni hanno le seguenti quattro proprietà standard

• Atomicità: assicura che tutte le operazioni all'interno dell'unità di lavoro siano state completate con successo; in caso contrario, la transazione viene interrotta nel punto di errore e le operazioni precedenti vengono riportate al loro stato precedente.
• Coerenza: assicura che il database modifichi correttamente gli stati in seguito a una transazione confermata correttamente.
• Isolamento: consente alle transazioni di operare in modo indipendente e trasparente l'una dall'altra.
• Durata: assicura che il risultato o l'effetto di una transazione confermata persista in caso di un errore del sistema.

Le transazioni iniziano con l'istruzione START TRANSACTION o BEGIN WORK e terminano con un'istruzione COMMIT o ROLLBACK . I comandi SQL tra le istruzioni di inizio e di fine costituiscono la maggior parte della transazione.

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;

Con START TRANSACTION , l'autocommit rimane disabilitato fino a quando non si termina la transazione con COMMIT o ROLLBACK . La modalità autocommit ritorna al suo stato precedente.

FOR UPDATE indica (e blocca) la / e riga / e per la durata della transazione.

Mentre la transazione rimane non impegnata, questa transazione non sarà disponibile per altri utenti.

Procedure generali coinvolte nella transazione

• Iniziare la transazione emettendo il comando SQL BEGIN WORK o START TRANSACTION .
• Esegui tutte le tue istruzioni SQL.
• Controlla se tutto è eseguito in base alle tue esigenze.
• In caso affermativo, emettere il comando COMMIT , altrimenti inviare un comando ROLLBACK per riportare tutto allo stato precedente.
• Verificare gli errori anche dopo COMMIT se si utilizza, o potrebbe eventualmente utilizzare, Galera / PXC.

COMMIT, ROLLBACK e AUTOCOMMIT

AUTOCOMMIT

MySQL impegna automaticamente le dichiarazioni che non fanno parte di una transazione. I risultati di qualsiasi INSERT UPDATE , DELETE o INSERT non preceduta da un BEGIN o START TRANSACTION saranno immediatamente visibili a tutte le connessioni.

La variabile AUTOCOMMIT è impostata su true per impostazione predefinita. Questo può essere cambiato nel modo seguente,

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

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

Per visualizzare lo stato AUTOCOMMIT

SELECT @@autocommit;

COMMETTERE

Se AUTOCOMMIT impostato su false e la transazione non è stata confermata, le modifiche saranno visibili solo per la connessione corrente.

Dopo l'istruzione COMMIT le modifiche alla tabella, il risultato sarà visibile per tutte le connessioni.

Consideriamo due connessioni per spiegare questo

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

Connessione 2

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

Connessione 1

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

Connessione 2

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

ROLLBACK

Se qualcosa è andato storto nell'esecuzione della query, ROLLBACK utilizzato per annullare le modifiche. Vedi la spiegazione qui sotto

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

Ora stiamo eseguendo ROLLBACK

--->Rollback executed now
mysql> ROLLBACk;

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

Una volta eseguito COMMIT , quindi ROLLBACK non causerà nulla

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

Se AUTOCOMMIT è impostato su true , COMMIT e ROLLBACK sono inutili

Transazione utilizzando il driver JDBC

La transazione che utilizza il driver JDBC viene utilizzata per controllare come e quando una transazione deve eseguire il commit e il rollback. La connessione al server MySQL viene creata utilizzando il driver JDBC

Il driver JDBC per MySQL può essere scaricato qui

Iniziamo con una connessione al database usando il driver 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");

Set di caratteri : indica quale set di caratteri verrà utilizzato dal client per inviare istruzioni SQL al server. Specifica inoltre il set di caratteri che il server deve utilizzare per inviare i risultati al client.

Questo dovrebbe essere menzionato durante la creazione di una connessione al server. Quindi la stringa di connessione dovrebbe essere come,

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

Vedi questo per maggiori dettagli su Set di caratteri e regole di confronto

Quando si apre la connessione, la modalità AUTOCOMMIT è impostata su true per impostazione predefinita, che deve essere modificata false per avviare la transazione.

con.setAutoCommit(false);

Dovresti sempre chiamare il metodo setAutoCommit() subito dopo aver aperto una connessione.

Altrimenti usa START TRANSACTION o BEGIN WORK per iniziare una nuova transazione. Utilizzando START TRANSACTION o BEGIN WORK , non è necessario modificare AUTOCOMMIT false . Questo sarà automaticamente disabilitato.

Ora puoi iniziare la transazione. Vedere un esempio di transazione JDBC completo di seguito.

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

La transazione JDBC garantisce che tutte le istruzioni SQL all'interno di un blocco di transazione siano eseguite correttamente, se una delle istruzioni SQL nel blocco di transazione non è riuscita, interrompe e ripristina tutto all'interno del blocco di transazione.