Re: non-explainable SQL-Error

A copy of this was sent to Peter Kroiß <pk_at_faw.uni-linz.ac.at> (if that email address didn't require changing) On Mon, 10 Jan 2000 16:51:21 +0100, you wrote:

>Sometimes an error occurs by using following statement, why? Normally
>this statement is ok, but sometimes I get the following error.
>
>[SERVERERROR] Error from Server: ORA-00001: unique constraint
>(USER.XPKOBJEKT_KEYWORD) violated
>
>INSERT INTO OBJEKT_KEYWORD(OBJEKT_NR, REIHUNG) SELECT 224183,1 FROM DUAL
>WHERE NOT EXISTS (SELECT OBJEKT_NR FROM OBJEKT_KEYWORD WHERE
>OBJEKT_NR=224183 AND REIHUNG=1)
>
>This statement inserts 2 values, but only if they are not allready in
>the table.
>
>
>

because in a multi-user environment, 2 people running this statement at or about the same time will try to insert the same values.

Oracle does NOT block reads from writes. Take the above INSERT and open 2 sqlplus sessions. Goto session 1 and paste the insert (do not commit). It'll work. goto session 2 and paste the insert. It'll BLOCK on the unique index you have -- but not on the query. Goto session 1 and commit. Session 2 will now get the error message about the unique constraint. Goto session 2 and paste the insert -- it'll insert zero rows this time since the row exists and has been committed.

If you are trying to insert a value and do not care if it exists are not (if it exists -- you wish to ignore any dup val on index errors) you should execute:

begin

    insert into x values ( 1, 2 );
exception

    when dup_val_on_index then NULL;
end;

Or, just ignore th ORA-00001 in the client code.

You might want to do a quick read through on the server concepts manual to see how we handle multi-user concurrency under the covers to get a better understand of exactly why this (your statement) cannot work in a multi-user environment.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jan 10 2000 - 10:40:38 CST