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

この記事は約12分で読めます。

はじめに

トランザクションについて教科書的な話は理解しているものの、MariaDB+PHP+CakePHP5での動きをもっと理解したいと思い、いろいろテストしてみた。理解と異なる点もあったので随時メモしていく。
本記事はわかっている人には特に目新しいものではないと思う。

目的

分離レベルやロック等の実験もしたいが、まずは単純に、commit/rollbackの動きをいろんなパターンで確認する。

環境

Xserver Standard plan上

MariaDB. ※Xserverは、サーバーにもよるが、PHP,MariaDBは同じサーバー上にある。

PHP8.3.10

CakePHP5.1

テストDB

何でもよいが、CakePHPの命名規約に合わせてある。

-- 注文テーブル
CREATE TABLE `orders` (
  `id` int(11) NOT NULL,
  `order_name` varchar(100) NOT NULL COMMENT '注文名',
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='注文トランザクションテスト用';
ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `orders`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

-- 注文詳細テーブル
CREATE TABLE `order_details` (
  `id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL COMMENT '注文番号',
  `sub_order_name` varchar(100) DEFAULT NULL COMMENT 'サブ注文名',
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='注文明細トランザクションテスト用';
ALTER TABLE `order_details`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `order_details`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

2つのレコードをordersに挿入するテスト

素のSQL（mysql CLI で実行）

1. 正しいSQL 最後はrollback

-- truncate table 済み
-- SQL文に間違いなし
MariaDB [devdb]> BEGIN;INSERT INTO `orders` (`order_name`) VALUES ('name1');INSERT INTO `orders` (`order_name`) VALUES ('name2');ROLLBACK;
Query OK, 0 rows affected (0.000 sec)
Query OK, 1 row affected (0.004 sec)
Query OK, 1 row affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
MariaDB [devdb]> select * from orders;
Empty set (0.000 sec)

結果：正常

2. ステートメントエラーとなるSQL 最後はrollback

-- truncate table 済み
-- SQL文（フィールド名）に間違い
MariaDB [devdb]> BEGIN;INSERT INTO `orders` (`order_name`) VALUES ('name1');INSERT INTO `orders` (`order_names`) VALUES ('name2');ROLLBACK;
Query OK, 0 rows affected (0.000 sec)
Query OK, 1 row affected (0.003 sec)
ERROR 1054 (42S22): Unknown column 'order_names' in 'field list'
Query OK, 0 rows affected (0.000 sec)
MariaDB [devdb]> select * from orders;
Empty set (0.000 sec)

結果：正常

3.正しいSQL 最後はcommit

MariaDB [devdb]> BEGIN;INSERT INTO `orders` (`order_name`) VALUES ('name1');INSERT INTO `orders` (`order_name`) VALUES ('name2');commit;
Query OK, 0 rows affected (0.000 sec)
Query OK, 1 row affected (0.000 sec)
Query OK, 1 row affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)

MariaDB [devdb]> select * from orders;
+----+------------+---------+----------+
| id | order_name | created | modified |
+----+------------+---------+----------+
|  1 | name1      | NULL    | NULL     |
|  2 | name2      | NULL    | NULL     |
+----+------------+---------+----------+
2 rows in set (0.001 sec)

結果：正常

4. ステートメントエラーとなるSQL 最後はcommit

MariaDB [devdb]> BEGIN;INSERT INTO `orders` (`order_name`) VALUES ('name1');INSERT INTO `orders` (`order_names`) VALUES ('name2');commit;
Query OK, 0 rows affected (0.000 sec)
Query OK, 1 row affected (0.007 sec)
ERROR 1054 (42S22): Unknown column 'order_names' in 'field list'
Query OK, 0 rows affected (0.000 sec)

MariaDB [devdb]> select * from orders;
+----+------------+---------+----------+
| id | order_name | created | modified |
+----+------------+---------+----------+
|  1 | name1      | NULL    | NULL     |
+----+------------+---------+----------+
1 row in set (0.000 sec)

結果：エラーとならなかった行はcommitされる

5. 入れ子となるトランザクション内でステートメントエラーとなるSQL

MariaDB [devdb]> BEGIN;INSERT INTO `orders` (`order_name`) VALUES ('name1');BEGIN;INSERT INTO `order_details` (`order_id`,`sub_order_names`) VALUES ('1','sub_name1');ROLLBACK;ROLLBACK;
Query OK, 0 rows affected (0.000 sec)
Query OK, 1 row affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
ERROR 1054 (42S22): Unknown column 'sub_order_names' in 'field list'
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)

MariaDB [devdb]> select * from orders;
+----+------------+---------+----------+
| id | order_name | created | modified |
+----+------------+---------+----------+
|  1 | name1      | NULL    | NULL     |
+----+------------+---------+----------+
1 row in set (0.000 sec)

MariaDB [devdb]> select * from order_details;
Empty set (0.000 sec)

結果：mariaDBで入れ子はできない。次のbeginでコミットされrollbackは無意味となる。SAVEPOINTを使う。

CakePHP内ステートメントオブジェクト のSQL文内で実行

CakePHP のcake コマンドを拡張するCommandオブジェクトを使ってCLIで実施。

上記 1-5 同様のSQLを実行

/** @var Cake\Database\Connection */
$connection = ConnectionManager::get('default');
$ordersTable = $this->fetchTable('Orders');
$ret = $connection->execute( 'truncate table orders;' );
// 以下、execute で直接SQLを実行。

結果：mysql CLI と同じ結果

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

わざとステートメントエラーとなる。SQL エラーとなったらrollbackする。

// $this->io は、ConsoleIo $io のコピー     
        $this->io->info("テスト 開始");
        $this->io->out("トランザクション内で2レコードを挿入する。\n2レコード目でわざと失敗させる。\n最初にtruncate table 実行する。");
        $this->io->hr();
        /** @var Cake\Database\Connection */
        $connection = ConnectionManager::get('default');
        $ordersTable = $this->fetchTable('Orders');

        $ret = $connection->execute( 'truncate table orders;' );
 
        $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();
        $rowCount = $ordersTable->find()->count();
        $this->io->warning('Ordersのレコード数：'.$rowCount);
        if ( $success){
            $this->io->warning('db insert は成功→テスト失敗');
        }else{
            $this->io->warning('db insert は失敗→テスト成功');
        }
実行結果
> 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
db insert は失敗→テスト成功

結果：正常

もし、catch 節の、$connection->rollback(); を消すと、オートコミットとなり、正常な一行目はコミットされる。

タイトルとURLをコピーしました