CODEWITHSUNDEEP
mysqltransactions
Theodosis
Theodosis

Reputation: 31

Restrict insertion based on a count

So, I need to safely restrict the insertion of entries in a table based on the count of other entries in that same table. Say we have the following table:

resource:(id, foreign_key)

I need to create up to a number of entries based on the foreign key. So, as soon as I reach a count, let's say 100 for our example, I want to restrict creating more entries.

The obvious answer would be something like that:

  1. count the entries with the specified foreign key.
  2. if count < limit insert the new entry

And in fact, that's what I have been using. The thing is, this approach is not fail-proof since between 1 and 2 there might occur another insertion. I considered the possibility of using transactions but (unless I'm completely misunderstanding transactions) this has the same issue:

  1. start transaction
  2. insert the new entry
  3. if entries have exceeded the limit, rollback. otherwise commit

Now, say we already have 99/100 entries and two transactions run at the same time. They both will commit since they don't see each-other's entries.

Short of actually creating the entry and then delete it if it's invalid (which feels kindof messy in my mind) I can't think of a way to solve this issue. Any ideas?

edit: upon request I'm providing sample data:

table1 +-------------+------------------+------+-----+----------------+ | Field | Type | Null | Key | Extra | +-------------+------------------+------+-----+----------------+ | id | int(10) unsigned | NO | PRI | auto_increment | | limit | int(10) unsigned | NO | MUL | | +-------------+------------------+------+-----+----------------+

table2 +-------------+------------------+------+-----+----------------+ | Field | Type | Null | Key | Extra | +-------------+------------------+------+-----+----------------+ | id | int(10) unsigned | NO | PRI | auto_increment | | foreign_id | int(10) unsigned | NO | MUL | | +-------------+------------------+------+-----+----------------+

and some sample data:

table1 +----+----------+ | id | limit | +----+----------+ | 1 | 5 | +----+----------+

table2 +----+---------------+ | id | foreign_id | +----+---------------+ | 1 | 1 | +----+---------------+ | 2 | 1 | +----+---------------+ | 3 | 1 | +----+---------------+ | 4 | 1 | +----+---------------+

At this point, let's say that two users attempt to create table2 entries. The first one will have to be accepted and the 2nd rejected.

With the first approach, if both users go through step 1 (counting the old entries) and then through step 2 (insert the new entry) both entries will be created.

With the second approach, if both of them run at the same time, they both will count 4 slots before themselves and commit instead of one of them rollbacking.

Upvotes: 1

Views: 98

Answers (1)

Zymon Castaneda
Zymon Castaneda

Reputation: 759

Halo Mate, a Stored Procedure similar to this structure may help you

UPDATE 

DROP PROCEDURE IF EXISTS sp_insert_record;
DELIMITER //
CREATE PROCEDURE sp_insert_record(
    IN insert_value1 INT(9),
    IN chosen_id INT(9)
)
BEGIN
    SELECT id, `limit`
    INTO @id, @limit
    FROM table1
    WHERE id = chosen_id;

    START TRANSACTION;

        INSERT INTO table2 (id, foreign_id)
        VALUES (insert_value1, chosen_id);

        SELECT COUNT(id)
        INTO @count
        FROM table2
        WHERE foreign_id = @id;

    IF @count <= @limit THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
END//
DELIMITER ;

By using a Stored Procedure, you can also add any validation or process based on your requirements.

Hope this can be of help, cheers!

Upvotes: 0

Related Questions