MySQLのプロシージャでトランザクション処理

最初はPHPに備わっているPDOのトランザクションを利用しようとしたんですが、
beginTransaction()、rollBack()をするもDBがなぜか更新されてしまうという事が起きました。
autocommitも切って、inTransaction()でトランザクション内かどうかを調べるもちゃんと「true」
という事で、MySQLサイドで直にトランザクションが行われているかを調べるために作成しました。

実際に作成したプロシージャ

DROP PROCEDURE IF EXISTS SQLEXE;

DELIMITER //
CREATE PROCEDURE SQLEXE(
    IN EDITSQL TEXT,
    OUT ERRFLG INT,
    OUT MESSAGE VARCHAR(255)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLWARNING, NOT FOUND, SQLEXCEPTION
    BEGIN
        SET MESSAGE := CONCAT(MESSAGE, "エラーです!");
        ROLLBACK;
    END;

    SET ERRFLG := 1;
    SET MESSAGE := "";

    SET AUTOCOMMIT := 0;
    START TRANSACTION;

        SET @EDITSQL := EDITSQL;
        PREPARE stmt FROM @EDITSQL;
        EXECUTE stmt;

        SET ERRFLG := 0;

    IF ERRFLG = 0 THEN
        SET MESSAGE := CONCAT(MESSAGE, "COMMITしました!");
        COMMIT;
    ELSE
        SET MESSAGE := CONCAT(MESSAGE, "ROLLBACKしました!");
        ROLLBACK;
    END IF;

END;
//
DELIMITER ;

SQLEXEプロシージャでは受け取った文字列をSQLとしてただ実行するだけです。
正常に処理が完了するとOUTとしてMESSAGEにCOMMITかROLLBACKのメッセージが入るはずです。
ECLARE EXIT HANDLERを利用すると、MySQLでのプロシージャ内のエラーを制御する事ができます。

PHPでの実装

test.php

    $sql = <<<SQL
    CALL SQLEXE("UPDATE BLOG SET BLOGNAME = 'てすと' WHERE BLOGID = 1;",@ERRFLG, @MESSAGE);
SQL;

    $stmt = null;
    $stmt = $GLOBALS['MYDB']->prepare($sql);
    $stmt->execute();


    $sql = <<<SQL
    SELECT @ERRFLG AS ERRFLG, @MESSAGE AS MESSAGE FROM DUAL;
SQL;

    $stmt = null;
    $stmt = $GLOBALS['MYDB']->prepare($sql);

    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    var_dump($row);
    

「BLOG」がテーブルで「BLOGNAME」「BLOGID」がカラムです。
SQLEXEに文字列のSQLを渡して、SQLを実行させます。
処理が完了した際に「@ERRFLG」と「@MESSAGE」にそれぞれ処理の結果が保存されているので確認してみます。
そして実行完了後にERRFLG=1でわざとロールバックさせると
きちんと@MESSAGEに「ROLLBACKしました!」の文字が入っていました。
そしてDBを確認してみるとなぜか更新されています。。。

結論から言うと

そもそも**ストレージエンジンがMyISAMなのでトランザクション機能をサポートしていない。**という事でした。
そしてロックにおいてもMyISAMの場合はテーブル単位、InnoDBの場合は行ロックだそうです。