MySQL: is there a way to limit the number of rows with a certain value without locking the whole tab-CodePudding

I have the following table

CREATE TABLE `FriendRelation` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `friend_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`user_id`, `friend_id`),
) ENGINE=InnoDB;

For a Twitter-like application. When Alice follows Bob, there will be a row inserted where user_id is Alice and friend_id is Bob.

Now I want to implement a follow limit, say 5. This means Alice can follow at most 5 friends and then every single insert will fail with user_id=Alice's id

My application process basically looks like the following

function follow(self, target) {
    begin transaction;
    select count(*) from FriendRelation where user_id=self;
    if (result is greater than 5) {
        rollback;
        return error;
    }
    insert into FriendRelation (user_id, friend_id) values (self, target);
    commit;
}

However the above process will result in more than 5 rows if two rows are inserted simultaneously. I think locking the whole table would resolve the problem but for a mature product like MySQL there must be a way to only lock the rows with a specific user_id while not affecting other user's insertions.

My thought is that there might be two possible ways: one is to ensure the "limit check" is on the MySQL side and not the application side. Another is to make use of locking and transaction while still keeping the "limit check" in application process. In these two ways I would prefer the latter, but the formal is also acceptable. I was not able to come up with the answer for either way.

Any help would be appreciated.

CodePudding user response：

CREATE TRIGGER limit_5_followings
BEFORE INSERT ON FriendRelation
FOR EACH ROW
BEGIN
    IF 5 <= ( SELECT COUNT(*)
              FROM FriendRelation
              WHERE user_id = NEW.user_id ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No more followings available.';
    END IF;
END

This trigger usage is free from concurrent insertion interference. Whoever managed to do it first will sign up, and the second one will get an error.

DEMO. Also CHECK constraint which does not allow to follow self is added.