MySQL
Transaction

Commencer la transaction

Une transaction est un groupe séquentiel d'instructions SQL telles que select, insert, update ou delete, qui se déroule en une seule unité de travail.

En d'autres termes, une transaction ne sera jamais complète à moins que chaque opération individuelle du groupe ne soit réussie. Si une opération dans la transaction échoue, la transaction entière échouera.

La transaction bancaire sera le meilleur exemple pour expliquer cela. Envisagez un transfert entre deux comptes. Pour ce faire, vous devez écrire des instructions SQL qui effectuent les opérations suivantes:

1. Vérifier la disponibilité du montant demandé dans le premier compte
2. Déduire le montant demandé du premier compte
3. Déposez-le dans un deuxième compte

Si quelqu'un de ce processus échoue, le tout devrait être rétabli à son état précédent.

ACID: Propriétés des transactions

Les transactions ont les quatre propriétés standard suivantes

• Atomicité: s'assure que toutes les opérations dans l'unité de travail sont terminées avec succès; sinon, la transaction est abandonnée au moment de la défaillance et les opérations précédentes sont restaurées dans leur état antérieur.
• Cohérence: garantit que la base de données modifie correctement les états lors d'une transaction correctement validée.
• Isolation: permet aux transactions de fonctionner indépendamment les unes des autres et de manière transparente.
• Durabilité: garantit que le résultat ou l'effet d'une transaction validée persiste en cas de défaillance du système.

Les transactions commencent par l'instruction START TRANSACTION ou BEGIN WORK et se terminent par une instruction COMMIT ou ROLLBACK . Les commandes SQL entre les instructions de début et de fin constituent le gros de la transaction.

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;

Avec START TRANSACTION , la validation automatique reste désactivée jusqu'à la fin de la transaction avec COMMIT ou ROLLBACK . Le mode autocommit revient alors à son état précédent.

Le FOR UPDATE indique (et verrouille) la ou les lignes pour la durée de la transaction.

Bien que la transaction ne soit pas validée, cette transaction ne sera pas disponible pour les autres utilisateurs.

Procédures générales impliquées dans la transaction

• Commencez la transaction en émettant la commande SQL BEGIN WORK ou START TRANSACTION .
• Exécutez toutes vos instructions SQL.
• Vérifiez si tout est exécuté selon vos besoins.
• Si oui, COMMIT commande COMMIT , sinon, ROLLBACK une commande ROLLBACK pour revenir à l'état précédent.
• Vérifiez les erreurs même après COMMIT si vous utilisez, ou pourriez éventuellement utiliser, Galera / PXC.

COMMIT, ROLLBACK et AUTOCOMMIT

AUTOCOMMIT

MySQL valide automatiquement les instructions qui ne font pas partie d'une transaction. Les résultats de toute UPDATE , DELETE ou INSERT non précédée d'un BEGIN ou d'un START TRANSACTION seront immédiatement visibles pour toutes les connexions.

La variable AUTOCOMMIT est définie sur true par défaut. Cela peut être changé de la manière suivante,

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

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

Pour afficher le statut AUTOCOMMIT

SELECT @@autocommit;

COMMETTRE

Si AUTOCOMMIT défini sur false et que la transaction n'est pas validée, les modifications ne seront visibles que pour la connexion en cours.

Une fois que l'instruction COMMIT validé les modifications apportées à la table, le résultat sera visible pour toutes les connexions.

Nous considérons deux connexions pour expliquer cela

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

Connexion 2

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

Connexion 1

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

Connexion 2

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

ROLLBACK

Si quelque chose a mal tourné dans l'exécution de votre requête, ROLLBACK utilisé pour annuler les modifications. Voir l'explication ci-dessous

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

Maintenant, nous exécutons ROLLBACK

--->Rollback executed now
mysql> ROLLBACk;

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

Une fois que COMMIT est exécuté, alors ROLLBACK ne causera rien

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

Si AUTOCOMMIT est défini sur true , COMMIT et ROLLBACK sont inutiles

Transaction à l'aide du pilote JDBC

La transaction utilisant le pilote JDBC est utilisée pour contrôler quand et comment une transaction doit être validée et annulée. La connexion au serveur MySQL est créée à l'aide du pilote JDBC

Le pilote JDBC pour MySQL peut être téléchargé ici

Commençons par obtenir une connexion à la base de données à l'aide du pilote 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");

Jeux de caractères : indique le jeu de caractères que le client utilisera pour envoyer des instructions SQL au serveur. Il spécifie également le jeu de caractères que le serveur doit utiliser pour renvoyer les résultats au client.

Cela doit être mentionné lors de la création de la connexion au serveur. Donc, la chaîne de connexion devrait être comme,

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

Voir ceci pour plus de détails sur les jeux de caractères et les assemblages

Lorsque vous ouvrez une connexion, le mode AUTOCOMMIT est défini sur true par défaut, il doit être modifié sur false pour démarrer la transaction.

con.setAutoCommit(false);

Vous devez toujours appeler la méthode setAutoCommit() juste après avoir ouvert une connexion.

Sinon, utilisez START TRANSACTION ou BEGIN WORK pour lancer une nouvelle transaction. En utilisant START TRANSACTION ou BEGIN WORK , pas besoin de changer AUTOCOMMIT false . Cela sera automatiquement désactivé.

Maintenant, vous pouvez commencer la transaction. Voir un exemple de transaction JDBC complet ci-dessous.

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 transaction JDBC vérifie que toutes les instructions SQL d'un bloc de transaction ont été exécutées avec succès, si l'une des instructions SQL du bloc de transaction a échoué, annulez et annulez tout dans le bloc de transaction.