0

During race condition, How to validate number of rows inserted to table A based on permitted row count defined in table B.

#Table A
CAMPAIGN_ID     ISSUE_LIMIT_PER_USER
-----------     --------------------
1000000111      1

#Table B
OFFER_ID    USER_ID     CAMPAIGN_ID   START_DATE  END_DATE
--------    -------     -----------   ----------  --------
11111111    90000       1000000111    2021-06-27  2021-07-27   Request1 allowed 
22222222    90000       1000000111    2021-06-28  2021-07-28   Request2 disallowed
33333333    90000       1000000111    2021-06-29  2021-07-29   Request3 disallowed

NOTE: There is no UNIQUE constraint in table B. Because we can have multiple rows based on the value of ISSUE_LIMIT_PER_USER.

Requirement here is, how to prevent inserting more than 1 record (because ISSUE_LIMIT_PER_USER = 1) into table B when multiple parallel requests are made.

Below SQL I tried but not working.

INSERT INTO B(,,,,) SELECT (,,,,) FROM DUAL WHERE (SELECT COUNT(OFFER_ID) FROM B WHERE 
CAMPAIGN_ID=:campaign_id < :issue_limit_per_user

Because it is an INSERT query it can not lock anything and all parallel threads getting same count and finally inserting 1 row each. This solution works perfectly for other UPDATE use case.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Prabhat Kumar
  • 21
  • 5

1 Answers1

0

My recommendation is the following.

  1. Create a new table with one row per campaign and per user.
  2. Populate this table with the "used" limit from A.
  3. Also have a column which is the number of "used" rows. Default the value to 0.
  4. Add a check constraint used_issues <= issue_limit.
  5. Create a trigger on B to keep the value up-to-date for inserts, deletes, and updates.

Voila! The limit will be verified by the check constraint.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • If the option is to add trigger on B, why do we need new table ? Why can't that trigger validate the row count in table B with limit in A in INSERT ? – Prabhat Kumar Jun 19 '21 at 15:43
  • @PrabhatKumar . . . Because the limitation is *per user*. Table `A` does not have a "user" column. – Gordon Linoff Jun 19 '21 at 17:38
  • my point is a trigger after insert on B can count the number rows for the user_id, campaign_id combination and can validate against table A. – Prabhat Kumar Jun 19 '21 at 19:16
  • @PrabhatKumar . . . Not in Oracle. you'll get a mutating table error. The way I described might be the simplest solution. – Gordon Linoff Jun 19 '21 at 19:18
  • … thank you for the solution, it helped me to see through a different window. I will be happy to understand the challenge with only trigger. It will be grate if you can help me to understand the mutating table error. – Prabhat Kumar Jun 19 '21 at 19:23
  • 1
    Now I understand- A trigger can not change a table that it has read from. This is the mutating table error issue. – Prabhat Kumar Jun 19 '21 at 19:34