隨機選取一個資源
模擬表如下:

create table room_info(
RoomID bigint not null auto_increment comment '房間ID',
State smallint not null default 1 comment '狀態.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);

從資源表中隨機選取一個資源,並且修改狀態.
資源表的記錄不多,大致1w行左右.

建立普通索引
create index inx_1 on room_info(roomid,state);

這個索引非常重要
如果沒有這個索引,可能會鎖多行.

隨機獲取一行,並且修改資源狀態.

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;

需要注意的是,在執行下面SQL的時候,沒有上鎖.
select RoomID from room_info where state=1 order by rand() limit 1
所以在多執行緒環境下,可能衝突.
所以需要判斷 update 的影響行數.如果影響行數為0,說明資源已經被別人鎖定.自己需要重新獲取.

如果影響行數為0,此時切記不能拿到@roomid直接使用,而是需要重新執行.

MySQL隨機選取資源

相關文章