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付けてない場合、行ロックになりませんでした。
ひも付けたいというケースあると思います。
そんな時、昇順にロックしていくと行ロックがかぶる可能性が上がるので、ランダムに
行ロックかけたいですよね。
そこで、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付けてない場合、行ロックになりませんでした。