隨機選取一個資源 模擬表如下: 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,說明資源已經被別人鎖定.自己需要重新獲取.
