1

I have a perl script which is run parallel.

ls -lrt INCOMING | grep .out | head -${PARALLEL_COUNT} |  awk '{print $9}' | parallel "./new_control.pl" {}

Script proccessing csv files which coresponds server data. When the script is running parallel I have exception

Uncaught exception from user code:
DBD::Oracle::st execute failed: ORA-00001: unique constraint (PRE.SYS_C006373) violated (DBD ERROR: OCIStmtExecute) [for Statement "INSERT INTO DISK
(
    ID,
    RECORD_ID,
    MOUNT_POINT,
    TOTAL_SIZE,
    USED,
    AVALIABLE,
    CAPACITY,
)
VALUES ((SELECT NVL(MAX(ID)+1, 1) FROM DISK), ?, ?, ?, ?, ?, ?, ?, ?, ?)

I suppose that the problem is ID column where is inserting incremented value. 

(SELECT NVL(MAX(ID)+1, 1)

and the same id is already also inserting by the other script instance.

I tried to change AutoCommit but without any positive result

our $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid;port=$port", $username, $password,
{
    RaiseError => 1,
    AutoCommit => 1 # this will need to call commit manually (if value is 0) after changes in DB.
});

When the script is not run parallel everythink is ok. I don't want to generate those id's before I run the script parallely.

Community
  • 1
  • 1
Peter F
  • 59
  • 5
  • 1
    Make an explicit $dbh->commit after each insert. Or change the field definition of ID to sequence. – ulix Nov 01 '18 at 17:34
  • 1
    Does Oracle not support an autoincrementing integer key so you don't have to try to pick a value manually? (Edit: Yes in recent versions, can be worked around in older ones. See https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle ) – Shawn Nov 01 '18 at 18:12
  • @ulix $dbh->commit after each insert did not work as I expected but the problem was solved by the change definition of ID to sequence. I used solution which Shawn pasted. Thanks a lot guys! – Peter F Nov 02 '18 at 07:24

1 Answers1

0

Assuming what you're trying todo here is make the data load quicker, you would be better off using the sqlldr tool ( the oracle bulk loading tool ).

Apart from anything else this is almost certainly IO bound, putting insert statements in multiple cores will do nothing to make it faster, and will create new problems like the one you have above.

jtym
  • 49
  • 4