随机选取一个资源
模拟表如下:

createtableroom_info(RoomIDbigintnotnullauto_incrementcomment'房间ID',Statesmallintnotnulldefault1comment'状态.1表示空闲,2表示被占用',primarykey(RoomID));INSERTINTO`room_info`(`RoomID`,`State`)VALUES(1,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(2,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(3,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(4,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(5,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(6,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(7,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(8,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(9,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(10,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(11,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(12,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(13,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(14,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(15,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(16,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(17,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(18,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(19,1);INSERTINTO`room_info`(`RoomID`,`State`)VALUES(20,1);

从资源表中随机选取一个资源,并且修改状态.
资源表的记录不多,大致1w行左右.

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

这个索引非常重要
如果没有这个索引,可能会锁多行.

随机获取一行,并且修改资源状态.

setautocommit=false;updateroom_infosetstate=2whereRoomID=(select*from(selectRoomIDfromroom_infowherestate=1orderbyrand()limit1)a)andstate=1and@roomid:=roomid;select@roomid;commit;

需要注意的是,在执行下面SQL的时候,没有上锁.
select RoomID from room_info where state=1 order by rand() limit 1
所以在多线程环境下,可能冲突.
所以需要判断 update 的影响行数.如果影响行数为0,说明资源已经被别人锁定.自己需要重新获取.


如果影响行数为0,此时切记不能拿到@roomid直接使用,而是需要重新运行.