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

シリアルコード、当選番号などあらかじめテーブルに入れておき、後からユーザー情報と
ひも付けたいというケースあると思います。
そんな時、昇順にロックしていくと行ロックがかぶる可能性が上がるので、ランダムに
行ロックかけたいですよね。

そこで、mysqlでランダムに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);

下記のSQLでランダムに1行だけロックすることができます。

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;
→◯


ちょっとはまったので、失敗談記載します。

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


これだと、テーブル全体にロックかかります。
posgresだったらいけるんですが・・・


idをサブクエリで指定し、行ロック。

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

これ、恐ろしい結果になります。

id=で指定して言うので取得できる行は1行かと思いきや、複数行selectされます。

serialsテーブルの各行、各行でselectが走り、毎度、比較するidが変わるようですね。


ちなみに、inner join で行ロックする方法AUTO_INCREMENT付けてない場合、行ロックになりませんでした。