How to limit the occurrence of a value in rows when insert with multi threads-CodePudding

I am facing a situation where I want to limit occurrence of a value in rows. For example, I want the maximum appearance of value V through the records in table T to be 5. Any insert statement that inserts V into the table after 5 should be rejected.

With single threading manner, I can check if the occurrence of V is less than 5 or not before inserting. But when insertions are conducted with multi threads, e.g. 100 threads, the application logic can be broken.

One solution is lock the table before any insert statement, however, it might not an optimal solution. Please share to me if you have any idea, thanks.

Update: Example

I have a table named persons with 2 columns (id, name)

Currently, the value Anna appears in 4 records. There are two threads trying to insert other Annas into the table at the same time. How to take just one and reject the other one?

CodePudding user response：

Combine the check with the insert so they happen simultaneously:

insert into mytable (name)
select 'Anna'
from (select 1) x
where (select count(*) from mytable where name = 'Anna') < 6

One thread will be executed first, making the other thread fail.

CodePudding user response：

hope triggers can help, check SIGNAL Statement:

drop trigger table_trigger_test;
DELIMITER $$

CREATE TRIGGER table_trigger_test
BEFORE INSERT ON table_trigger
REFERENCING NEW AS new

FOR EACH ROW
BEGIN
    DECLARE b INT;
    SET b = (SELECT COUNT(*) FROM table_trigger where name = new.name);
    IF (b >3)
    THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'over 4';
    END IF;

END$$

DELIMITER ;