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,说明资源已经被别人锁定.自己需要重新获取.
