【Mysql | MariaDB】DDL暗黙的コミット知らなくて事故った話

TL;DR

以下の対応するDBはCREATE文で暗黙的にCOMMITされる

データベース	DDLの暗黙的コミット
MySQL	はい
MariaDB	はい
PostgreSQL	いいえ
Oracle	はい

mariadb

BEGIN;

-- テーブルの作成（DDL）
CREATE TABLE example (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
-- ここでDDLが自動的にコミットされ、トランザクションが分割される
-- この時点でROLLBACKを実行してもテーブル作成は取り消されない

-- データの挿入（DML）
INSERT INTO example (id, name) VALUES (1, 'Test');
-- トランザクションが分割されているのでROLLBACKは効かない

ROLLBACK;

問題があった処理

複数テーブルへのデータ投入する処理を作っていました。
結果的に間違っていますが、想定していたフローは以下です。

実際の処理は以下のようなコードなのですがもっと入り組んでいたので、PHPっぽい疑似言語で書きます。
それっぽく見てもらえると。

try {
    // トランザクション開始
    begin();

    /** @var string $table テーブル名  */
    foreach($tables as $table){
        // 同構造のテーブルを"bk_"というprefixをつけてバックアップ用として作成
        $bkTabkeName = "bk_" . $table;
        $bkCreateTableSql = sprintf("
        CREATE TABLE %s LIKE %s"
        , $bkTabkeName
        , $table);
        
        // CREATE文実行
        execute($bkCreateTableSql);
    
        // バックアップ元から全件取得
        $fetchSql = sprintf("SELECT * FROM %s", $table);
        $records = execute($fetchSql);

        // 元のテーブルを全件削除
        $deleteSql = sprintf("DELETE FROM %s", $table);
        execute($deleteSql);

        // バックアップテーブルに新規登録
        $bkInsertSql = sprintf("
        INSERT INTO %s (%s) VALUES %s"
        , $bkTabkeName
        , {対象カラム}
        , implode(', ', $records));
        execute($bkInsertSql);
    }
    commit();
} catch {
    rollback();
}

パッと見大丈夫そうな空気がプンプンします。
ですが致命的な間違いを犯していました。

CREATEの段階で暗黙的COMMITされていた

mysqlとmariadbはDDLの時点で暗黙的COMMITされます。
それを考慮すると前述のフローチャートは実際には以下のようになります。

はい。もうめちゃくちゃです。
CREATEの段階で明示的なBEGINは終了しており、そのあとの処理はROLLBACKができない処理になります。
ですので以下のように処理を分離して対処しました。

/** @var string $table テーブル名  */
foreach($tables as $table){
    // 同構造のテーブルを"bk_"というprefixをつけてバックアップ用として作成
    $bkTabkeName = "bk_" . $table;
    $bkCreateTableSql = sprintf("
    CREATE TABLE %s LIKE %s"
    , $bkTabkeName
    , $table);
    
    // CREATE文実行
    execute($bkCreateTableSql);
}
    
try {
    // トランザクション開始
    begin();
        
    foreach($tables as $table){
        $bkTabkeName = "bk_" . $table;
        
        // バックアップ元から全件取得
        $fetchSql = sprintf("SELECT * FROM %s", $bkTabkeName);
        $records = execute($fetchSql);

        // 元のテーブルを全件削除
        $deleteSql = sprintf("DELETE FROM %s", $table);
        execute($deleteSql);

        // バックアップテーブルに新規登録
        $bkInsertSql = sprintf("
        INSERT INTO %s (%s) VALUES %s"
        , $bkTabkeName
        , {対象カラム}
        , implode(', ', $records));
        execute($bkInsertSql);
    }
    commit();
} catch {
    rollback();
}

DDLの暗黙的COMMITには気をつけよう

今回はCREATEだったので”まだ”影響は少なかったですがTRUNCATEだと悲惨なことになりそうです。

-- DMLなのでROLLBACKされる
BEGIN;

DELETE FROM example;

ROLLBACK;

-- DDLなのでROLLBACKされない
BEGIN;

TRUNCATE TABLE example;

ROLLBACK ;

実行するSQLがDDLなのか、DMLなのかはしっかり理解しないと笑えない状況に陥ってしまう可能性ありけりですね。

参考