Forum Stats

  • 3,872,606 Users
  • 2,266,458 Discussions
  • 7,911,262 Comments

Discussions

Unusual Primary Key Constraint violation

476328
476328 Member Posts: 71
edited Sep 5, 2011 7:53AM in APEX Discussions
When using Apex collections from time to time this unique constraint error happens.

ORA-00001: unique constraint (FLOWS_030000.WWV_FLOW_COLLECTIONS_UK) violated

Now, I can't figure out why this constraint would be happening as we check for the existence of a collection before trying to create one, and the constraint relies on a number of values which we can't modify.

All of our collection operations are done using the following APEX_COLLECTION calls
COLLECTION_MEMBER_COUNT
COLLECTION_EXISTS
CREATE_OR_TRUNCATE_COLLECTION
ADD_MEMBER
DELETE_MEMBERS

As I don't modify any data inside of the collections using any methods other than these above API calls I'm a bit confused.

It's almost impossible for me to reproduce the error as it happens very rarely, in addition, most of the time performing the same action again after the constraint error has happened will succeed.

Any ideas what I might be doing that could cause this?

Thanks in advance,
Joe
«1

Comments

  • 60437
    60437 Member Posts: 16,564
    Joe,

    Can you give us more details? Are the sessions authenticated? Is it an after-submit process where this happens. What's the exact code? DB version? RAC? Apache setup, anything unusual? Load balancing?

    Scott
  • 476328
    476328 Member Posts: 71
    The sessions are authenticated.
    The processing happens in a page PL/SQL region called "Page Setup" which is the first region on a page.

    The Oracle DB version is 10.2.0.1.
    Apache setup is pretty much exactly the same as out of box install of Apex except we have turned on gzip compression.

    We also have multiple Apex installs running through the same Apache server setup with their own caches.

    No load balancing.

    Unfortunately I can't provide the code right now, I'll work on creating a version of the code that I can post here.

    In the mean time any advice would be great.

    Thanks in advance,
    Joe
  • 476328
    476328 Member Posts: 71
    Just a second note, we use our own authenticator. Though the sessions are authenticated, I'll keep an eye out for problems in there as well.
  • 476328
    476328 Member Posts: 71
    edited Jul 5, 2007 10:10AM
    I was just able to recreate the problem

    We create a procedure run by an AJAX call which contains the following code
    IF HTMLDB_COLLECTION.COLLECTION_EXISTS( p_COLLECTION_NAME ) THEN
        HTMLDB_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION( p_COLLECTION_NAME );
    END IF;
    Then, if the ajax fires very quickly in succession from a browser (for example if there's an onclick event which is written poorly and "clicks" twice for a single click) so that two HTTP GET(or POST) events hit the server in quick succession, it seems that both calls to the procedure get past the "if collection exists" check. If so they both hit "create or truncate my collection" but somewhere inside of there something bad happens.

    If I change the code to:
    IF HTMLDB_COLLECTION.COLLECTION_EXISTS( p_COLLECTION_NAME ) THEN
        HTMLDB_COLLECTION.TRUNCATE_COLLECTION( p_COLLECTION_NAME );
    ELSE
        HTMLDB_COLLECTION.CREATE_COLLECTION( p_COLLECTION_NAME );
    END IF;
    the error doesn't seem to happen.. though that could be because I'm not racing fast enough.

    I'm really not sure how to mitigate this except to surround the block with an exception handler to abort on the unique constraint error. It would stand to reason that if the problem could possibly exist inside CREATE_OR_TRUNCATE_COLLECTION that all I've done with my second example above is move the problem into my own code... but I can't seem to reproduce the error with the change listed above.

    Any suggestions would be great.

    Thanks!
    Joe

    Message was edited to make the code easier to read:
    joe.bauser
  • partlycloudy
    partlycloudy Member Posts: 8,175 Silver Trophy
    edited Jun 25, 2007 6:46PM
    Just some observations...

    Wrapping a call to create_or_truncate API inside "IF collection_exists" is unnecessary. I would suppose the create_or_truncate_collection API is implemented to first call the same collection_exists API and if that succeds to call truncate_collection otherwise to call create_collection.

    In other words,
    HTMLDB_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(p_COLLECTION_NAME);
    is 100% equivalent to
    IF HTMLDB_COLLECTION.COLLECTION_EXISTS( p_COLLECTION_NAME ) THEN
    HTMLDB_COLLECTION.TRUNCATE_COLLECTION( p_COLLECTION_NAME );
    ELSE
    HTMLDB_COLLECTION.CREATE_COLLECTION( p_COLLECTION_NAME );
    END IF;
    Besides, the AJAX implemented in APEX is really "JAX" i.e. it issues synchronous XMLHTTP requests. The second reqeust is issued only when the first one has completed. So I am not sure I fully understand the reason you are getting the PK violation in the first place.
  • 476328
    476328 Member Posts: 71
    edited Jun 26, 2007 10:01AM
    I'm aware the solution should be 100% the same, however I receive the PK violation fairly consistently when I use:

    CREATE_OR_TRUNCATE_COLLECTION

    I have yet to see it when I use the second solution.

    Or more to the point, any time a page is queried using AJAX and that page utilizes a collection, we have intermittently received the PK violation listed above. Every time we see this we've noticed calls to:

    HTMLDB_APPLICATION.CREATE_OR_TRUNCATE_COLLECTION

    and have replaced them with my second example below. Those pages then fail to reproduce the error.

    Like I mentioned above, this makes no sense because the code should be functionally which is why I bring it up.

    Also I'm aware the HTMLDB AJAX is not Asynchronous.
    We're using AJAX with help from the Prototype javascript library, so let me assure you that it is indeed asynchronous.

    I'm working on reproducing the problem remotely on apex.oracle.com but I haven't had time to touch it since my last post.
  • partlycloudy
    partlycloudy Member Posts: 8,175 Silver Trophy
    so let me assure you that it is indeed asynchronous

    OK then in that case, the worst case is that both "threads" happen to call your on-demand application process at the exact same instant. If they both do create_or_truncate_collection and find that the collection doesn't exist and proceed to create it (create a row in the wwv_flow_collections$ table), the first one would succeed and the second one would get that PK violation.

    What might be happening is that the second variant of the code with the IF/THEN/ELSE checks introduces enough of a delay in the code for the second process to hit that code, see that the collection already exists and proceed to truncate it (i.e. delete rows for the collection from the collection_members table).

    I would say go ahead and use code in your second example since that is more resilient to this race condition.

    Perhaps someone from the APEX team will chime in with some more insight.

    [This does raise some interesting questions about how to properly handle these situations when doing heavy asynchronous xmlhttp]
  • 60437
    60437 Member Posts: 16,564
    I'm not sure if this will help but here's some more info:

    1. The constraint being violated is not a primary key constraint but a unique constraint as defined by:
    alter table wwv_flow_collections$
    add constraint wwv_flow_collections_uk
    unique(session_id, user_id, flow_id, collection_name, security_group_id)
    /
    2. The CREATE_OR_TRUNCATE_COLLECTION procedure first checks for the existence of a collection with those 5 keys. If found, it does:
        delete wwv_flow_collections$ where id = <the id of the existing collection>;
    This delete cascades to rows in wwv_flow_collection_members$.

    Then, whether the "truncate" occurred or not it does this:
        insert into wwv_flow_collections$( collection_name ) values( upper(p_collection_name));
    3. The TRUNCATE_COLLECTION procedure requires that the named collection exists, fetches its ID and then:
    delete wwv_flow_collection_members$ m where m.collection_id = <the id of the existing collection>;
    4.Of the two scenarios Joe showed us, the first one creates the failure condition (assuming two concurrent sessions) by allowing an insert to be performed, i.e., the "create collection" action. Even if the first inserter takes a while to commit, the second one will wait on a lock until the first inserter commits, then it will attempt an insert and raise the UK violation. In the second scenario, nobody ever inserts; only deletes from collections_members$ are issued (assuming the create_collection procedure is never called).

    Scott

    P.S. My head hasn't hurt this bad since Vikas's background job puzzler (1231385
  • 60437
    60437 Member Posts: 16,564
    Vikas - In case you missed this, thought you'd be interested.

    Scott
  • partlycloudy
    partlycloudy Member Posts: 8,175 Silver Trophy
    The thread is already on my watch list because I just chimed in before you did! I am not sure either of us answered the OPs question conclusively, we just made some observations and clarified how APEX APIs work.
This discussion has been closed.