APEX

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

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

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.

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

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.

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.

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]

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

Vikas - In case you missed this, thought you'd be interested.

Scott

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.

Well my initial question is answered I guess.

The question being: What am I doing that could cause this?

The answer being: I am using collection APIs in an asynchronous way in an environment which is not designed to support asynchronous access causing a race condition which can cause the error I'm experiencing.


New and more exciting question time:
How can I maintain the asynchronous nature of my application without causing this error?

Well, Scott confirmed that the implementation of the HTMLDB_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION API is "atomic".

Regardless of who gets to that API first, at the end of the API call, the end-result will be the same i.e. an empty collection (just a row in the collections$ table). No UK violation will be raised even when it is used concurrently or asynchronously.

however I receive the PK violation fairly consistently when I use: CREATE_OR_TRUNCATE_COLLECTION

So, I don't understand Joe's assertion (quoted above). Scott, do you?

Hope I'm addressing the right question: Why does the UK violation occur with CREATE_OR_TRUNCATE_COLLECTION? I explained that, it should be expected.

Scott

I was about to post an example describing my problem but my head just cleared and I see now that the exception should be expected.

Please disregard my second question.

If I'm using the application asynchronously I should assure that the asynchronous components are designed in such a way that they do not rely on the same collections otherwise "odd" things could happen.

Scott: You are right, my head hurts too! :-)

Hi,

I encountered the same problem, but not via an asynchronous AJAX call. I have a page which creates a collection. It takes a while to create this collection and the users were clicking the link to the page many times. Here's how I fixed it: (locked the collection)

[ul]
DECLARE
CURSOR x_cur
IS
SELECT c.collection_name
FROM apex_collections c
WHERE c.collection_name = :p_collection_name
FOR UPDATE;
BEGIN
-- Lock Collection.
OPEN x_cur;

CLOSE x_cur;

-- Create New Collection
apex_collection.create_or_truncate_collection (:p_collection_name);
-- Do Stull
END;
[ul]

Can you see any problems with this? If not, hope it helps!.

Hi,

I'm encountering the same problem with one of my pages (APEX 3.1.0, Oracle 10g).

An 'on-load before header' process uses the create_or_truncate_collection API. Occasionally, but frequently enough for the customer to notice, we are getting the WWV_FLOW_COLLECTIONS_UK exception being raised.

From your description, the API appears to:

1. Check for existence of collection;
2. DELETE from wwv_flow_collections$ with the collection name;
3. INSERT into wwv_flow_collection$ with the collection name;

In my case, this is then followed by a time-consuming population of the collection (my page is called from another simply to produce and display the collection).

My thought is that, if the page is resubmitted before the previous page 'run' has completed and committed, it is possible for both page 'runs' to attempt to perform the INSERT? That is to say, as the initial run has not committed its changes, both runs can legitimately see that the collection exists delete it and then insert a new collection. It's only when the page completes processing (some time later) that the second run receives the unique key validation.

Is there a simple way of dealing with this? Would setting the page 'prevent duplicate page submission' work for this case (I suspect that the use clicks for my page, 'nothing' happens whilst the collection is being populated and then the user re-clicks for my page)?

Thanks for any thoughts,