MySQL
Transacción

Iniciar Transacción

Una transacción es un grupo secuencial de sentencias SQL, como seleccionar, insertar, actualizar o eliminar, que se realiza como una sola unidad de trabajo.

En otras palabras, una transacción nunca se completará a menos que cada operación individual dentro del grupo tenga éxito. Si falla alguna operación dentro de la transacción, la transacción completa fallará.

La transacción bancaria será el mejor ejemplo para explicar esto. Considere una transferencia entre dos cuentas. Para lograr esto tienes que escribir sentencias SQL que hagan lo siguiente

1. Verifique la disponibilidad del monto solicitado en la primera cuenta.
2. Deducir cantidad solicitada de la primera cuenta
3. Depositarlo en segunda cuenta

Si alguien falla este proceso, el conjunto debe revertirse a su estado anterior.

ACID: Propiedades de las transacciones

Las transacciones tienen las siguientes cuatro propiedades estándar

• Atomicidad: asegura que todas las operaciones dentro de la unidad de trabajo se completen con éxito; de lo contrario, la transacción se cancela en el punto de falla y las operaciones anteriores se devuelven a su estado anterior.
• Coherencia: garantiza que la base de datos cambie correctamente los estados en una transacción confirmada con éxito.
• Aislamiento: permite que las transacciones operen independientemente y transparentes entre sí.
• Durabilidad: asegura que el resultado o efecto de una transacción confirmada persista en caso de una falla del sistema.

Las transacciones comienzan con la instrucción START TRANSACTION o BEGIN WORK y terminan con una ROLLBACK COMMIT o ROLLBACK . Los comandos SQL entre las instrucciones de inicio y finalización forman la mayor parte de la transacción.

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 , la confirmación automática permanece deshabilitada hasta que finalice la transacción con COMMIT o ROLLBACK . El modo de confirmación automática vuelve a su estado anterior.

La FOR UPDATE indica (y bloquea) la (s) fila (s) durante la transacción.

Si bien la transacción permanece sin confirmar, esta transacción no estará disponible para otros usuarios.

Procedimientos generales involucrados en la transacción.

• Comience la transacción emitiendo el comando SQL BEGIN WORK o START TRANSACTION .
• Ejecute todas sus declaraciones SQL.
• Compruebe si todo se ejecuta de acuerdo a sus requerimientos.
• Si es así, emita el comando COMMIT ; de lo contrario, emita un comando ROLLBACK para revertir todo al estado anterior.
• Compruebe si hay errores incluso después de COMMIT si está utilizando, o podría usar, Galera / PXC.

COMPROMISO, ROLLBACK y AUTOCOMMIT

AUTOCOMIT

MySQL automáticamente confirma declaraciones que no son parte de una transacción. Los resultados de cualquier UPDATE , DELETE o INSERT no precedida por BEGIN o START TRANSACTION serán inmediatamente visibles para todas las conexiones.

La variable AUTOCOMMIT se establece como verdadera por defecto. Esto se puede cambiar de la siguiente manera,

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

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

Para ver AUTOCOMMIT estado de AUTOCOMMIT

SELECT @@autocommit;

COMETER

Si AUTOCOMMIT establece en falso y la transacción no se confirma, los cambios serán visibles solo para la conexión actual.

Después de que la instrucción COMMIT confirme los cambios en la tabla, el resultado será visible para todas las conexiones.

Consideramos dos conexiones para explicar esto.

Conexión 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 |
+-----+

Conexión 2

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

Conexión 1

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

Conexión 2

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

RETROCEDER

Si algo salió mal en la ejecución de su consulta, ROLLBACK utiliza para revertir los cambios. Vea la explicación a continuación.

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

Ahora estamos ejecutando ROLLBACK

--->Rollback executed now
mysql> ROLLBACk;

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

Una vez que se ejecuta COMMIT , ROLLBACK no causará nada

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 se establece en verdadero , COMMIT y ROLLBACK son inútiles

Transacción utilizando el controlador JDBC

La transacción que utiliza el controlador JDBC se utiliza para controlar cómo y cuándo se debe confirmar y deshacer una transacción. La conexión al servidor MySQL se crea utilizando el controlador JDBC

El controlador JDBC para MySQL se puede descargar aquí

Comencemos por obtener una conexión a la base de datos utilizando el controlador 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");

Conjuntos de caracteres : Esto indica qué conjunto de caracteres utilizará el cliente para enviar declaraciones SQL al servidor. También especifica el conjunto de caracteres que el servidor debe usar para enviar los resultados al cliente.

Esto debe mencionarse al crear la conexión al servidor. Así que la cadena de conexión debe ser como,

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

Vea esto para obtener más detalles sobre conjuntos de caracteres y colaciones.

Cuando abre la conexión, el modo AUTOCOMMIT se establece en verdadero de forma predeterminada, que debe cambiarse como falso para iniciar la transacción.

con.setAutoCommit(false);

Siempre debe llamar setAutoCommit() método setAutoCommit() justo después de abrir una conexión.

De lo contrario, use START TRANSACTION o BEGIN WORK para iniciar una nueva transacción. Al utilizar START TRANSACTION o BEGIN WORK , no es necesario cambiar AUTOCOMMIT false . Eso se desactivará automáticamente.

Ahora puedes iniciar la transacción. Vea un ejemplo completo de transacción JDBC a continuación.

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 transacción JDBC se asegura de que todas las sentencias de SQL dentro de un bloque de transacción se ejecuten correctamente, si una de las sentencias de SQL dentro del bloque de transacción falla, anule y deshaga todo dentro del bloque de transacción.