MySQL
Сделка

Начать транзакцию

Транзакция представляет собой последовательную группу операторов SQL, таких как выбор, вставка, обновление или удаление, которая выполняется как один рабочий блок.

Другими словами, транзакция никогда не будет полной, если каждая отдельная операция внутри группы не будет успешной. Если какая-либо операция в транзакции завершится с ошибкой, вся транзакция завершится неудачно.

Банковская сделка будет лучшим примером для объяснения этого. Рассмотрите возможность перехода между двумя учетными записями. Для этого вам нужно написать инструкции SQL, которые выполняют следующие

1. Проверьте доступность запрашиваемой суммы на первой учетной записи
2. Вычитайте запрашиваемую сумму с первой учетной записи
3. Внести его во вторую учетную запись

Если кто-то этот процесс терпит неудачу, целое должно быть возвращено в прежнее состояние.

ACID: свойства транзакций

Транзакции имеют следующие четыре стандартных свойства

• Атомарность: обеспечивает успешное завершение всех операций в рабочей единице; в противном случае транзакция прерывается в момент сбоя, а предыдущие операции возвращаются в прежнее состояние.
• Согласованность: гарантирует, что база данных правильно изменяет состояния при успешной транзакции.
• Изоляция: позволяет транзакциям работать независимо друг от друга и прозрачно.
• Долговечность: гарантирует, что результат или эффект совершенной транзакции сохранится в случае сбоя системы.

Транзакции начинаются с заявления START TRANSACTION или BEGIN WORK и заканчиваются либо заявлением COMMIT либо ROLLBACK . Команды SQL между инструкциями начала и окончания составляют основную часть транзакции.

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;

С помощью START TRANSACTION автокоммит остается отключенным до тех пор, пока вы не закончите транзакцию с помощью COMMIT или ROLLBACK . Затем режим автосохранения возвращается в прежнее состояние.

FOR UPDATE указывает (и блокирует) строку (строки) на время транзакции.

Хотя транзакция остается незафиксированной, эта транзакция будет недоступна для других пользователей.

Общие процедуры, связанные с транзакцией

• Начните транзакцию, выпустив команду SQL BEGIN WORK или START TRANSACTION .
• Запустите все ваши SQL-операторы.
• Проверьте, все ли выполнено в соответствии с вашим требованием.
• Если да, то выполните команду COMMIT , иначе выполните команду ROLLBACK чтобы вернуть все в предыдущее состояние.
• Проверьте ошибки даже после COMMIT если вы используете или можете в конечном итоге использовать Galera / PXC.

COMMIT, ROLLBACK и AUTOCOMMIT

AUTOCOMMIT

MySQL автоматически фиксирует операторы, которые не являются частью транзакции. Результаты любых UPDATE , DELETE или INSERT не предшествующих BEGIN или START TRANSACTION , сразу будут видны всем соединениям.

По AUTOCOMMIT переменной AUTOCOMMIT установлено значение true . Это можно изменить следующим образом,

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

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

Просмотр состояния AUTOCOMMIT

SELECT @@autocommit;

COMMIT

Если AUTOCOMMIT установлен в значение false и транзакция не выполнена, изменения будут видны только для текущего соединения.

После того, как инструкция COMMIT фиксирует изменения в таблице, результат будет видимым для всех подключений.

Мы рассматриваем две связи, чтобы объяснить это

Соединение 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 |
+-----+

Соединение 2

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

Соединение 1

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

Соединение 2

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

ROLLBACK

Если что-то пошло не так в выполнении запроса, ROLLBACK используется для отмены изменений. См. Объяснение ниже

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

Теперь мы выполняем ROLLBACK

--->Rollback executed now
mysql> ROLLBACk;

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

После выполнения COMMIT , ROLLBACK не вызовет ничего

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

Если значение AUTOCOMMIT установлено верно , то COMMIT и ROLLBACK бесполезны

Транзакция с использованием драйвера JDBC

Транзакция с использованием драйвера JDBC используется для контроля того, как и когда транзакция должна совершать транзакции и откатываться. Подключение к серверу MySQL создается с использованием драйвера JDBC

Драйвер JDBC для MySQL можно скачать здесь

Давайте начнем с подключения к базе данных с помощью драйвера 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");

Символьные наборы : это указывает, какой набор символов будет использоваться клиентом для отправки операторов SQL на сервер. Он также указывает набор символов, который сервер должен использовать для отправки результатов обратно клиенту.

Это следует упомянуть при создании соединения с сервером. Таким образом, строка подключения должна быть похожа,

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

См. Это для более подробной информации о наборах символов и коллаборациях

Когда вы открываете соединение, для режима AUTOCOMMIT по умолчанию установлено значение true , которое должно быть изменено на значение false, чтобы начать транзакцию.

con.setAutoCommit(false);

Вы всегда должны вызывать setAutoCommit() сразу после открытия соединения.

В противном случае используйте START TRANSACTION или BEGIN WORK чтобы начать новую транзакцию. Используя START TRANSACTION или BEGIN WORK , не нужно менять AUTOCOMMIT false . Это автоматически отключится.

Теперь вы можете начать транзакцию. См. Полный пример транзакции JDBC ниже.

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 гарантирует, что все операторы SQL в блоке транзакции выполнены успешно, если какой-либо из SQL-оператора в блоке транзакции не удался, отменил и отменил все операции внутри блока транзакций.