It appears collections are mostly populated (and used) on submit of pages. There are use cases to populate a collection on render of a page, and this is where my problem lies. Colleagues at another site converged on the same issue.
Consider a query that takes a while to execute, this feeds the collection on page load using
IF APEX_COLLECTION.COLLECTION_EXISTS('P1_LIST') THEN
APEX_COLLECTION.DELETE_COLLECTION('P1_LIST');
END IF;
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B
(p_collection_name => 'P1_LIST'
,p_query => lc_sql
,p_names => la_names
,p_values => la_values
);
You could then use the results of that query in a number of charts, or allow quicker filtering on those pre-determined results that live in the collection.
The problem occurs (and it's a big problem) when the user might become impatient, start clicking elsewhere, then back on the page that generates the collection. This now causes a deadlock where only one session will win. Ultimately the error returned will be along the lines of
ORA-20101: Application collection ORA-20104
ORA-00060: deadlock detected while waiting for resource
I'm almost a little surprised I'm not finding more questions or content on this issue, so I thought I'd put it out there to see if anyone has feedback. The closest I could find are
APEX's own PL/SQL calls are locking
Tracking intermittent collection violations
but no real cause or resolution was found.
Our workaround was to define an application/page item that increments prior to building the collection (and never delete prior collections, until session cleanup)
ln_seq := coalesce(nv('P1_SEQ'),0)+1;
apex_util.set_session_state('P1_SEQ', ln_seq);
-- no delete_collection()
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B
(p_collection_name => 'P1_LIST'||ln_seq
...
This sequence becomes a suffix to the collection name, and all queries on the collection refer to the most recent collection created.
WHERE collection_name = 'P1_LIST'||:P1_SEQ
It seems like a dirty solution, but it's effective.
Anyone encountered this before, or have any thoughts?