mysqlでランダムに1行、行ロックをかける方法

シリアルコードや当選番号をあらかじめテーブルに登録しておき、後からユーザー情報とひも付けるケースがあると思います。この際、昇順にロックをかけると行ロックが競合する可能性があるため、ランダムに行ロックをかける方法が有効です。

ランダムに1行だけロックをかける方法

まず、以下のSQLでテーブルを作成します。

create table serials(
  id int AUTO_INCREMENT, 
  code varchar(20),
  user_id int, 
  INDEX(id)
) ENGINE=InnoDB;

次に、以下のSQLでレコードを挿入します。

insert into serials (id, code, user_id) values (1, 'a', null);
insert into serials (id, code, user_id) values (2, 'b', null);
insert into serials (id, code, user_id) values (3, 'c', null);
insert into serials (id, code, user_id) values (4, 'd', null);
insert into serials (id, code, user_id) values (5, 'e', null);

ランダムに1行だけロックをかけるには、以下のSQLを実行します。

begin;
select * from serials AS a 
inner join (select id from serials order by rand() limit 1) AS b 
on a.id = b.id 
for update;

この方法は、1行のみをランダムに選択してロックするのに有効です。

失敗談

以下のようなSQLを試みたところ、予期しない結果が発生しました。

begin;
select * from serials order by rand() limit 1 for update;

このSQLでは、テーブル全体にロックがかかってしまいます。PostgreSQLでは問題なく動作するかもしれませんが、MySQLでは注意が必要です。

また、以下のようにサブクエリを使用して行ロックをかけようとすると、複数行がロックされる恐れがあります。

begin;
select * from serials 
where id = (select id from serials order by rand() limit 1) 
for update;

このSQLでは、id を使って1行を指定しているように見えますが、内部で select id from serials order by rand() limit 1 が各行ごとに実行されるため、複数行がロックされる可能性があります。

注意点

AUTO_INCREMENT が設定されていない場合、INNER JOIN を使用しても行ロックが正しく機能しないケースがありました。ユニークな識別子がない場合、行ロックの挙動に注意が必要です。