MySQL
Transaktion

Starta transaktion

En transaktion är en sekventiell grupp av SQL-satser som att välja, infoga, uppdatera eller radera, som utförs som en enda arbetsenhet.

Med andra ord kommer en transaktion aldrig att slutföras såvida inte varje enskild operation inom gruppen lyckas. Om någon åtgärd inom transaktionen misslyckas misslyckas hela transaktionen.

Banktransaktion är det bästa exemplet för att förklara detta. Överväg en överföring mellan två konton. För att uppnå detta måste du skriva SQL-satser som gör följande

1. Kontrollera tillgängligheten för det begärda beloppet i det första kontot
2. Dra av begärt belopp från det första kontot
3. Sätt in det på andra kontot

Om någon av dessa processer misslyckas, bör hela återställas till sitt tidigare tillstånd.

ACID: Egenskaper för transaktioner

Transaktioner har följande fyra standardegenskaper

• Atomicity: säkerställer att alla operationer inom arbetsenheten är framgångsrika; I annat fall avbryts transaktionen vid ett misslyckande och tidigare operationer rullas tillbaka till sitt tidigare tillstånd.
• Konsekvens: säkerställer att databasen korrekt ändras tillstånd vid en framgångsrik begiven transaktion.
• Isolering: gör det möjligt för transaktioner att fungera oberoende av och öppna för varandra.
• Hållbarhet: säkerställer att resultatet eller effekten av en begiven transaktion kvarstår i händelse av ett systemfel.

Transaktioner börjar med uttalandet START TRANSACTION eller BEGIN WORK och slutar med antingen ett COMMIT eller ROLLBACK uttalande. SQL-kommandona mellan uttalanden från början och slut utgör huvuddelen av transaktionen.

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;

Med START TRANSACTION förblir START TRANSACTION inaktiverat tills du avslutar transaktionen med COMMIT eller ROLLBACK . Auto-commit-läget återgår sedan till sitt tidigare tillstånd.

FOR UPDATE indikerar (och låser) raden / raderna under transaktionens varaktighet.

Transaktionen förblir obefogad, men denna transaktion är inte tillgänglig för andra användare.

Allmänna förfaranden involverade i transaktioner

• Börja transaktionen genom att utfärda SQL-kommandot BEGIN WORK eller START TRANSACTION .
• Kör alla dina SQL-uttalanden.
• Kontrollera om allt utförs enligt ditt krav.
• Om ja, ge sedan COMMIT kommandot, annars utfärda ett ROLLBACK kommando för att återställa allt till föregående tillstånd.
• Kontrollera om det finns fel även efter COMMIT om du använder eller eventuellt använder Galera / PXC.

ÅTAGANDE, ROLLBACK och AUTOCOMMIT

AUTOCOMMIT

MySQL begår automatiskt uttalanden som inte ingår i en transaktion. Resultaten från alla UPDATE , DELETE eller INSERT satser som inte föregås av BEGIN eller START TRANSACTION kommer omedelbart att vara synliga för alla anslutningar.

AUTOCOMMIT variabeln ställs in som sann som standard. Detta kan ändras på följande sätt,

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

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

För att se AUTOCOMMIT status

SELECT @@autocommit;

BEGÅ

Om AUTOCOMMIT inställd på falsk och transaktionen inte har begåtts, kommer ändringarna att vara synliga endast för den aktuella anslutningen.

När COMMIT uttalandet har COMMIT sig ändringarna i tabellen kommer resultatet att vara synligt för alla anslutningar.

Vi överväger två kopplingar för att förklara detta

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

Anslutning 2

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

Anslutning 1

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

Anslutning 2

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

RULLA TILLBAKA

Om något gick fel i din ROLLBACK , ROLLBACK för att återställa ändringarna. Se förklaringen nedan

--->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 kör vi ROLLBACK

--->Rollback executed now
mysql> ROLLBACk;

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

När COMMIT har ROLLBACK kommer ROLLBACK inte att orsaka någonting

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

Om AUTOCOMMIT är inställda sant, då COMMIT och ROLLBACK är värdelös

Transaktion med JDBC Driver

Transaktion med JDBC-drivrutin används för att kontrollera hur och när en transaktion ska begås och återuppspelas. Anslutning till MySQL-server skapas med JDBC-drivrutinen

JDBC-drivrutin för MySQL kan laddas ner här

Låt oss börja med att få en anslutning till databasen med JDBC-drivrutinen

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

Teckenuppsättningar : Detta indikerar vilken teckenuppsättning klienten kommer att använda för att skicka SQL-satser till servern. Den specificerar också den teckenuppsättning som servern ska använda för att skicka resultat tillbaka till klienten.

Detta bör nämnas när du skapar anslutning till servern. Så anslutningssträngen ska vara,

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

Se detta för mer information om karaktärsatser och -samlingar

När du öppnar anslutningen är AUTOCOMMIT läget inställt till true som standard, vilket bör ändras falskt för att starta transaktionen.

con.setAutoCommit(false);

Du bör alltid ringa setAutoCommit() direkt efter att du har öppnat en anslutning.

START TRANSACTION eller BEGIN WORK att starta en ny transaktion. Genom att använda START TRANSACTION eller BEGIN WORK , behöver du inte ändra AUTOCOMMIT falskt . Det kommer att inaktiveras automatiskt.

Nu kan du starta transaktionen. Se ett komplett JDBC-transaktionsexempel nedan.

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-transaktioner se till att alla SQL-uttalanden i ett transaktionsblock utförs framgångsrikt, om någon av SQL-satserna inom transaktionsblocket misslyckas, avbryta och återuppgradera allt inom transaktionsblocket.