MySQLの LAST_INSERT_ID の挙動について少し調べてみる

一意なIDを取得するための採番テーブルを利用したかったのだが、
ベストプラクティスとして色んなサイトに書かれているやり方が以下。

update num set id = LAST_INSERT_ID(id + 1);
select LAST_INSERT_ID();

だいたいやってることは理解できる（採番テーブルを更新して、更新後のデータをselect）
のだが、

LAST_INSERT_ID ってなんじゃい？となったので調べてみた。

LAST_INSERT_IDを使って採番テーブルを扱う - (ﾟ∀ﾟ)o彡 sasata299's blog

上記の記事がわかりやすかった。

だが、そもそも適切なトランザクション管理してあれば
LAST_INSERT_ID 使わなくてもよくない？って思ったのでちょっと検証してみる。

準備

mysql> CREATE TABLE num (
    ->     id bigint(20) unsigned NOT NULL DEFAULT '0'
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.53 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

試す

update num set id = id + 1;
select id from num;

LAST_INSERT_ID を用いない上記のSQLで、
2人のユーザーが採番したケースを試す。

select id from num;  // 0

start transaction;
update num set id = id + 1;
                                       start transaction;
                                       update num set id = id + 1; // 左記がコミットされるまで待つ
select id from num; // 1
commit;
                                       select id from num; // 2
                                       commit;

このように適切にトランザクション管理してあれば
同時実行でも問題は起きない。

トランザクションを使わない場合

トランザクションを使わない場合、どうだろうか。
まずLAST_INSERT_ID を使わない方から

select id from num;  // 0

update num set id = id + 1;
                                       update num set id = id + 1;
select id from num; // 2
                                       select id from num; // 2

トランザクションを使ってないので当然の結果。
これはもちろん両者 id が同じになってしまったのでまずいパターン。

次、LAST_INSERT_ID を使う場合

select id from num;  // 0

update num set id = last_insert_id(id + 1);
                                       update num set id = last_insert_id(id + 1);
select last_insert_id(); // 1 !!!
                                       select last_insert_id(); // 2

select id from num; // 2
                                       select id from num; // 2

というわけで、LAST_INSERT_ID を使うことで、
トランザクションを使わずとも Isolation が保たれてしまった。

この場合の LAST_INSERT_ID は、AUTO INCREMENT と関係のある使い方ではなく、
id の値をセッション内で保存しておくためのローカル変数のように使われているのがポイントだ。

というわけで採番テーブルとして役割を全うするなら、
トランザクションなしで LAST_INSERT_ID を使おう！
不用意にロックがかかる心配もないぞ！

あースッキリ。