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

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',
  ),
)