Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
5 replies Latest Post - ‏2012-12-17T07:05:35Z by SystemAdmin
SystemAdmin
SystemAdmin
1143 Posts
ACCEPTED ANSWER

Pinned topic UNIQUE INDEX causes INSERT INTO to fail

‏2012-12-06T13:29:37Z |
We use IDs for our tables and we fill them with a trigger.

Problem is that we get from time to time an error from the database when multiple tasks try to insert many rows into the table. (SQLSTATE S1000, sqldbcode 271 - Could not insert new row into the table)

Without the unique index everything works fine.

My question is: why does the index causes this error?

Simplified we have the following database:

CREATE TABLE adr (id integer not null, address char(50);

CREATE SEQUENCE adr_s1;

CREATE FUNCTION adr_id ( aid integer) RETURNS INTEGER;
DEFINE sid INTEGER;
IF aid is null THEN
SELECT adr_s1.nextval INTO sid FROM dual;
ELSE
let sid = aid;
END IF RETURN sid;
END FUNCTION;

CREATE TRIGGER adr_trg INSERT ON adr REFERENCING NEW AS neu
FOR EACH ROW
(execute function adr_id (neu.id) into id);

CREATE UNIQUE INDEX adr_idx adr (id ) ;

Any help is very apreciated!
Updated on 2012-12-17T07:05:35Z at 2012-12-17T07:05:35Z by SystemAdmin
  • andreasl
    andreasl
    369 Posts
    ACCEPTED ANSWER

    Re: UNIQUE INDEX causes INSERT INTO to fail

    ‏2012-12-07T16:52:02Z  in response to SystemAdmin
    Hi Olaf,

    are u in a position to open a PMR (support case) with IBM?

    If so I'd suggest doing so.

    What you're describing sounds like the same value from a sequence might be getting assigned more than once.

    On the other hand:
    what if a valid aid is passed into adr_id() and that's being used - what would keep the sequence to return that same value later when a NULL aid is passed?
    Or the other way round?

    Cheers,
    Andreas
    • andreasl
      andreasl
      369 Posts
      ACCEPTED ANSWER

      Re: UNIQUE INDEX causes INSERT INTO to fail

      ‏2012-12-07T16:59:02Z  in response to andreasl
      "what would keep the sequence from returning that same value" this must be!
  • SystemAdmin
    SystemAdmin
    1143 Posts
    ACCEPTED ANSWER

    Re: UNIQUE INDEX causes INSERT INTO to fail

    ‏2012-12-13T12:54:49Z  in response to SystemAdmin
    Hi Andreas,

    thanks for your answer!

    We indeed get an -239 (duplicate) error!

    And 'yes' we did open an support case with IBM.

    The funny thing is i get the 239 when i execute the following statement a thousand time in two differnt sessions at the same time:

    insert into adr (address) values ('street1')

    When i do the following instead everything is ok. There are no double values in table a_test:

    insert into a_test execute function adr_id(NULL)

    Whats going on here?????

    Greets, Olaf
    • andreasl
      andreasl
      369 Posts
      ACCEPTED ANSWER

      Re: UNIQUE INDEX causes INSERT INTO to fail

      ‏2012-12-13T15:48:16Z  in response to SystemAdmin
      Hi Olaf,

      reproduces nicely!

      Defect has been entered. Please update your PMR accordingly and escalate it to level 2 support in case this is urgent for you.

      Thanks,
      Andreas
  • SystemAdmin
    SystemAdmin
    1143 Posts
    ACCEPTED ANSWER

    Re: UNIQUE INDEX causes INSERT INTO to fail

    ‏2012-12-17T07:05:35Z  in response to SystemAdmin
    Andreas, i just wanted to let you know what IBM suggest to do. Let me know if you understand German otherwise i translate the most important things:


    Hallo,

    es war eine harte Nuss bis ich verstanden habe, was hier intern schief
    läuft.
    Es ist wirklich ein Timing Problem so weit ich dies sehe. Der Wert wird
    eingetragen während ein zweiter Task innerhalb der Instanz die ID ermittelt
    und übergibt.
    Bis da hin kommt der Record erst einmal mit "leerer ID" in die Tabelle, bis
    dann die ID nachgereicht wird und diese dann zum Commit eigentlich stimmt
    und konsistent ist.
    Es gibt also weder doppelte Werte aus der Sequence, noch versucht jemand
    leerer IDs in die Tabelle einzutragen. Es ist nur ein Zwischenzustand ...
    und wenn die beiden
    Gegner schnell genug sind, dann versuchen dies beide und dann kommt der
    UNIQUE-Check und schlägt zu.

    Wenn ich nun zum UNIQUE INDEX noch einen UNIQUE CONSTRAINT schalte, dann
    ist dieser das Mass der Dinge innerhalb von Transaktionen und damit kann
    ich
    den CONSTARINT-Check auf das Commit verlagern ... was dann bewirkt, dass
    dann ja ALLES wieder OK ist.

    Meine Änderungen sind damit:

    Create unique index adr_idx on adr (id);
    alter table adr add constraint unique(id); <--- also
    ein CONSTRAINT zum INDEX dazu ...

    set lock mode to wait 5;
    set isolation to cursor stability;
    begin work;
    set constraints all deferred; <--- und dann
    die CONSTARINTS auf DEFERRED setzen ... also TEST erst mit COMMIT statt
    sofort
    insert into adr(address) select tabname from systables;
    commit work;

    Create unique index adr_idx on adr (id);
    alter table adr add constraint unique(id);

    Mit freundlichen Grüßen / Kind regards
    Gerd