MariaDB とCakePHP5でのトランザクションの実験(1.5)

※1のつづき

ConnectionManager のbeginを利用してトランザクションを開始し、execute()メソッドを使用

トランザクション内で2レコードを挿入する。強制的にロールバックする

// $this->io は、ConsoleIo $io のコピー     
        $this->io->out("トランザクション内で2レコードを挿入する。強制的にロールバックする\n最初にtruncate table 実行する。");
        $this->io->hr();
        /** @var Cake\Database\Connection */
        $connection = ConnectionManager::get('default');
        $sql = 'truncate table orders;';
        $ret = $connection->execute( $sql );
        $connection->begin();
        $success = false;
        try{
            $this->io->out("ordersに挿入（成功SQL実行）");
            $ret = $connection->execute(
                'INSERT into orders (order_name) values(?)',
                ["name1"]
            );
            // わざと失敗
            $this->io->out("ordersに挿入（成功SQL実行）");
            $ret = $connection->execute(
                'INSERT into orders (order_name) values(?)',
                ["name2"]
            );

            $this->io->info("ロールバック発生");
            $connection->rollback();
            $success = true;
        }catch(Exception $ex){
            $this->io->error('Exception 発生');
            $this->io->error($ex->getMessage());
//            $connection->rollback();
            $success = false;
        }finally{
            $connection = null;
        }


        $this->io->hr();
        $ordersTable = $this->fetchTable('Orders');
        $rowCount = $ordersTable->find()->count();
        $this->io->warning('Ordersのレコード数：'.$rowCount);

結果：レコード数ゼロ。MariaDB CLIから見てもレコード数ゼロ。正常

２行挿入し、２行目でわざとステートメントエラーとなる。例外時にrollbackしない。

// $this->io は、ConsoleIo $io のコピー     
        $this->io->out("トランザクション内で2レコードを挿入する。\n2レコード目でわざと失敗させる。\n最初にtruncate table 実行する。");
        $this->io->hr();
        /** @var Cake\Database\Connection */
        $connection = ConnectionManager::get('default');
        $sql = 'truncate table orders;';
        $ret = $connection->execute( $sql );
        $connection->begin();
        $success = false;
        try{
            $this->io->out("ordersに挿入（成功SQL実行）");
            $ret = $connection->execute(
                'INSERT into orders (order_name) values(?)',
                ["name1"]
            );
            // わざと失敗
            $this->io->out("ordersに挿入（失敗SQL実行）");
            $ret = $connection->execute(
                'INSERT into orders (order_names) values(?)',
                ["name2"]
            );

            $connection->commit();
            $success = true;
        }catch(Exception $ex){
            $this->io->error('Exception 発生');
            $this->io->error($ex->getMessage());
//            $connection->rollback();
            $success = false;
        }finally{
            $connection = null;
        }

        $this->io->hr();
        $ordersTable = $this->fetchTable('Orders');
        $rowCount = $ordersTable->find()->count();
        $this->io->warning('Ordersのレコード数：'.$rowCount);

実行結果

> bin/cake transaction test
トランザクション内で2レコードを挿入する。
2レコード目でわざと失敗させる。
最初にtruncate table 実行する。
-------------------------------------------------------------------------------
ordersに挿入（成功SQL実行）
ordersに挿入（失敗SQL実行）
Exception 発生
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'order_names' in 'field list'
-------------------------------------------------------------------------------
Ordersのレコード数：1

-- 別コネクションから
MariaDB [dev]> select * from orders;
Empty set (0.000 sec)

結果：CakePHPでは成功SQLがあるように見えるが、実際のテーブルにレコードは無い。
これは、find()メソッドつまりselect文はトランザクション中だから。
commit()なしでPHP（PDO）が終了すると、自動的にロールバックされる。
https://www.php.net/manual/ja/pdo.transactions.php
MariaDB CLIでもcommitせずにquitすると同じ現象となる。
→CakePHPに限らないが、SQL Exceptionは必ずcatch して適切な処理を行う必要があるし、commitかrollbackを明示的に通るようにしておく。
上記テストメソッドでは、他メソッドから呼び出されている場合、トランザクションが予期できない動きとなる可能性が高い。
ちなみに、
}finally{
$connection = null;
}
は、局所変数をnullにしているだけでありコネクションが切断されるわけではない。

// test()から、test2()を呼び出した。   test2() は前項のメソッド
protected function test() : void {
        $this->io->info("test");
        /** @var Cake\Database\Connection */
        $connection = ConnectionManager::get('default');
        $this->io->info("test2 呼び出し");
        $this->test2();

        $this->io->hr();
        $ret = $connection->execute(
            'INSERT into orders (order_name) values(?)',
            ["name3"]
        );
        $ordersTable = $this->fetchTable('Orders');
        $rowCount = $ordersTable->find()->count();
        $this->io->warning('test: Ordersのレコード数：'.$rowCount);

    }

以下の様に、呼び出し側のSQLはトランザクション中のSQLとみなされ、ロールバックされてしまう。

[dev]$ bin/cake transaction test
トランザクションのテスト開始
test
test2 呼び出し
テスト2 開始
トランザクション内で2レコードを挿入する。
2レコード目でわざと失敗させる。
最初にtruncate table 実行する。
-------------------------------------------------------------------------------
ordersに挿入（成功SQL実行）
ordersに挿入（失敗SQL実行）
Exception 発生
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'order_names' in 'field list'
-------------------------------------------------------------------------------
Ordersのレコード数：1
-------------------------------------------------------------------------------
test: Ordersのレコード数：2  ← test2() 内の成功レコードとtest()の成功レコードが見える、が......


MariaDB [dev]> select * from orders;
Empty set (0.000 sec)

catch 節の、コメントアウトされている $connection->rollback(); を戻す。

> bin/cake transaction test
トランザクション内で2レコードを挿入する。
2レコード目でわざと失敗させる。
最初にtruncate table 実行する。
-------------------------------------------------------------------------------
ordersに挿入（成功SQL実行）
ordersに挿入（失敗SQL実行）
Exception 発生
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'order_names' in 'field list'
-------------------------------------------------------------------------------
Ordersのレコード数：0

結果：正常。MariaDB CLIから見てもレコード数ゼロ。

2へ続く。