I have to convert an java web application currently using an Oracle DB
back end to one using a Postgres backend.
In Oracle much of the application logic is abstracted away from the java
middleware layer using stored procedures within the Oracle database. There
are certain features in Oracle that appear to be missing from Postgres
which are causing us some concern, and we wondered how much we're going to
have to butcher the backend and db stored procs.
Issue - nested transactions
=====
Oracle provides the #pragma autonomous hint which allows you to declare
that a procedure/function will run in its own transaction and which can be
committed regardless of the final commit/rollback state of the enclosing
transaction.
This is an issue for us because some procedures make use of a function
which issues a row level lock on a table (select ... for update) in order
to read and then update a counter, and which then commits to release the
lock. The nested function returns the new counter value on return. We
cannot use Sequence objects, because the counter is tied directly to the
record which contains it, and there are any number of these record types.
We have the function being called by many threads simultaneously, and if
the lock is only released at the end of the enclosing transaction, then
the subsequent calls after the first will block until the first completes.
In other words, although threads are making calls in parallel, they will
only run serially because of the bottleneck.
I have seen a note about using separate connections/threads to resolve
this issue. There is NO possibility of our java middleware using two
threads/connections to separate out the transaction as the idea is that
the java makes one call to the database, and it handles all concurrency
issues (beautifully) without us having to embed db specific code/logic in
the middleware.
Is there a simple/elegant solution to this problem? And is there a good
document on dealing with concurrency issues - I have read the manual for
7.4 and while it describes the transaction isolation levels, and MVCC - it
doesn't really offer any practical tips or solutions to this problem.
Thanks for any info anyone can provide.
John Sidney-Woollett