6

I have an issue: I have table T with one column with unique constraint

CREATE TABLE T (ID NUMBER,
                UNIQUE (ID));

Session 1 done insert into that table

INSERT INTO T(id) VALUES(1);

Session 2 is trying to MERGE the same value to that table

 MERGE INTO t
 USING (SELECT 1 col FROM dual) s
    ON (t.id = s.col)
  WHEN NOT MATCHED THEN 
INSERT (id) VALUES (col);

At that moment Session 2 is blocked and waiting for Session 1 to be committed or rollbacked. Now I run in Session 1

COMMIT;

At that moment an error occurred in Session 2

ORA-00001: unique constraint violated

Is there any options how can I avoid it?

P.S. the problem is that I have INSERT into some table and MERGE (using UNIQUE columns in ON section) at the same table. This INSERT and MERGE are called separately in two different sessions. And sometimes MERGE falls because of situation described upper. I hope I described it understandably

Tatiana
  • 1,489
  • 10
  • 19
  • Seems like these two sessions can't run in parallel, might have to run sequentially and wait for session 1 to finish first and then launch session 2, would that be an issue? – Jacek Trociński May 04 '17 at 07:54
  • How value for id is generated, and what is wrong with `ORA-00001` – Arkadiusz Łukasiewicz May 04 '17 at 07:56
  • @Arkadiusz Łukasiewicz as I said - ID is just an example. Actually I have UNIQUE constraint on 3 columns. I made `INSERT` in session 1. After it I run `MERGE` with the same values, but merge have this 3 unique columns in ON section (so in normal situation MERGE goes to `WHEN MATCHED` section and runs successfully). Now MERGE is waiting for session 1 to be committed. After commit MERGE doesn't control ON clause, it just trying to made INSERT (goes to `WHEN NOT MATCHED` section) and falls on ORA-00001. – Tatiana May 04 '17 at 08:01
  • 1
    Just try repeating MERGE a limited number of times till it succeds or the limit is reached. In the later case notify the caller that it's wrong time to MERGE (a batch of INSERTs is running) – Serg May 04 '17 at 08:05
  • "This example is simplified. So "use the sequence" will not helps - I can't use it in my system, I have a UNIQUE constraint on 3 columns." **I suggest you make your question about the actual unique constraint.** Simplification can be good, but in this case you have simplified away your requirements, making the question unanswerable. –  May 04 '17 at 08:07
  • @Tatiana How values for three(uniqe) columns is generated for insert and for merge. This is a key question. – Arkadiusz Łukasiewicz May 04 '17 at 08:13
  • @Serg it is a solution but really bad - what if I need to change something in MERGE statement? I will have to control it everywhere and could miss something... – Tatiana May 04 '17 at 08:14
  • @Arkadiusz Łukasiewicz it is some identification number, date and type of row I inserted. – Tatiana May 04 '17 at 08:16
  • @dan1111 thank you for your comment, I updated the question – Tatiana May 04 '17 at 08:20
  • @Tatiana How your unique column are generated? Aare you receiving data from external system or using own logic. Add this information because question is to general. – Arkadiusz Łukasiewicz May 04 '17 at 08:30
  • @Arkadiusz Łukasiewicz I receive data from external system – Tatiana May 04 '17 at 08:33
  • @Tatiana, I still don't think there is enough info here. I think the real problem is probably upstream of this (What is the id you are receiving and what are you trying to accomplish with these two queries?) –  May 04 '17 at 08:51
  • First thought Create transition, staggin or buffering table. (There are a lot of names for this.). The same structure but without any constraints. New data always go into new table. (Only insert without update). Next process constantly merge data into old destination. – Arkadiusz Łukasiewicz May 04 '17 at 10:31
  • 2
    You can [`lock table t in exclusive mode`](https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9015.htm) in first session. – Ponder Stibbons May 04 '17 at 10:49
  • 1
    @PonderStibbons , apparently locking the whole table may degrade the whole system perfomance. This can only be a solution in a very special case. – Serg May 04 '17 at 11:27
  • @Serg - I agree. Everything is described in the documentation. It's just an idea that OP can test. – Ponder Stibbons May 04 '17 at 11:46
  • 2
    I think people missing the point about the question, it isn't about how the data is derived, but how to overcome the situation where a value is waiting to be inserted into a tale whilst looks correct, but will fail once the pending waited on transaction completes. I think this is about TRANSACTION ISOLATION levels, in particular dirty reads. To keep things simple, could you catch DUP_VAL_ON_INDEX exception knowing this this situation could arise and re-attempt the MERGE? See here http://www.oracle.com/technetwork/testcontent/o65asktom-082389.html . – TenG May 04 '17 at 13:02
  • @TenG thank a lot for understanding the main problem :) Your suggestion is exactly what I did. I don't really like this solution - will execute MERGE twice in some cases, but at least it works. – Tatiana May 04 '17 at 13:19
  • then you could try doing a `select for update` first before the merge that will only lock the records you are interested in?!? but maybe that doesnt work for newly inserted records. – ShoeLace Oct 12 '17 at 06:51

1 Answers1

1

Your example is a subset of the phantom reads problem. Phantom reads and your problem are just a property of relational databases. I recommend reading chapter 7 of Kleppmann's Designing Data-Intensive Applications.

Your options are not to be taken lightly:

  1. Redesign the application with optimistic locks.
  2. Change the database isolation level to SERIALIZABLE, which will both slow individual transactions down and reduce the database's ability to run transactions in parallel.

In my experience, most designers choose the third option of living with the problem. Depending on your non-functional requirements, it can be better to keep your application simple rather than theoretically correct.

Steven Ensslen
  • 1,164
  • 9
  • 21