MySQL => トランザクション

トランザクションの開始

トランザクションは、select、insert、update、deleteなどのSQL文のシーケンシャルなグループであり、単一のワークユニットとして実行されます。

つまり、グループ内の個々の操作が成功しない限り、トランザクションは完了しません。トランザクション内で操作が失敗すると、トランザクション全体が失敗します。

これを説明するための最良の例は、銀行取引です。 2つの口座間の振替を検討してください。これを実現するには、次のことを行うSQL文を記述する必要があります

最初のアカウントでリクエストされた金額の利用可能性を確認する
最初のアカウントから請求額を控除してください
それを2番目のアカウントに入金する

これらのプロセスが失敗した場合は、全体を元の状態に戻す必要があります。

ACID：トランザクションのプロパティ

トランザクションには次の4つの標準プロパティがあります

アトミック性：作業単位内のすべての操作が正常に完了したことを確認します。それ以外の場合、トランザクションは障害発生時に中止され、以前の操作は元の状態にロールバックされます。
整合性：トランザクションが正常にコミットされたときに、データベースが状態を適切に変更するようにします。
分離：トランザクションを互いに独立して、相互に透過的に動作させることができます。
耐久性：システムに障害が発生した場合でも、コミットされたトランザクションの結果または効果が確実に維持されます。

トランザクションは文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してトランザクションを終了するまで、自動コミットは無効のままROLLBACK 。自動コミットモードは、それまでの状態に戻ります。

FOR UPDATEは、トランザクションの期間中の行を示します（およびロックします）。

トランザクションは未コミットのままですが、このトランザクションは他のユーザーには利用できません。

取引に関わる一般的な手続き

SQLコマンドBEGIN WORKまたはSTART TRANSACTION発行して、トランザクションを開始します。
すべてのSQL文を実行します。
あなたの要件にしたがってすべてが実行されているかどうかを確認します。
はいの場合はCOMMITコマンドを発行し、それ以外の場合はすべてを元の状態に戻すROLLBACKコマンドを発行しROLLBACK 。
Galera / PXCを使用している、または最終的に使用している可能性がある場合は、 COMMIT後もエラーをチェックしてください。

COMMIT、ROLLBACKおよびAUTOCOMMIT

AUTOCOMMIT

MySQLは、トランザクションの一部ではない文を自動的にコミットします。 BEGINまたはSTART TRANSACTIONが前に付いていないUPDATE 、 DELETEまたはINSERTステートメントの結果は、すべての接続で直ちに表示されます。

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;

コミット

AUTOCOMMITがfalseに設定され、トランザクションがコミットされていない場合、変更は現在の接続に対してのみ表示されます。

COMMIT文がテーブルへの変更をコミットすると、結果はすべての接続で表示されます。

これを説明する2つの接続を考えます

接続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を使用して変更を元に戻します。以下の説明を参照してください

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

--->Rollback executed now
mysql> ROLLBACk;

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

COMMITが実行されると、 ROLLBACKは何も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がtrueに設定されている場合、 COMMITとROLLBACKは役に立たない

JDBCドライバを使用したトランザクション

JDBCドライバを使用したトランザクションは、トランザクションのコミットとロールバックの方法とタイミングを制御するために使用されます。 MySQLサーバへの接続は、JDBCドライバを使用して作成されます。

MySQL用JDBCドライバはここからダウンロードできます

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&amp;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&amp;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文が正常に実行されたことを確認し、トランザクションブロック内のすべてを中止し、ロールバックします。

Modified text is an extract of the original Stack Overflow Documentation

ライセンスを受けた CC BY-SA 3.0

所属していない Stack Overflow

MySQL
トランザクション

サーチ…

トランザクションの開始

COMMIT、ROLLBACKおよびAUTOCOMMIT

JDBCドライバを使用したトランザクション