CODEWITHSUNDEEP
javasqlmultithreadingoracle-database
Aska
Aska

Reputation: 85

How can I prevent duplicated insert in oracle database with Java?

Let me give the example first. It is a log table.

User A subscribe the service A = OK
User A unsubscribe the service A = OK
User A subscribe the service A again = OK
User A subscribe the service A again = Not OK, because you can't subscribe same service at the same time.

Sometimes the client goes crazy and send 5 subscribe requests at the same time ( 4 tomcat servers behind), if I do nothing in this situation then 5 same records will be inserted.

As you can see, I can't use unique constraint here. I guess perhaps I can use some single thread block in Oracle, but not sure.. I tried "merge" , but I guess it is used in specific records instead of last record.

begin single thread

  1. select the last record
  2. if the last record is the same then don't insert.
  3. if the last record is not the same then insert. end single thread

Is it possible and how to achieve ?

Upvotes: 0

Views: 4472

Answers (4)

Evgeniy Dorofeev
Evgeniy Dorofeev

Reputation: 135992

I think you could solve this problem with constraint. When user subscribes it inserts a row when it unsubscribes it deletes it. A row must be unique for same user and same service.

If you do not want to delete rows add ACTIVE column to this table and make constraint on USER + SERVICE + ACTIVE.

Upvotes: 1

Aska
Aska

Reputation: 85

I tried "MERGE" and subquery to solve this case. By the way, this problem is only happened when subscribe. First, I get the status(subscribe or unsubscribe) from the last record of a user and the service. If the last status in table is 'subscribe', means this subscribed request might be the duplicated one.

MERGE INTO subscr_log M 
  USING
  (SELECT status
  FROM  subscr_log
  WHERE rid=
    (SELECT MAX(rid)
    FROM monthly_subscr_log
    WHERE SCRID    ='123456'
    AND service_item='CHANNEL1'
    )
  ) C 
ON (C.status ='SUB' ) -- try to see the last record is subscribe or not
WHEN MATCHED THEN
   UPDATE  SET M.REASON='N/A' WHERE M.STATUS='XXXXXXX' --do impossible sql
WHEN NOT MATCHED THEN
     INSERT VALUES  (9999,8888,'x','x','x','x','x','x','x','x','x',sysdate,'x','x','x','x');

Upvotes: 0

Baby
Baby

Reputation: 5092

  1. Perhaps you need to check for the user id, and service type. if same user trying to subscribe same service before the previous subcribed service is performed, then alert the user.

  2. or maybe you want to limit the user to subscribe in only some given duration, say: user can only subscribe same service in each 1 day

  3. You can update the record if the record already exist, for example:

Make a query to check if the record with particular user and service is exist:

SELECT * FROM table WHERE userid = userid AND serviceid=serviceid

If the query return any result, means its exist. then do update:

UPDATE table SET column1='value', column2='value2' ... WHERE userid = userid AND serviceid = serviceid

else, if no result returned, means the user haven't subscribe the service. then insert record:

INSERT INTO table(column1, column2, ...) values ('value1', 'value2', ...)

Upvotes: 1

Jorge_B
Jorge_B

Reputation: 9872

I do not fully understand your problem, but it seems you need to implement mutual exclusion somewhere. Have you tried with a SELECT ... FOR UPDATE?

http://www.techonthenet.com/oracle/cursors/for_update.php

Upvotes: 0

Related Questions