Question 113440 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to limit the occurrence of a value in rows when insert with multi threads

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)

enter image description here

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?

Tags:

2

Answers


  1. 0

    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') < 5

    Relational databases are ACID, which in this case means that if two such queries are run simultaneously, because they are operating on the same entity, they will effectively be forced to be sequential operations (not concurrent), so one transaction will complete before the other, and the other will "see" the data written by the first and so determine the count as 5.

    Login or Signup to reply.
  2. 0

    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 ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search