真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

MySQL隨機(jī)選取資源

隨機(jī)選取一個(gè)資源
模擬表如下:

create table room_info(
RoomID bigint not null auto_increment comment '房間ID',
State smallint not null default 1 comment '狀態(tài).1表示空閑,2表示被占用',
primary key(RoomID)
) ;
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (1,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (2,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (3,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (4,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (5,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (6,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (7,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (8,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (9,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (10,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (11,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (12,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (13,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (14,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (15,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (16,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (17,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (18,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (19,1);
INSERT INTO `room_info` (`RoomID`,`State`) VALUES (20,1);

從資源表中隨機(jī)選取一個(gè)資源,并且修改狀態(tài).
資源表的記錄不多,大致1w行左右.

創(chuàng)建普通索引
create index inx_1 on room_info(roomid,state);

這個(gè)索引非常重要
如果沒(méi)有這個(gè)索引,可能會(huì)鎖多行.

隨機(jī)獲取一行,并且修改資源狀態(tài).

set autocommit=false;
update room_info set state=2 where RoomID=(
select * from (
select RoomID from room_info where state=1 order by rand() limit 1
) a
) and state=1 and @roomid:=roomid;
select @roomid;
commit;

需要注意的是,在執(zhí)行下面SQL的時(shí)候,沒(méi)有上鎖.
select RoomID from room_info where state=1 order by rand() limit 1
所以在多線(xiàn)程環(huán)境下,可能沖突.
所以需要判斷 update 的影響行數(shù).如果影響行數(shù)為0,說(shuō)明資源已經(jīng)被別人鎖定.自己需要重新獲取.