ORA-00001: unique constraint: 1node vs 3node

0
votes

We're using ORACLE 11.2.0.3.0, configured as 3 node RAC.

In our application we have hibernate over UCP and OJDBC with compatible version to RAC. Hibernate use some sequence to get ID for any record in database. I database we've got table with UNIQUE_CONSTRAINT (some_value) on it. It's used to synchronized many instance of application, every transaction in application requires unique row in this table. So application A tries to insert in this table (some_value="A"), if other application already inserted row with (some_value="A"), first instance get ORA-00001 unique constrain violated, and retry this with other value (some_value="B").

UNIQUE_CONSTRAINT fires very often. Like one in 8tx.

We run two tests:

service pinned to one node: response time avg 6ms
service on all 3 nodes: response time avg 800-1000ms

High level question is why? What is happening in 3 node RAC when UNIQUE_CONSTRAINT occurs, and why it's slowing down so much application. How can I diagnose this case?

Michal

2
sqloraclehibernateoracle11g

2 Answers

0
votes

Use service level scaling on RAC. Create a "LOADER" service the RAC side. Make this service active on one node only. And let hibernate use these service "LOADER" connections for loads.

The explanation is - very vague - each cluster node is mastering some subset of database's address space. When using unique constraint, each node must request data blocks of the unique index from it's mastering node. When a duplicit key is found and both duplicit keys were inserted via transactions which were not commited yet. Oracle has to enqueue one session and let it wait till the other session(belonging to another node) commits or rollbacks.

0
votes

If you need to generate a unique value, you should let the database do it for you. You can create an object called a SEQUENCE. You then get the next value of a sequence simply by

my_seq.nextval

And the current value of the sequence is simply

my_seq.currval

So if you are inserting record...

insert into my_table( my_seq.nextval, 'xxx', yyy, 123, ... )