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

Question

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

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

Is it possible and how to achieve ?

@EvgeniyDorofeev mostly because unique constraint will still allow the service A subscribed repeatedly. — Baby
– Baby, Commented Dec 23, 2013 at 7:31
@EvgeniyDorofeev: Because user can subscribe and unsubscribe later and subscribe again even in one day. — Aska
– Aska, Commented Dec 23, 2013 at 7:41

Baby · Accepted Answer · 2013-12-23 08:39:38Z

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.
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
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', ...)

edited Dec 23, 2013 at 8:39

answered Dec 23, 2013 at 7:26

Baby

5,1123 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aska Over a year ago

Hi Rafa: It is a log table. Normally same user will subscribe a service, and unsubscribe it when he/she is not interested. Then there will be two records : one for subscribe and another for unsubscribe. However, there is an unknown bug in the client code(mobile device), it will send 5 subscribe requests at the same time. But I can only insert one record in this case. Otherwise there will be 5 subscribe logs for same user at the same time, which is not wanted.

Baby Over a year ago

perhaps you can update the current record i.e replacing it with the new same service request instead of adding new record

Aska Over a year ago

Hi Rafa : I guess your solution is "merge" ? If so, how can I write codes in USING block in this case? Normally it will be "(SELECT COUNT(*) AS CNT FROM SHELF_STATUS WHERE SID='abcd') C" or something, but in this case, what I need to do will be two steps : 1. query the last record for this user and this service 2. if this record is "subscribe" and user still want to subscribe, then update it like you said.

Evgeniy Dorofeev · Accepted Answer · 2013-12-23 15:54:42Z

1

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.

edited Dec 23, 2013 at 15:54

answered Dec 23, 2013 at 7:44

Evgeniy Dorofeev

137k31 gold badges209 silver badges288 bronze badges

2 Comments

Aska Over a year ago

Thanks for your suggestion, however, I can't delete those records. This table is for the report use, people would like to know customer behavior.

Evgeniy Dorofeev Over a year ago

But you can have a separate table for locking urposes

Jorge_B · Accepted Answer · 2013-12-23 07:37:46Z

0

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

edited Dec 23, 2013 at 7:37

answered Dec 23, 2013 at 7:18

Jorge_B

9,8622 gold badges19 silver badges22 bronze badges

3 Comments

Aska Over a year ago

Hi Jorge_B : If you subscribe some monthly service, say, "all you can watch", then you can't subscribe again until you unsubscribe it. The problem is one client (mobile device) sometimes send multiple same requests (it is a bug) at the same time, but I can only insert one subscribe log. In this case I have no rows to be locked, so it seems that I can't use select .. for update in this situation.

Jorge_B Over a year ago

Just an idea: have your log table work with an autonumeric sequence primary key and just log everything. This way you know when your multiple subscribe attemps bug happens and will be a way towards resolving it (instead of hiding it)

Aska Over a year ago

I would like to fix the bugs from APP, however, the IOS development team there can't reproduce such problems. There is a dialog to ask user to confirm if they want subscribe or not.When clicked, then user can't do anything until the process behind is done. So it should be OK in this case.

Aska · Accepted Answer · 2013-12-23 09:26:07Z

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

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related