Webアプリケーションのシステム開発を行っている際に、検証したことと気づいたこととコーディングしたこと。

 

MySQLのデフォルトエンジンは通常はMyISAMとなっているが、自分はトランザクション発行の関係でInnoDBを使用してシステムを構築した。

このとき、「複合キーを持たせたInnoDBのテーブルにMyISAM的なINSERTを行うならどうするんやろか？」とか「基本的なところで複数のセッションでそれを同時に実行したらどうなるんやろか？」とかで頭を捻らせたことが多々あった。

もちろん最終的にきちんと実装にこぎつけたし、開発もある程度は落ち着いてきているので、とてつもなく久しぶりの更新となるが、今のうちに行ったことの記録を記しておくことにする。

 

まず前提条件としてInnoDBで以下のようなテーブルを作りたいとする。

以下の複合キーの構造でGRPカラムをグルーピングとして、同じIDを共存させるものとする。

 

FOODSテーブル

GRP	ID	NAME
fruit	1	Kiwifruit
fruit	2	Apple
fruit	3	Orange
fruit	4	Banana
vegetable	1	Cabbage
vegetable	2	Carrot
vegetable	3	Tomato

 

FOODSテーブルのCREATEのSQL

CREATE TABLE FOODS (
	GRP VARCHAR(24) NOT NULL DEFAULT '',
	ID INT(11) NOT NULL AUTO_INCREMENT,
	NAME varchar(255) NOT NULL DEFAULT '',
	PRIMARY KEY ( GRP, ID )
)
#ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
#ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
;
ALTER TABLE FOODS ADD INDEX FOODS_INDEX ( GRP, ID );

 

IDには上記のようにAUTO_INCREMENTを付与しておくことにするが・・・

コメントアウトしているENGINE=InnoDBを有効にすると「#1075 – 不正な表定義です。AUTO_INCREMENT列は1個までで、索引を定義する必要があります。」といきなりエラーを吐いてくる。

 

そりゃそうだ。だってInnoDBでは複合キーを設定したカラムにAUTO_INCREMENTは設定できないもんね。

このあたりはInnoDBの仕様のため、PHPのミドルウェア側（又はストアドプロシージャ側、今回は扱わないけど）でデータ作成を行う必要がある。

 

ENGINE=MyISAMにすれば普通に通るんだけど、そうすると今度はトランザクションが発生させられなくなりシステムの仕様的に非常に不都合。

よって、IDはAUTO_INCREMENTを削除して、「ID INT(11) NOT NULL,」として設定する。

 

そうすると今度はPHPで、mysqliかpdoで連番体系を作りつつSQLを実行させる必要があるわけだが、以下のようにしてもMyISAMを採用したときと同じ挙動はできない。

 

$sql = '';
$into = array(
	array( 'fruit', null, 'Grape' ),
	// array( 'fruit', null, 'Melon' ),
);

$db = new mysqli( 'hostname', 'user', 'password', 'dbname' );
$db->set_charset( 'utf8' ); 
$db->begin_transaction();
try {
	foreach( $into as $val ) {
		$sql = sprintf( 'INSERT INTO FOODS ( GRP, ID, NAME ) VALUES ( %1$s, %2$s, %3$s );', $val[0], $val[1], $val[2] );
		$ins = $db->query( $sql );
		if ( false === $ins ) {
	 		throw new Exception( $ins->error );
		}
	}
	// ～ここには他のテーブルを操作する処理が入る～
	$db->commit();
}
catch ( Exception $e ) {
	$db->rollback();
}

 

ともあれ、MyISAMでAUTO_INCREMENTを設定したのであればこのコードでGRP ‘fruit’でID ‘5’のレコードが挿入されるものの、InnoDBでは複合キーのAUTO_INCREMENTが設定できない訳で、実行したら当然ながら以下のレコードが挿入されるだけ。

 

GRP	ID	NAME
fruit	0	Grape

 

コメントアウトのMelonの配列を入れた場合は悲惨で、IDが’0’で2行挿入されるわけだから、実行しないまでも確定でエラーの発生がわかること請け合い。

じゃあ、InnoDBの仕様に全てを任せてGRPを複合キーのプライマリから外したらいいやんとなれば、そもそものお題が達成できなくなる。

（実際に実装しているテーブルは上記のような単純なテーブル操作ではなく、システムの仕様というか実務の都合上、確実に複合キーでの連番が必要となるテーブルがあるので、『できない』では済まされない。）

 

この状況で要件を実装する場合、以下の箇条の問題を解決しなければならない。

• 複合キー上での最大のIDである番号を取得する
• 最大のIDである番号からの連番を生成する

 

そこでまずは、PHP側で上記の箇条である「複合キー上での最大のIDである番号を取得する」を解決する。

これは実にカンタンな話で、以下のように取得可能。

$max_id = 1;
$group = 'fruit';
$rows = array(); 

$sql = sprintf( 'SELECT MAX( ID ) AS max_id FROM FOODS WHERE GRP = \'%1$s\' GROUP BY GRP;', $group );
$sel = $db->query( $sql );
if ( false !== $sel ) {
	while ( $row = $sel->fetch_assoc() ) {
		$rows[] = $row;
	}
	if ( isset( $rows[0] ) ) $max_id = $rows[0]['max_id'];
}

 

そして、次に箇条のふたつめにある「最大のIDである番号からの連番を生成する」のところ。

foreachのINSERTのID部分の始点を$max_idから設定する。

foreach( $into as $val ) {
	$max_id++;
	$sql = sprintf( 'INSERT INTO FOODS ( GRP, ID, NAME ) VALUES ( %1$s, %2$s, %3$s );', $val[0], $max_id, $val[2] );
	$ins = $db->query( $sql );
	if ( false === $ins ) {
 		throw new Exception( $ins->error );
	}
}

 

ヤター！これで、InnoDBで複合キーの連番が設定できたよー＼(^o^)／

 

な～んて、思ったらまだまだ甘い。

そもそもそれだけならば、要所をググって調べれば何の苦労もなく実装できるわけで・・・。

 

一見テストで問題がないように見えても、少し考えると致命的な問題にハッと気づくはず。

自分のようにひとり情シスでアプリケーションを組んでいるときはついつい忘れがちだが、実運用上でアプリとは経理さんとか現場の人とかが、常にたったひとりで実行するものではないということ。

 

すなわち、この時点で冒頭第二の頭を捻らせたことが関わってくる。

実行した場合、確かに一人だけが運用しているときは問題がないのだが、複数人で運用した場合、INSERT時点でIDが競合して「#1062 – ‘GRP-ID’ は索引 ‘PRIMARY’ で重複しています。」とエラーが発生する。

これは連番を振っている時点ではPHPのスクリプト内で競合チェックをしていないことに起因している問題である。

 

例えば、AさんとBさんが同時に運用しているときに、同時INSERTが発生したとする。

そのとき、それぞれの持ちスクリプトは以下のようになってしまう。

 

GRP ‘fruit’がID最大値が’5’
Aさんの持ちスクリプト	Bさんの持ちスクリプト
パターン1：タイミングが同じ ID最大値の初期取得は’5’でINSERT○	パターン1：タイミングが同じ ID最大値の初期取得は’5’でINSERT×
パターン2：タイミングがズレたとき ID最大値の初期取得は’5′ 二度目の採番は’6’でINSERT× →’6’にINSERT失敗して処理終了	パターン2：タイミングがズレたとき ID最大値の初期取得は’6′ 二度目の採番は’7’でINSERT○

 

これを回避するためには、INSERTが行われるタイミングで、その時点の最大値を取得する必要がある。

INSERTを行う際にIDが競合してSQLエラーが発生したら、再度別IDの同一データを抽選してあげてデータを挿入するという仕様。

 

最終的に、以下のようなコードを打ち出した。

 

$sql = '';
$duplicate = true; // 重複フラグ
$into = array(
	array( 'fruit', 'Grape' ),
	array( 'fruit', 'Melon' ),
);
$max_id = 1;
$group = 'fruit';
$rows = array(); 


// DB接続
$db = new mysqli( 'hostname', 'user', 'password', 'dbname' );
$db->set_charset( 'utf8' ); 

// 最大値を取得
$sql = sprintf( 'SELECT MAX( ID ) AS max_id FROM FOODS WHERE GRP = \'%1$s\' GROUP BY GRP;', $group );
$sel = $db->query( $sql );
if ( false !== $sel ) {
	while ( $row = $sel->fetch_assoc() ) {
		$rows[] = $row;
	}
	if ( isset( $rows[0] ) ) $max_id = $rows[0]['max_id'];
}
// 複合キーを連番でINSERTする処理
$db->begin_transaction();
try {
	foreach( $into as $val ) {
		$max_id = $max_id + 1; // 次番号を取れるように加算
		$duplicate = true; // 第一ループの際にこれを設定することで必ずwhileを一度は通す
		while ( $duplicate ) {
			try {
				// 抽選処理
				$sql = sprintf( 'INSERT INTO FOODS ( GRP, ID, NAME ) VALUES ( %1$s, %2$s, %3$s );', $val[0], $max_id, $val[2] );
				$ins = $db->query( $sql );
				if ( false === $ins ) {
					// ネスト内のtryに通知
					// ※重複フラグはtrueで維持される
			 		throw new Exception( $ins->errno );
				}
				$duplicate = false;
			}
			catch ( Exception $e ) {
				if ( 1062 === $e ) {
					// ID最大値を加算
					$max_id = $max_id + 1;
				} else {
					// Duplicate以外のエラーはネスト元のtryに通知
					// ※通知した時点でforeachやwhileは中断されることがミソ
					$sql = sprintf( 'INSERT INTO FOODS ( GRP, ID, NAME ) VALUES ( %1$s, %2$s, %3$s );', $val[0], $val[1], $val[2] );
					$ins = $db->query( $sql );
					if ( false === $ins ) {
				 		throw new Exception( $ins );
					}
				}
			}
		}
	}

	// ～ここには他のテーブルを操作する処理が入る～

	$db->commit();
}
catch ( Exception $e ) {
	$db->rollback();
}

// DB切断
$db->close();

 

今更ながらとなるが、いくらトランザクションを発行しているとはいえ、ループのところでSQLを実行しているあたりリソース的にやばいなと思ってはいたけど・・・。

結果的にここで生きてきたというのは、皮肉としか言いようがない。

 

これで冒頭の頭を捻らせたことは無事解決し、MySQLのInnoDBで複合キーかつ連番のINSERTを実装することが可能となった。

多分、同じようなコード構成であれば、DjangoだろうがRailsだろうが実装はできると思う。

 

ただし、注意事項としてこの方法は飽くまでも「トランザクション発行が必要不可欠であり、また、凄まじく大量のレコードは挿入しない」という仕様ありきで実装できるもの。

自分の場合は、そのケースに一致していたのでこのコーディング内容としたが、実装方法はやはりきちんと見極めるべき。

 

スクリプトでのループを多用する特性上、いくら近年の実行サーバやデータベースサーバのスペックが上がっているとはいえ、パフォーマンスを考えてもせいぜい50～100行程度がギリギリのライン。

そういうときは、トランザクションの発行を諦めて要所要所をMyISAMベースにするか、もしくはテーブルの構成を練り直してユニークなカラムを振るとか、単純なDELETE→INSERTにしたり、あるいはストアドプロシージャにしたりと、ケースバイケースでコーディングするのが無難だと思う。