SE_BOKUのまとめノート的ブログ

SE_BOKUが知ってること・勉強したこと・考えたことetc

トップ > PG:WEB・PHP > PHP7のPDOを使ってMariaDB（MySQL）の暗黙的なコミットの動きを確かめる

PHP7のPDOを使ってMariaDB（MySQL）の暗黙的なコミットの動きを確かめる

PG:WEB・PHP MariaDB・SQL

目次

PHPのPDOを使ってMariaDB（MySQL）の暗黙的なコミットの動きを確かめる

MariaDB（MySQL）で時々「暗黙的なコミット」が話題になります。

mariadb.com

暗黙的なコミットとは、トランザクションの途中でDDL文があると、その時点までに実行されていた処理をコミットしてからDDL文が実行され、そのDDL文も自動コミットされてしまうという動きのことです。

言葉だけでは、わかりづらい点があるので、PHPを使って、動きを確認してみます。

トランザクションを使うので、PDOを使います。

www.php.net

PDOは「PHP Data Objects」略で、データベースの違いを吸収し、DBを変更しても同じ関数でアクセスできるようにしてくれます。

PDOは、php.iniで「extension=pdo_mysql」のコメントを外して使います。

動作確認している環境は「PHP7.2」です。

PHPのバージョンが違うと、動きや結果が違う場合もあるかもしれませんので、その点留意してみてください。

 

確かめる準備と方法

僕がテスト用に使っているDBに、以下の確認用テーブルを作ります、

CREATE TABLE `test_a` (
    `int_id` INT(11) NOT NULL AUTO_INCREMENT,
    `str_a_name` VARCHAR(255),
    PRIMARY KEY (`int_id`)
);

最初に以下の2行だけデータを登録してます。

int_id:1 str_a_name:名前Aの１
int_id:2 str_a_name:名前Aの２

あと、ソースをごちゃごちゃさせないよう、PDOを継承してDB接続する部分だけ切り出したクラス(class_testdb.php)を作成して、それをサンプルでは使ってます。

<?php
class TestDb extends PDO
{
     const DSN = "mysql:host=127.0.0.1;dbname=bltdb;charset=utf8mb4";
     const USER = 'bltuser';
     const PASSWORD = 'bltpass';
     public function __construct()
    {
        parent::__construct(self::DSN,self::USER,self::PASSWORD);
        $this->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
}
?>

 

これを使って以下を順番に確認していきます。

 

ケース１：トランザクション内にDDLを含まない場合

暗黙的コミットは発生しないパターンです。

deleteして、insertを2件やってますが、rollback()してます。

<?php
    require "class_testdb.php";
    try {
        $dbh = new TestDb();
        $dbh->beginTransaction();
        $sth = $dbh->exec("delete from test_a;");
        $sth = $dbh->exec("insert into test_a values(0,'インサート１','インサート１','インサート１');");
        $sth = $dbh->exec("insert into test_a values(0,'インサート２','インサート２','インサート２');");
        $dbh->rollBack();  
        
        $sql = 'select int_id, str_a_name from test_a;';
        $stmt = $dbh->query($sql);
        while($result = $stmt->fetch(PDO::FETCH_ASSOC)){
            print('int_id:'.$result['int_id'].' str_a_name:' .$result['str_a_name'].'<br>');
        }
   } catch (PDOException $e) {
        printf("[Error] %s¥n", $e->getMessage());
   }
?>

当然ながら、ロールバック後のSELECTで元のデータがそのまま取得されます。

int_id:1 str_a_name:名前Aの１
int_id:2 str_a_name:名前Aの２

 

ケース２：トランザクション内にDDLを含むが、テンポラリの場合

DDL文がはいっても、暗黙的なコミットが発生しないパターンです。

<?php
    require "class_testdb.php";
    try {
        $dbh = new TestDb();
        $dbh->beginTransaction();
        $sth = $dbh->exec("delete from test_a;");
        $sth = $dbh->exec("insert into test_a values(0,'インサート１','インサート１','インサート１');");
		// ****** テンポラリテーブルをcreate
        $sql = "create temporary table tmp_test (
            id INT(11) AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(20)
        ) engine=memory;";
        
        $res = $dbh->query($sql);
        // *************************
        $sth = $dbh->exec("insert into test_a values(0,'インサート２','インサート２','インサート２');");
        $dbh->rollBack();  
        
        $sql = 'select int_id, str_a_name from test_a;';
        $stmt = $dbh->query($sql);
        while($result = $stmt->fetch(PDO::FETCH_ASSOC)){
            print('int_id:'.$result['int_id'].' str_a_name:' .$result['str_a_name'].'<br>');
        }
   } catch (PDOException $e) {
        printf("[Error] %s¥n", $e->getMessage());
   }
?>

create temporary table文を間に挟み込みます。 

MariaDBのドキュメントにも「CREATE TABLE および DROP TABLE で TEMPORARY キーワードが使用された場合、暗黙的なコミットを引き起こしません。」とあるので、さきほどの、DDL文を含まないケースと同じ結果になります。

確かめてみると、その通りでした。

int_id:1 str_a_name:名前Aの１
int_id:2 str_a_name:名前Aの２

 

ケース３：トランザクション内にDDLを含む場合

ここからが本題です。

上記のソースの「create temporary table」を「create table」にします。

TEMPORARYキーワードをとります。

<?php
    require "class_testdb.php";
    try {
        $dbh = new TestDb();
        $dbh->beginTransaction();
        $sth = $dbh->exec("delete from test_a;");
        $sth = $dbh->exec("insert into test_a values(0,'インサート１','インサート１','インサート１');");
        // **** DDL文発行
        $sql = "create table tmp_test (
            id INT(11) AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(20)
        ) engine=memory;";
        $res = $dbh->query($sql);
        // **************
        $sth = $dbh->exec("insert into test_a values(0,'インサート２','インサート２','インサート２');");
        $dbh->rollBack();  
        
        $sql = 'select int_id, str_a_name from test_a;';
        $stmt = $dbh->query($sql);
        while($result = $stmt->fetch(PDO::FETCH_ASSOC)){
            print('int_id:'.$result['int_id'].' str_a_name:' .$result['str_a_name'].'<br>');
        }
   } catch (PDOException $e) {
        printf("[Error] %s¥n", $e->getMessage());
   }
?>

DDL文がはいることで、暗黙的なコミットが発生します。 

これを「Apatche　＋　PHP7.2」環境で実行すると、以下の結果になりました。

int_id:1 str_a_name:インサート１
int_id:2 str_a_name:インサート２

 $dbh->rollBack();  は完全に無視されています。

動きだけを見ると、[create table」分が実行された時にそれまでの実行分がコミットされて、その後オートコミットモードにはいってしまったように見えます。

ネットの記事を見ていると、DDL文までのステートメントはコミットされていて、それ以降のステートメントはロールバックされる・・みたいに書いてあるものもありましたが、それとはちょっと違う動きみたいです。

 

まとめ

暗黙的なコミットには注意が必要ですね。

意図しない動きなので、SQLエラーにならなくても、アンチパターンです。

トランザクションの設計をするとき、DDL文がまぎれこまないように十分注意しておく必要があります。

共通メソッドやストアドルーチンなど、トランザクションの中で呼ばれるものについても、DDL文が紛れ込まないように注意しないと・・です。

でないと、どっかでトラブルを引き起こすリスクがありますね。

今回はこんなところで。

ではでは。