[PGメモ]シーケンス(っぽい)DBを作成してAUTO_INCREMENT的にID管理を行う | Ｌａｔｅ Ｒｉｓｅｒ
AmebaAmeba 20th Anniv.ホームピグアメブロ
芸能人ブログ人気ブログ
新規登録
ログイン

Ｌａｔｅ Ｒｉｓｅｒ

ダメ主婦ミルミルのプログラムと道の駅ドライブとリラックマの日々。
プログラム系は情報提供ではなく個人的メモなので、信憑性薄め。

[PGメモ]シーケンス(っぽい)DBを作成してAUTO_INCREMENT的にID管理を行う


検証version: 5.0.67-community-log MySQL Community Edition (GPL)

MtSQL5.1からは高性能化したAUTO_INCREMENTですが、
やはり旧バージョンではロックがネックとなり、マルチタスクには対応が微妙な模様。
こいつに対する処理方法についての検証として、
MyISAMテーブルを用いたシーケンス（的）管理方法の動作検証を簡単にしてみた。
とりあえず、下記のようなテーブルをつくる。

一つはスタック用のテーブル。もう一つはシーケンス用。
トランザクションの範囲外で動いているシーケンスのIDを都度取得し、
test_stackのstack_idに連動させればよいのではないかと。
CREATE  TABLE `test_stack` (
    ->   `big_id`        int unsigned NOT NULL DEFAULT 0 ,         #大カテゴリ
    ->   `mid_id`        int unsigned NOT NULL DEFAULT 0 ,         #中カテゴリ
    ->   `min_id`        int unsigned NOT NULL DEFAULT 0 ,         #小カテゴリ
    ->   `stack_id`      bigint unsigned  NOT NULL DEFAULT 0,      #+1で管理する
    ->   `user_id`       int unsigned NOT NULL DEFAULT 0,          #ユーザーID
    ->   `create_time`   int unsigned NOT NULL DEFAULT 0 ,         #スタック時刻
    ->   PRIMARY KEY (`big_id`,`mid_id`, `min_id`, `stack_id`)
    ->   ) ENGINE=InnoDB;
mysql> CREATE  TABLE `seaq_stack_id` (
    ->   `big_id`        int unsigned NOT NULL DEFAULT 0 ,         #大カテゴリ
    ->   `mid_id`        int unsigned NOT NULL DEFAULT 0 ,         #中カテゴリ
    ->   `min_id`        int unsigned NOT NULL DEFAULT 0 ,         #小カテゴリ
    ->   `stack_id`      bigint unsigned NOT NULL DEFAULT 0,       #シーケンスID
    ->   PRIMARY KEY (`big_id`,`mid_id`, `min_id`)
    ->   )  ENGINE=MYISAM;


ということで、MYISAMのシーケンステーブルの動きを検証してみます。


http://blog.livedoor.jp/montz/archives/220104.html
に関連して改めて検証してみた。



まず、データをつくります。初期ID値は0で。(何かを設定しないと更新されません。)

mysql①> insert into seaq_stack_id (big_id,mid_id,min_id,stack_id)values(1,1,1,0);

mysql①> select * from seaq_stack_id;
+--------+--------+--------+----------+
| big_id | mid_id | min_id | stack_id |
+--------+--------+--------+----------+
|      1 |      1 |      1 |        0 | 
+--------+--------+--------+----------+
1 row in set (0.00 sec)


LAST_INSERT_ID([expr])とは。
http://dev.mysql.com/doc/refman/4.1/ja/miscellaneous-functions.html

AUTO_INCREMENT カラムに挿入された値のうち、最後に自動生成された値を返す。
生成された最後の ID は、接続ごとにサーバで維持される。
したがって、この関数から個々のクライアントに返される値は、
そのクライアントによって生成された最新の AUTO_INCREMENT 値である。
この値は、他のクライアントがそれぞれの AUTO_INCREMENT 値を生成しても、
それによって影響されることはない。
この動作によって、他のクライアントの活動にかかわりなく、
また、ロックやトランザクションを必要とすることなく、
自分の ID を確実に取り出すことができる。

http://dev.mysql.com/doc/refman/5.1/ja/information-functions.html
5.1からは例の如く少々変わっているので注意されたい。


mysql①> UPDATE seaq_stack_id SET stack_id=LAST_INSERT_ID(stack_id+1) 
       > where big_id=1 and mid_id=1 and min_id=1;

参考にしたサイトには下記の注意書きがあった。負荷を掛けた際のエラーについても記載されているので参考にしたい。
このときのUPDATE文での注意として、LAST_INSERT_ID(id+1)のように、LAST_INSERT_ID()の括弧内に取得したい値
（例の場合は、id+1のインクリメント値)を指定すること。下記SQL文は間違いである。
×UPDATE sequence SET id=LAST_INSERT_ID(id)+1;
×UPDATE sequence SET id=id+1;


で、上記UPDATEの結果発生したID値がこれ。
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 | 
+------------------+
1 row in set (0.00 sec)


もう一回更新してみても結果はちゃんと取れます。

mysql①> UPDATE seaq_stack_id SET stack_id=LAST_INSERT_ID(stack_id+1)
       > where big_id=1 and mid_id=1 and min_id=1;

mysql①> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 | 
+------------------+
1 row in set (0.00 sec)



もう数回更新してみても結果はちゃんと取れます。

mysql①> UPDATE seaq_stack_id SET stack_id=LAST_INSERT_ID(stack_id+1)
       > where big_id=1 and mid_id=1 and min_id=1;
mysql①> UPDATE seaq_stack_id SET stack_id=LAST_INSERT_ID(stack_id+1)
       > where big_id=1 and mid_id=1 and min_id=1;
mysql①> UPDATE seaq_stack_id SET stack_id=LAST_INSERT_ID(stack_id+1)
       > where big_id=1 and mid_id=1 and min_id=1;

mysql①> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                5 | 
+------------------+
1 row in set (0.01 sec)




では、データを増やした場合の挙動を確認してみます

mysql①> insert into seaq_stack_id(big_id,mid_id,min_id,stack_id)values(1,1,2,0);
mysql①> insert into seaq_stack_id (big_id,mid_id,min_id,stack_id)values(1,1,3,0);

mysql> select * from seaq_stack_id;
+--------+--------+--------+----------+
| big_id | mid_id | min_id | stack_id |
+--------+--------+--------+----------+
|      1 |      1 |      1 |        5 |
|      1 |      1 |      2 |        0 |
|      1 |      1 |      3 |        0 |
+--------+--------+--------+----------+

まず今持っているLAST_INSERT_IDの値を出力します。
(where big_id=1 and mid_id=1 and min_id=1;)

mysql①> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)


コレに対し、別の条件でUPDATEし、LAST_INSERT_IDを取得すると、
新しい条件付けのが入手できます。

mysql①> UPDATE seaq_stack_id SET stack_id=LAST_INSERT_ID(stack_id+1)
       > where big_id=1 and mid_id=1 and min_id=2;

mysql①> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

mysql①> UPDATE seaq_stack_id SET stack_id=LAST_INSERT_ID(stack_id+1)
       > where big_id=1 and mid_id=1 and min_id=2;

mysql①> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

ここで再びupdateした場合、入手できるのは7となります
mysql①> UPDATE seaq_stack_id SET stack_id=LAST_INSERT_ID(stack_id+1)
       > where big_id=1 and mid_id=1 and min_id=1;

mysql①> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                7 |
+------------------+
mysql①> select * from seaq_stack_id;
+--------+--------+--------+----------+
| big_id | mid_id | min_id | stack_id |
+--------+--------+--------+----------+
|      1 |      1 |      1 |        7 |
|      1 |      1 |      2 |        2 |
|      1 |      1 |      3 |        0 |
+--------+--------+--------+----------+
3 rows in set (0.00 sec)


では、先ほどのデータに対して2端末からのアクセスを試してみます


これは公式ドキュメントの↓に対する検証となります
>複数スレッドからの同時アクセス（レースコンディション）時における、LAST_INSERT_ID()の戻り値について、生成された最後の ID は、接続ごとにサーバで維持される。


mysql①> select * from seaq_stack_id;
+--------+--------+--------+----------+
| big_id | mid_id | min_id | stack_id |
+--------+--------+--------+----------+
|      1 |      1 |      1 |        7 |
|      1 |      1 |      2 |        2 |
|      1 |      1 |      3 |        0 |
+--------+--------+--------+----------+
3 rows in set (0.01 sec)

上記の流れから端末①出取得できるSELECT LAST_INSERT_IDは7
mysql①> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                7 |
+------------------+

端末②からデータの確認をします
mysql②> select * from seaq_stack_id;
+--------+--------+--------+----------+
| big_id | mid_id | min_id | stack_id |
+--------+--------+--------+----------+
|      1 |      1 |      1 |        7 |
|      1 |      1 |      2 |        2 |
|      1 |      1 |      3 |        0 |
+--------+--------+--------+----------+
3 rows in set (0.00 sec)

端末②ではまだID発行を行っていない為、取得できるSELECT LAST_INSERT_IDは0

mysql②> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+

端末②から更新してみます。
mysql②> UPDATE seaq_stack_id SET stack_id=LAST_INSERT_ID(stack_id+1)
       > where big_id=1 and mid_id=1 and min_id=1;

mysql②> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                8 |
+------------------+
1 row in set (0.00 sec)

あたらしいIDが取得できました。
このタイミングで端末①からもう一度LAST_INSERT_IDを取得すると、
先ほど端末①で発行したLAST_INSERT_IDの値が取得できます。
これでスレッドごとのアクセスで保持されることが検証できました。

この調子でいくつかやってみます。
mysql①> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                7 |
+------------------+
1 row in set (0.00 sec)

mysql①> UPDATE seaq_stack_id SET stack_id=LAST_INSERT_ID(stack_id+1)
       > where big_id=1 and mid_id=1 and min_id=1;

mysql①> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                9 |
+------------------+
1 row in set (0.00 sec)


mysql②> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                8 |
+------------------+
1 row in set (0.00 sec)


mysql②> UPDATE seaq_stack_id SET stack_id=LAST_INSERT_ID(stack_id+1)
       > where big_id=1 and mid_id=1 and min_id=1;

mysql②> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               10 |
+------------------+
1 row in set (0.00 sec)


mysql①> UPDATE seaq_stack_id SET stack_id=LAST_INSERT_ID(stack_id+1)
       > where big_id=1 and mid_id=1 and min_id=1;

mysql①> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               11 |
+------------------+
1 row in set (0.00 sec)


うん。便利だ。


ふむ。これでseaq_stack_idからIDを引っ張ってtest_stackのstack_idへ当て込めば
DBロックにも引っかからずにマルチタスク処理できるっつーわけですな。