Re: Transaction Question

On Wednesday 03 December 2003 08:08, John Sidney-Woollett wrote:
> I have to convert an java web application currently using an Oracle DB
> back end to one using a Postgres backend.
[snip]
> Issue - nested transactions
> =====
>
[snip]
> 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.

Can you elaborate on what this counter is/how you are using it? It sounds like
the "counter" gets incremented regardless of whether an insert/update gets
committed, which makes me wonder what it is counting.

> 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.

Hmm - we don't seem to have any items dealing with concurrency issues on
techdocs.postgresql.org, which is a shame since they are exactly the sort of
thing benefit from having examples of pitfalls.

--
Richard Huxton
Archonet Ltd