MySQL randomly selects resources

Randomly select a resource

The simulation table is as follows:

Create table room_info (RoomID bigint not null auto_increment comment 'room ID',State smallint not null default 1 comment' status. 1 means idle, 2 means occupied', primary key (RoomID)); INSERT INTO `room_ info` (`RoomID`, `State`) VALUES (1 VALUES); INSERT INTO `RoomID` (`RoomID`, `State`) VALUES (2); INSERT INTO `room_ info` (`RoomID`, `State`) VALUES (3); INSERT INTO `room_ info` (`RoomID`, `State`) VALUES (4gamma 1); INSERT INTO `RoomID`, `State`) VALUES (57.1) INSERT INTO `room_ info` (`RoomID`, `State`) VALUES (6Magne1); INSERT INTO `RoomID` (`RoomID`, `State`) VALUES (7Power1); INSERT INTO `room_ info` (`RoomID`, `State`) VALUES (8pm 1); INSERT INTO `room_ info` (`RoomID`, `State`) VALUES (9Magne1); INSERT INTO `room_ info` (`RoomID`, `State`) VALUES (10pm 1); INSERT INTO `room_ info` (`RoomID`, `State`) VALUES (11pm 1); INSERT INTO `RoomID`, `State`) VALUES (122.1); INSERT INTO `room_ info` (`RoomID`, `State`) VALUES (131st) INSERT INTO `room_ info` (`RoomID`, `State`) VALUES (14recover1); INSERT INTO `RoomID` (`RoomID`, `State`) VALUES (15Magne1); INSERT INTO `room_ info` (`RoomID`, `State`) VALUES (16Magne1); INSERT INTO `room_ info` (`RoomID`, `State`) VALUES (17Magne1); INSERT INTO `room_ info` (`RoomID`, `State`) VALUES (181); INSERT INTO `room_ info` (`RoomID`, `State`) VALUES (19.1); INSERT INTO `RoomID`, `State`) VALUES (20.1)

Randomly select a resource from the resource table and modify the status.

There are not many records in the resource table, about 1w rows.

Create a normal index

Create index inx_1 on room_info (roomid,state)

This index is very important.

Without this index, multiple rows may be locked.

Get a random row and modify the resource 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

It is important to note that when executing the following SQL, it is not locked.

Select RoomID from room_info where state=1 order by rand () limit 1

So in a multithreaded environment, conflicts may occur.

Therefore, it is necessary to judge the number of rows affected by update. If the number of affected rows is 0, the resource is locked by someone else. You need to get it back.

If the number of affected rows is 0, remember that you can't get @ roomid to use it directly, but need to rerun it.