Thread: Transaction Question

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

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

Here are two procedures converted from Oracle's PL/SQL (so the oracle
specific stuff (like #pragma autonomous) has gone.

This first function returns two values (it used to use an extra pair of
out parameters). You are correct in that the function SHOULD increment the
counter regardless of whether the enclosing transaction commits or not.
(Of course in Postgres this is not the case).

CREATE OR REPLACE FUNCTION GetVolumeFileReference (varchar, integer)
RETURNS integer AS '
  -- allocates a volume and unique file ID for storing a resource
  -- determines where to store a (file) resource according to the
allocation strategy
  -- pAllocStrategy can be either
  --   null, FIRST (default) = fill first available volume before using next
  --  DISTRIBUTE = distribute files across the available volumes

DECLARE
  pAllocStrategy     ALIAS OF $1;
  pSpaceReqd         ALIAS OF $2;

  vAllocStrategy     varchar(16) := pAllocStrategy;
  vVolumeID          integer := -1;
  vFileID            integer := -1;

BEGIN
  -- set defaults
  vVolumeID := -1;
  pFileID := -1;

  -- get the volume ID, and next file ID
  -- check what allocation strategy has been requested
  -- to locate the most appropriate volume
  if (upper(vAllocStrategy) = ''DISTRIBUTE'') then
      select WDVolumeID into vVolumeID
      from WDVolume
      where Writeable = ''Y''
      and DiskAvailPC >= 5
      order by DiskAvailPC desc
      limit 1;
  else
  -- use first volume with space available
      select WDVolumeID into vVolumeID
      from WDVolume
      where Writeable = ''Y''
      and DiskAvailPC >= 5
      order by WDVolumeID
      limit 1;
  end if;

  -- cannot find a free volume
  if vVolumeID is null then
    return -1;
  end if;

  -- now lock the volume (and wait for it to become free)
  select LastFileSeq into vFileID
  from WDVolume
  where WDVolumeID = vVolumeID
  for update;

  -- increment the file seq counter
  if (vFileID is null) then vFileID := 0; end if;
  vFileID := vFileID + 1;

  -- update the volume, and write the changed values back
  update WDVolume
  set LastFileSeq = vFileID
  where WDVolumeID = vVolumeID;

  RETURN lpad(vVolumeID, 10, '' '') || lpad(vFileID, 10, '' '');
END;
' LANGUAGE 'plpgsql';

Here is a stripped down version of the function that calls the one above -
there are many threads calling this function simultaneously:

CREATE OR REPLACE FUNCTION CreateFile (integer, varchar, varchar, integer)
RETURNS integer AS '
  -- creates a new file (if it does not exist)
  -- and returns the WDResource ID of the newly
  -- created file. If the file already exists
  -- then the resource ID is returned, otherwise
  -- if the file could not be created then -1 is returned

DECLARE
  pFolderID      ALIAS OF $1;
  pFilename      ALIAS OF $2;
  pContentType   ALIAS OF $3;
  pSize          ALIAS OF $4;

  vUserID        integer;
  vResourceID    integer := -1;
  vURL           varchar(255) := null;
  vVolumeID      integer := -1;
  vFileSeq       integer := -1;
  vRefPath       varchar(64) := null;
  vRefName       varchar(64) := null;
  vContentType   varchar(16) := pContentType;
  vSize          integer := pSize;
  vVolFileSeq    varchar(64) := null;

BEGIN
  -- get the parent folder information
  select WDUserID, URL into vUserID, vURL
  from WDResource
  where WDResourceID = pFolderID;

  if vURL is null then
      return -1;
  end if;

  -- check that the file doesnt exist already
  select WDResourceID into vResourceID from WDResource
  where URL = vURL||''/''||pFileName;

  -- if it does exist then return the ID
  if (vResourceID is not null) then
    return vResourceID;
  end if;

  -- determine the volume to locate the file on, and get the file
  -- sequence number
  vVolFileSeq := GetVolumeFileReference(''FIRST'', vSize);
  vVolumeID := to_number(trim(substring(vVolFileSeq, 1, 10)));
  vFileSeq := to_number(trim(substring(vVolFileSeq, 11, 10)));

  -- if any error occured here then abort
  if (vVolumeID = -1) or (vFileSeq = -1) then
    return -1;
  end if;

  -- guess the content type if not known
  if (vContentType is null) then
    vContentType := GuessContentType(pFilename);
  end if;

  -- create the unique file reference name
  -- this is the one stored on disk
  vRefName := getFileRefName(vVolumeID, vFileSeq, vContentType, pFilename);

  -- get the pathname from the file reference name
  vRefPath := getFileRefPath(vRefName);

  -- get the next resource ID
  select nextval(''SEQ_WDRESOURCE'') into vResourceID;

  -- set the content length (file size) to zero initially
  -- it will be updated after successfully writing the file
  -- using the UpdateDiskUsed(WDResourceID, newSize) procedure
  vSize := 0;

  -- create the new file resource
  insert into WDResource (WDRESOURCEID, WDPARENTID, WDUSERID, WDRESTYPEID,
URL, WDVOLUMEID,
    FILENAME, REFPATH, REFNAME, CONTENTTYPE, CONTENTLENGTH, LASTMODIFIED)
  values (vResourceID, pFolderID, vUserID, 2, vURL||''/''||pFilename,
vVolumeID,
    pFilename, vRefPath, vRefName, vContentType, vSize, now());

  return vResourceID;
END;
' LANGUAGE 'plpgsql';

I recognize that the above code may not be ana elegant way to code using
Postgres - but it is a fairly literal transation from the Oracle PL/SQL
code.

You can see that the call to

vVolFileSeq := GetVolumeFileReference(''FIRST'', vSize);

is going to be a bottleneck.

Is there some way of dealing with this issue? I cannot allow dirty reads
onto the value returned by this function.

I'm really unsure of how Postgres guarantees data consistancy with MVCC...

Thanks for any help.

John




Richard Huxton said:
> 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
>

On Wednesday 03 December 2003 11:01, John Sidney-Woollett wrote:
> Here are two procedures converted from Oracle's PL/SQL (so the oracle
> specific stuff (like #pragma autonomous) has gone.
>
> This first function returns two values (it used to use an extra pair of
> out parameters). You are correct in that the function SHOULD increment the
> counter regardless of whether the enclosing transaction commits or not.
> (Of course in Postgres this is not the case).
>
> CREATE OR REPLACE FUNCTION GetVolumeFileReference (varchar, integer)
> RETURNS integer AS '
[snip]
>   -- now lock the volume (and wait for it to become free)
>   select LastFileSeq into vFileID
>   from WDVolume
>   where WDVolumeID = vVolumeID
>   for update;
>
>   -- increment the file seq counter
>   if (vFileID is null) then vFileID := 0; end if;
>   vFileID := vFileID + 1;
>
>   -- update the volume, and write the changed values back
>   update WDVolume
>   set LastFileSeq = vFileID
>   where WDVolumeID = vVolumeID;

OK - here you are basically recreating what a sequence does. The difference
being that you have one sequence per "VolumeID".

Now, is "LastFileSeq" just used as a unique identifier, or does it have a
hidden meaning too (e.g. "approximate number of files created")? If it is
just a unique identifier, just share one sequence between all the volumes.

If it comes down to it, you can have many sequences, but I don't know how the
system copes if you have thousands or millions of them.

Oh - did you know you could return a pair of integers from your function? Use
CREATE TYPE to make a type containing two ints and then return that type.
Should save you a substring later (incidentally, I don't think integer will
go to 20 digits, so you'll have a problem there).

--
  Richard Huxton
  Archonet Ltd

Richard

You summized correctly. The field being updated is basically a sequence
per volume. BUT the sequences cannot be shared across the volumes...

I did wonder about "binding" one sequence object to each Volume record, and
modifying the function so that the identified the volume to use, and then
obtained the next value from the appropriate sequence object. Is it
possible to do the following:

--declare var to "hold" the sequence name
vVolSeqName := "SEQ_VOLUME_1";

--access the sequence from the name in the variable
select nextval(vVolSeqName) into vFileSeq;

If I cannot do this, can you suggest a solution to my original bottleneck
issue. And also the problem of the sequencing number being rolled back in
the event that the CreateFile function aborts or is rolled back.

However, for me a bigger area of confusion is how to deal with concurrency
issues in Postgres generally. Are there any good docs with examples of
different scenarios?

I appreciate all your help so far. Thanks.

John

Richard Huxton said:
> On Wednesday 03 December 2003 11:01, John Sidney-Woollett wrote:
>> Here are two procedures converted from Oracle's PL/SQL (so the oracle
>> specific stuff (like #pragma autonomous) has gone.
>>
>> This first function returns two values (it used to use an extra pair of
>> out parameters). You are correct in that the function SHOULD increment
>> the
>> counter regardless of whether the enclosing transaction commits or not.
>> (Of course in Postgres this is not the case).
>>
>> CREATE OR REPLACE FUNCTION GetVolumeFileReference (varchar, integer)
>> RETURNS integer AS '
> [snip]
>>   -- now lock the volume (and wait for it to become free)
>>   select LastFileSeq into vFileID
>>   from WDVolume
>>   where WDVolumeID = vVolumeID
>>   for update;
>>
>>   -- increment the file seq counter
>>   if (vFileID is null) then vFileID := 0; end if;
>>   vFileID := vFileID + 1;
>>
>>   -- update the volume, and write the changed values back
>>   update WDVolume
>>   set LastFileSeq = vFileID
>>   where WDVolumeID = vVolumeID;
>
> OK - here you are basically recreating what a sequence does. The
> difference
> being that you have one sequence per "VolumeID".
>
> Now, is "LastFileSeq" just used as a unique identifier, or does it have a
> hidden meaning too (e.g. "approximate number of files created")? If it is
> just a unique identifier, just share one sequence between all the volumes.
>
> If it comes down to it, you can have many sequences, but I don't know how
> the
> system copes if you have thousands or millions of them.
>
> Oh - did you know you could return a pair of integers from your function?
> Use
> CREATE TYPE to make a type containing two ints and then return that type.
> Should save you a substring later (incidentally, I don't think integer
> will
> go to 20 digits, so you'll have a problem there).
>
> --
>   Richard Huxton
>   Archonet Ltd
>

On Wednesday 03 December 2003 13:34, John Sidney-Woollett wrote:
> Richard
>
> You summized correctly. The field being updated is basically a sequence
> per volume. BUT the sequences cannot be shared across the volumes...

Why not? Are you worried about running out of numbers, or is there some other
reason?

> I did wonder about "binding" one sequence object to each Volume record, and
> modifying the function so that the identified the volume to use, and then
> obtained the next value from the appropriate sequence object. Is it
> possible to do the following:
>
> --declare var to "hold" the sequence name
> vVolSeqName := "SEQ_VOLUME_1";
>
> --access the sequence from the name in the variable
> select nextval(vVolSeqName) into vFileSeq;

If nothing else, you could use EXECUTE and build your select in a string. See
the manual for details of EXECUTE.

> If I cannot do this, can you suggest a solution to my original bottleneck
> issue. And also the problem of the sequencing number being rolled back in
> the event that the CreateFile function aborts or is rolled back.

A sequence number will never be rolled back. The sequence generator guarantees

> However, for me a bigger area of confusion is how to deal with concurrency
> issues in Postgres generally. Are there any good docs with examples of
> different scenarios?

Not as far as I know (if you would like to contribute some as you learn, I'm
sure it would be appreciated). The isolation levels are listed with the
corresponding standard SQL though, so it might be worth googling for a
general reference.

--
  Richard Huxton
  Archonet Ltd

You could do something with threads on the backend, invisible to your Java
middleware. I don't have enough experience to feel confident about trying to
evaluate the pros and cons of (possibly) different ways of doing this. But
given that you can write functions in C and load them into Postgres so that
they can be called from plpgsql, I think you could in essence add to
Postgres a function which when called would hand off the sequence load &
update on a separate thread/connection, wait for its commit and completion,
and return the value. Of course you still have to be careful about
concurrency issues with this approach, so that you don't wind up with the 2
threads deadlocked.

That may well strike you as a gross hack. I don't particularly like it
either, but I think it would get the job done without requiring any changes
to your current code base except for the rewrite of GetVolumeFileReference.

BTW, in reference to other suggestions: I believe that a sequence name is
indeed just a string, so you can build the name and pass it to sequence
functions on the fly; I know that sequences do not roll back, once a value
is issued it is "burned" regardless of whether the enclosing transaction
commits or not. So you should be able to have a trigger that on insert of a
WDVolume row creates a corresponding sequence, then use that sequence within
GetVolumeFileReference. Whether this is a good idea depends I'm sure on how
many WDVolumes you'll have. I feel confident that dozens or hundreds would
be no problem; thousands I don't have any idea; millions I wouldn't try.


--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice

Hi Scott

Firstly, your name rings a bell. I'm sure you're a 4D developer - I was
one too (once, but no more sadly)...

You imply that it should be possible to create an embedded C function that
can access the DB in its own thread, and therefore its own transaction. Is
there a document that would explain how this is done?

Also do you know if you can embedded java functions - I'm more comfortable
with java than C these days?

I think that for this problem, I will use SEQUENCE objects who name is
stored within the WDVolume table. It will allow me to avoid the row level
lock bottleneck, and my counters won't be rolled back on a transaction
rollback. There are only going to be less than 100 Volumes - so this
approach will be fine.

I'm amazed that nested transactions are still not yet supported. Postgres
is a mature product (from my understanding), and I can see loads of
messages asking about nested transaction support.

Thanks for your feedback.

John

Scott Ribe said:
> You could do something with threads on the backend, invisible to your Java
> middleware. I don't have enough experience to feel confident about trying
> to
> evaluate the pros and cons of (possibly) different ways of doing this. But
> given that you can write functions in C and load them into Postgres so
> that
> they can be called from plpgsql, I think you could in essence add to
> Postgres a function which when called would hand off the sequence load &
> update on a separate thread/connection, wait for its commit and
> completion,
> and return the value. Of course you still have to be careful about
> concurrency issues with this approach, so that you don't wind up with the
> 2
> threads deadlocked.
>
> That may well strike you as a gross hack. I don't particularly like it
> either, but I think it would get the job done without requiring any
> changes
> to your current code base except for the rewrite of
> GetVolumeFileReference.
>
> BTW, in reference to other suggestions: I believe that a sequence name is
> indeed just a string, so you can build the name and pass it to sequence
> functions on the fly; I know that sequences do not roll back, once a value
> is issued it is "burned" regardless of whether the enclosing transaction
> commits or not. So you should be able to have a trigger that on insert of
> a
> WDVolume row creates a corresponding sequence, then use that sequence
> within
> GetVolumeFileReference. Whether this is a good idea depends I'm sure on
> how
> many WDVolumes you'll have. I feel confident that dozens or hundreds would
> be no problem; thousands I don't have any idea; millions I wouldn't try.
>
>
> --
> Scott Ribe
> scott_ribe@killerbytes.com
> http://www.killerbytes.com/
> (303) 665-7007 voice
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>

On Thursday 04 December 2003 07:57, John Sidney-Woollett wrote:
>
> I'm amazed that nested transactions are still not yet supported. Postgres
> is a mature product (from my understanding), and I can see loads of
> messages asking about nested transaction support.

Loads of messages asking for many other features too ;-)
It will get done sooner or later - the more assistance people offer the core
the sooner it'll be.

--
  Richard Huxton
  Archonet Ltd

Point taken.

I'm also interested in replication - seeing lots of messages about that
too! :)

John

Richard Huxton said:

> Loads of messages asking for many other features too ;-)
> It will get done sooner or later - the more assistance people offer the
> core
> the sooner it'll be.
>
> --
>   Richard Huxton
>   Archonet Ltd
>

On Wed, 3 Dec 2003 08:08:49 -0000 (GMT), "John Sidney-Woollett"
<johnsw@wardbrook.com> wrote:
>Issue - nested transactions

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

AFAICS nested transactions - at least in the way we plan to implement
them - won't help, because subtransaction commit will not release locks.
We see a subtransaction as part of the main transaction.  If a
subtransaction commits but the main transaction aborts, the
subtransaction's effects are rolled back.

    START TRANSACTION;   -- main xact
    ...
    START TRANSACTION;   -- sub xact
    UPDATE t SET n=n+1 WHERE i=42;

This locks the row with i=42, because if another transaction wants to
update this row, it cannot know whether to start with the old or the new
value of n before our transaction commits or rolls back.

    COMMIT;              --sub xact

Here we are still in the main transaction.  Nothing has changed for
other backends, because they still don't know whether our main
transaction will succeed or fail.  So we have to keep the lock...

>Is there a simple/elegant solution to this problem?

Perhaps dblink?  Just a thought, I don't have any personal experience
with it.

Servus
 Manfred

It would be nice if nested transactions could be (optionally) decoupled
from their enclosing transaction.

John

Manfred Koizar said:
> On Wed, 3 Dec 2003 08:08:49 -0000 (GMT), "John Sidney-Woollett"
> <johnsw@wardbrook.com> wrote:
>>Issue - nested transactions
>
>>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.
>
> AFAICS nested transactions - at least in the way we plan to implement
> them - won't help, because subtransaction commit will not release locks.
> We see a subtransaction as part of the main transaction.  If a
> subtransaction commits but the main transaction aborts, the
> subtransaction's effects are rolled back.
>
>     START TRANSACTION;   -- main xact
>     ...
>     START TRANSACTION;   -- sub xact
>     UPDATE t SET n=n+1 WHERE i=42;
>
> This locks the row with i=42, because if another transaction wants to
> update this row, it cannot know whether to start with the old or the new
> value of n before our transaction commits or rolls back.
>
>     COMMIT;              --sub xact
>
> Here we are still in the main transaction.  Nothing has changed for
> other backends, because they still don't know whether our main
> transaction will succeed or fail.  So we have to keep the lock...
>
>>Is there a simple/elegant solution to this problem?
>
> Perhaps dblink?  Just a thought, I don't have any personal experience
> with it.
>
> Servus
>  Manfred
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

On Thu, 4 Dec 2003 17:56:33 -0000 (GMT), "John Sidney-Woollett"
<johnsw@wardbrook.com> wrote:
>It would be nice if nested transactions could be (optionally) decoupled
>from their enclosing transaction.

While I see your point how this could be useful for certain use cases,
unfortunately I don't have any idea how it could be implemented with
respect to MVCC.

Servus
 Manfred