Help us understand the problem. What is going on with this article?

MySQLの「暗黙のトランザクションコミット」対策:トランザクション中でも安全にCREATE TABLEなどをする方法

More than 5 years have passed since last update.

MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.4.3 暗黙のコミットを引き起こすステートメント

次の各ステートメント(そしてそれらの同義語)は、まるでステートメントを実行する前に COMMIT を行ったかのように、暗黙にトランザクションを終了します。

ALTER FUNCTION, ALTER PROCEDURE, ALTER TABLE, BEGIN, CREATE DATABASE, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, DROP DATABASE, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP TABLE, LOAD DATA INFILE LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE TABLE, UNLOCK TABLES.

とのことなので、BEGIN していても、途中で CREATE TABLE などをすると、その直前で COMMIT が走ります。「CREATE TABLE失敗しちゃったー、とりあえずできたかどうかわからんテーブルはDROP IF EXISTSするとして、その前にやったデータ更新系はROLLBACKだ!」なんて思っても、既に遅しです。

ここでは、トランザクション中でも安全にCREATE TABLEなどをするコードを紹介します。

<?php

$dbname = 'test';
$host   = 'localhost';
$user   = 'root';
$pass   = 'root';
$dsn    = sprintf('mysql:dbname=%s;host=%s', $dbname, $host);

$options = array(
    PDO::ATTR_ORACLE_NULLS       => PDO::NULL_NATURAL,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_AUTOCOMMIT         => true,
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
    PDO::ATTR_EMULATE_PREPARES   => false,
);

/**
 * クエリーをログできるようにしたPDO
 */
class MyPDO extends PDO
{
    public function query($query)
    {
        $this->_print($query);
        $arguments = func_get_args();
        return call_user_func_array(array('parent', 'query'), $arguments);
    }

    public function beginTransaction()
    {
        $this->_print('BEGIN');
        parent::beginTransaction();
    }

    public function rollBack()
    {
        $this->_print('ROLLBACK');
        parent::rollBack();
    }

    protected function _print($query)
    {
        static $count = 1;
        echo $count.') '.$query.';'.PHP_EOL;
        $count += 1;
    }
}


echo "暗黙のトランザクションコミットが走るかどうかの実験\n\n";

// 下準備
/** @var PDO $pdo  */
$pdo = new MyPDO($dsn, $user, $pass, $options);
$pdo->query('DROP TABLE IF EXISTS foo');
$pdo->query('DROP TABLE IF EXISTS baz');
$pdo->query('CREATE TABLE foo (bar VARCHAR(255)) ENGINE=InnoDB');
$pdo->query('INSERT INTO foo (bar) VALUES ("a"), ("b"), ("c")');

// 更新処理
$pdo->beginTransaction(); // トランザクション開始
$pdo->query('DELETE FROM foo'); // fooテーブルのレコードすべて削除
$pdo->query('CREATE TABLE baz (bazbaz VARCHAR(255)) ENGINE=InnoDB'); // 暗黙のトランザクションコミットが走る
$pdo->rollBack(); // ロールバック!
var_export($pdo->query('SELECT * FROM foo')->fetchAll()); // ロールバックが効いていないので fooテーブルには何も残ってない


echo "\n\nCREATE TABLE文を別コネクションにしたら,
暗黙のトランザクションコミットがどうなるかの実験\n\n";

// 下準備
/** @var PDO $pdo  */
$pdo = new MyPDO($dsn, $user, $pass, $options);
$pdoAnother = new MyPDO($dsn, $user, $pass, $options);
$pdo->query('DROP TABLE IF EXISTS foo');
$pdo->query('DROP TABLE IF EXISTS baz');
$pdo->query('CREATE TABLE foo (bar VARCHAR(255)) ENGINE=InnoDB');
$pdo->query('INSERT INTO foo (bar) VALUES ("a"), ("b"), ("c")');

// 更新処理
$pdo->beginTransaction(); // トランザクション開始
$pdo->query('DELETE FROM foo'); // fooテーブルのレコードすべて削除
$pdoAnother->query('CREATE TABLE baz (bazbaz VARCHAR(255)) ENGINE=InnoDB'); // 別コネクションなのでトランザクションには影響ない
$pdo->rollBack(); // ロールバック!
var_export($pdo->query('SELECT * FROM foo')->fetchAll()); // ロールバックが効いているので、fooテーブルは元通りになっている
実行結果
php TransactionWithCreateTable.php 
暗黙のトランザクションコミットが走るかどうかの実験

1) DROP TABLE IF EXISTS foo;
2) DROP TABLE IF EXISTS baz;
3) CREATE TABLE foo (bar VARCHAR(255)) ENGINE=InnoDB;
4) INSERT INTO foo (bar) VALUES ("a"), ("b"), ("c");
5) BEGIN;
6) DELETE FROM foo;
7) CREATE TABLE baz (bazbaz VARCHAR(255)) ENGINE=InnoDB;
8) ROLLBACK;
9) SELECT * FROM foo;
array (
)

CREATE TABLE文を別コネクションにしたら,
暗黙のトランザクションコミットがどうなるかの実験

10) DROP TABLE IF EXISTS foo;
11) DROP TABLE IF EXISTS baz;
12) CREATE TABLE foo (bar VARCHAR(255)) ENGINE=InnoDB;
13) INSERT INTO foo (bar) VALUES ("a"), ("b"), ("c");
14) BEGIN;
15) DELETE FROM foo;
16) CREATE TABLE baz (bazbaz VARCHAR(255)) ENGINE=InnoDB;
17) ROLLBACK;
18) SELECT * FROM foo;
array (
  0 => 
  array (
    'bar' => 'a',
  ),
  1 => 
  array (
    'bar' => 'b',
  ),
  2 => 
  array (
    'bar' => 'c',
  ),
)
suin
Qiita 4位/TypeScript入門書執筆中/TypeScripterのための座談会「YYTypeScript」主催/『実践ドメイン駆動設計』書籍邦訳レビュア/分報Slack考案/YYPHP主催/CodeIQマガジン執筆/株式会社クラフトマンソフトウェア創設/Web自動テスト「ShouldBee」の開発/TypeScript/DDD/OOP
https://yyts.connpass.com/
craftsman_software
「インフラの心配は、もうおしまい」 インフラ運用を自動化し、手作業を限りなくゼロにする会社
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away