MySQL5.7のInnoDBを使用しており、その中で次のような文字列型のプライマリーキーを持つテーブルがあります。
CREATE TABLE `item` ( `id` varchar(32) NOT NULL, `shop_id` varchar(10) NOT NULL, `name` varchar(100) NOT NULL, `deleted` tinyint(1) NOT NULL, `created` timestamp NOT NULL, `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), INDEX (`shop_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
また、初期データとして以下の内容を登録済みです。
INSERT `item` (id, shop_id, name, deleted, created) VALUES ('itemid-1', 'shopid-1', 'item-1', false, NOW()); INSERT `item` (id, shop_id, name, deleted, created) VALUES ('itemid-2', 'shopid-1', 'item-2', false, NOW()); INSERT `item` (id, shop_id, name, deleted, created) VALUES ('itemid-3', 'shopid-1', 'item-3', false, NOW()); INSERT `item` (id, shop_id, name, deleted, created) VALUES ('itemid-4', 'shopid-2', 'item-4', false, NOW()); INSERT `item` (id, shop_id, name, deleted, created) VALUES ('itemid-5', 'shopid-2', 'item-5', false, NOW());
このテーブルではお店毎の取扱商品を管理していると仮定してください。
取扱商品は各店舗毎に登録可能数に制限ある為、登録を行う際には対象のお店の情報に行ロックをかけ、登録商品数が変動しないようにした上で登録済みの商品数を確認し、登録済み商品数が規定値以下であれば追加商品を登録するという仕組みを実現したいと考えています。
この時「ショップ1」に対する行ロックをかけているつもりが、実際にはいかなるショップへの追加もロック待ちとなります。
具体的には以下の操作で確認しています。
まず、ターミナル1でショップ1の登録済み商品数を確認します。
BEGIN; SELECT count(*) FROM item WHERE shop_id = 'shopid-1' AND deleted = false FOR UPDATE;
この状態で、別なターミナル(ターミナル2)からショップ2の商品を登録します。
INSERT `item` (id, shop_id, name, deleted, created) VALUES ('itemid-7', 'shopid-2', 'item-7', false, NOW());
上記の操作を行うと、ターミナル1のトランザクションが終了する、またはタイムアウトするまでターミナル2は待ち状態となります。
この現象は、プライマリーキーである「id」を「AUTO_INCREMENT」に変更する事で回避できる事は理解していますが、できれば文字列型のキーをそのまま使いたいと考えています。
その後再検証しましたが、そもそもプライマリーキーである「id」を「AUTO_INCREMENT」に変更しても同様にロックがかかり異なるショップIDに対してであっても待ち状態となりました。
その為、現状は解決方法がないようです。
ショップ1の操作中であってもそれ以外のショップの商品を登録できるようにするには、どのような対応をすればよいのでしょうか。
回答1件
あなたの回答
tips
プレビュー