MySQL
Transactie

Start transactie

Een transactie is een opeenvolgende groep SQL-instructies zoals selecteren, invoegen, bijwerken of verwijderen, die wordt uitgevoerd als één enkele werkeenheid.

Met andere woorden, een transactie zal nooit voltooid zijn tenzij elke afzonderlijke operatie binnen de groep succesvol is. Als een bewerking binnen de transactie mislukt, mislukt de hele transactie.

Een banktransactie is hiervan het beste voorbeeld. Overweeg een overdracht tussen twee accounts. Om dit te bereiken, moet u SQL-instructies schrijven die het volgende doen

1. Controleer de beschikbaarheid van het gevraagde bedrag op de eerste rekening
2. Gevraagd bedrag van de eerste rekening aftrekken
3. Stort het op een tweede account

Als iemand dit proces faalt, moet het geheel worden teruggezet naar zijn vorige staat.

ZUUR: Eigenschappen van transacties

Transacties hebben de volgende vier standaardeigenschappen

• Atomiciteit: zorgt ervoor dat alle bewerkingen binnen de werkeenheid met succes worden voltooid; anders wordt de transactie afgebroken op het punt van mislukking en worden eerdere bewerkingen teruggezet naar hun vorige staat.
• Consistentie: zorgt ervoor dat de database de status correct wijzigt bij een succesvol vastgelegde transactie.
• Isolatie: zorgt ervoor dat transacties onafhankelijk en transparant voor elkaar kunnen worden uitgevoerd.
• Duurzaamheid: zorgt ervoor dat het resultaat of effect van een gecommitteerde transactie blijft bestaan in geval van een systeemstoring.

Transacties beginnen met de instructie START TRANSACTION of BEGIN WORK en eindigen met een COMMIT of een ROLLBACK instructie. De SQL-opdrachten tussen de begin- en eindinstructies vormen het grootste deel van de transactie.

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;

Met START TRANSACTION blijft autocommit uitgeschakeld totdat u de transactie beëindigt met COMMIT of ROLLBACK . De autocommit-modus keert vervolgens terug naar de vorige status.

De FOR UPDATE geeft (en vergrendelt) de rij (en) voor de duur van de transactie.

Hoewel de transactie niet is vastgelegd, is deze transactie niet beschikbaar voor andere gebruikers.

Algemene procedures betrokken bij transactie

• Begin de transactie met het SQL-commando BEGIN WORK of START TRANSACTION .
• Voer al uw SQL-instructies uit.
• Controleer of alles volgens uw vereiste wordt uitgevoerd.
• Zo ja, COMMIT dan COMMIT commando, anders een ROLLBACK commando om alles terug te zetten naar de vorige status.
• Controleer op fouten, zelfs na COMMIT als u Galera / PXC gebruikt of eventueel gebruikt.

COMMIT, ROLLBACK en AUTOCOMMIT

autocommit

MySQL legt automatisch verklaringen af die geen deel uitmaken van een transactie. De resultaten van een UPDATE , DELETE of INSERT instructie die niet is voorafgegaan door een BEGIN of START TRANSACTION zijn onmiddellijk zichtbaar voor alle verbindingen.

De variabele AUTOCOMMIT is standaard ingesteld op true . Dit kan op de volgende manier worden gewijzigd,

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

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

Om de AUTOCOMMIT status te bekijken

SELECT @@autocommit;

COMMIT

Als AUTOCOMMIT ingesteld op false en de transactie niet is vastgelegd, zijn de wijzigingen alleen zichtbaar voor de huidige verbinding.

Nadat de COMMIT opdracht de wijzigingen in de tabel heeft doorgevoerd, is het resultaat zichtbaar voor alle verbindingen.

We beschouwen twee verbindingen om dit uit te leggen

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

Verbinding 2

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

Verbinding 1

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

Verbinding 2

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

TERUGROLLEN

Als er iets mis is gegaan in de uitvoering van uw zoekopdracht, wordt ROLLBACK gebruikt om de wijzigingen ROLLBACK te maken. Zie onderstaande uitleg

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

Nu voeren we ROLLBACK

--->Rollback executed now
mysql> ROLLBACk;

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

Nadat COMMIT is uitgevoerd, zal ROLLBACK niets meer veroorzaken

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

Als AUTOCOMMIT is ingesteld op true , zijn COMMIT en ROLLBACK nutteloos

Transactie met JDBC-stuurprogramma

Transactie met het JDBC-stuurprogramma wordt gebruikt om te bepalen hoe en wanneer een transactie moet worden vastgelegd en teruggedraaid. Verbinding met MySQL-server wordt gemaakt met behulp van JDBC-stuurprogramma

JDBC-stuurprogramma voor MySQL kan hier worden gedownload

Laten we beginnen met het verkrijgen van een verbinding met de database met behulp van het JDBC-stuurprogramma

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

Tekensets : dit geeft aan welke tekenset de client gebruikt om SQL-instructies naar de server te verzenden. Het specificeert ook de tekenset die de server moet gebruiken voor het terugsturen van resultaten naar de client.

Dit moet worden vermeld tijdens het maken van de verbinding met de server. Dus de verbindingsreeks zou er zo uit moeten zien,

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

Zie dit voor meer informatie over tekensets en verzamelingen

Wanneer u de verbinding opent, is de modus AUTOCOMMIT standaard ingesteld op true , die moet worden gewijzigd in false om de transactie te starten.

con.setAutoCommit(false);

U moet altijd de methode setAutoCommit() aanroepen direct nadat u een verbinding hebt geopend.

Gebruik anders START TRANSACTION of BEGIN WORK om een nieuwe transactie te starten. Door START TRANSACTION of BEGIN WORK , hoeft AUTOCOMMIT false niet te worden gewijzigd. Dat wordt automatisch uitgeschakeld.

Nu kunt u de transactie starten. Bekijk hieronder een compleet JDBC-transactievoorbeeld.

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

JDBC-transactie zorgt ervoor dat alle SQL-instructies in een transactieblok succesvol worden uitgevoerd. Als een van de SQL-instructies in het transactieblok is mislukt, wordt alles binnen het transactieblok afgebroken en teruggedraaid.