Consultor Eletrônico

Status: Unverified

FACT(s) (Environment):

Oracle DataServer

SYMPTOM(s):

Program generating error "unique constraint violated" as well as error 121

The program below run within 2 sessions might generate errors
* unique constraint (<owner>.Y_Z_SETT##Y_Z_SETT1IX) violated

As expected, the following message will regularly appear
y_z_sett in use by on tty ?. Wait or press CTRL-C to stop. (121)

DEF VAR i AS INT.

DO i=1 TO 500 TRANSACTION:
RUN newcreate.
END.


PROCEDURE newcreate:
DEFINE VARIABLE vSeqNr AS INTEGER NO-UNDO.

DO ON STOP UNDO, RETURN ERROR
ON ERROR UNDO, RETURN ERROR:

FIND LAST y_z_sett EXCLUSIVE-LOCK NO-ERROR.

vSeqNr = (IF AVAILABLE y_z_sett THEN y_z_sett.seq_nr_log ELSE 0) + 1.
CREATE y_z_sett.
ASSIGN
Y_z_sett.adm_nr = 5367
y_z_sett.seq_nr_log = vSeqNr.
END.
END PROCEDURE.

Oracle table, sequence and indexes:

CREATE TABLE y_z_sett (
seq_nr_log number,
adm_nr number NOT NULL,
U##group_name varchar2 (10),
group_name varchar2 (10),
progress_recid number null
);
CREATE SEQUENCE y_z_sett_SEQ START WITH 1 INCREMENT BY 1;
CREATE UNIQUE INDEX y_z_sett##progress_recid ON y_z_sett (progress_recid);
CREATE UNIQUE INDEX y_z_sett##y_z_sett1ix ON y_z_sett (adm_nr, U##group_name, seq_nr_log);
CREATE UNIQUE INDEX y_z_sett##y_z_settix ON y_z_sett (seq_nr_log);

CAUSE:

When one client creates a new record, the other client will try to lock this new "last" record not released.

FIX:

Modify the procedure "newcreate" as, for example:

PROCEDURE newcreate:
DEFINE VARIABLE vSeqNr AS INTEGER NO-UNDO.
DO ON STOP UNDO, RETURN ERROR
ON ERROR UNDO, RETURN ERROR:

FIND LAST y_z_sett NO-LOCK NO-ERROR.
IF AVAILABLE y_z_sett THEN DO:
vSeqNr = y_z_sett.seq_nr_log.
RecordLock:
REPEAT:
FIND LAST y_z_sett WHERE y_z_sett.seq_nr_log >= vSeqNr
EXCLUSIVE-LOCK NO-ERROR.
vSeqNr = IF AVAILABLE y_z_sett THEN y_z_sett.seq_nr_log ELSE 0.
FIND LAST y_z_sett WHERE y_z_sett.seq_nr_log >= vSeqNr
NO-LOCK NO-ERROR.
IF vSeqNr = y_z_sett.seq_nr_log THEN LEAVE RecordLock.
END.
vSeqNr = y_z_sett.seq_nr_log.
END.

CREATE y_z_sett.
ASSIGN
Y_z_sett.adm-nr = 5367
y_z_sett.seq_nr_log = vSeqNr + 1.
END.
END PROCEDURE.