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

はじめに
1. 環境
  1. テストDB
2つのレコードをordersに挿入するテスト
1. 素のSQL（mysql CLI で実行）
2. CakePHP内ステートメントオブジェクトのSQL文内で実行
  1. 上記 1-5 同様のSQLを実行

はじめに

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

（本記事は書きかけで随時更新しております。）

環境

Xserver Standard plan上

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

Version 10.5.22-MariaDB-log

tx_isolation REPEATABLE-READ (MariaDBのデフォルトの分離レベル）

PHP8.3.10

CakePHP5.1 ※ デフォルトで PDO::ATTR_PERSISTENT => true となっている。

テスト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. 2行挿入。すべて正しい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. 2行挿入。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. 2行挿入。正しい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. 2行挿入。2行目はステートメントエラーとなる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 全ROLLBACK

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 と同じ結果

1.5へ続く。