Re: Transaction atomicity

From: Giuseppe Sacco <giuseppe(at)eppesuigoccas(dot)homedns(dot)org>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Transaction atomicity
Date: 2007-03-07 15:08:39
Message-ID: 1173280119.20645.39.camel@scarafaggio
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hi Dave,

Il giorno mer, 07/03/2007 alle 07.15 -0500, Dave Cramer ha scritto:
> 1) What you are observing is quite normal for postgresql. It use MVCC
> so it's quite possible for every connection to see the same value for
> max (seNR).
> which leads us to 2

Thank you very much. I just read the documentation about MVCC and I
understand why my code didn't work.

> 2) Don't use max() for this. If you have to use max then you have to
> lock the record before doing the insert which will slow everyone
> down. Postgresql provides you with sequences for exactly this
> purpose. Use nextval('sequence_name') to increment it and currval
> ('sequence_name') to get the value that your connection just used.

Thanks for this suggestion. I have been evaluating the use of sequences
in my application, but I was looking for a more portable code (this code
have to run on postgresq 8.1, mysql 5, oracle 9.2 and sql-server 2005).
As you may know postgresql nextval syntax isn't as required by the
standard; and other vendors use different syntax too. Moreover it would
make my code really complex since I would need a new sequence for any
connected user.

So, before starting using sequences, I will try to loop my original
INSERT statement in order to see how long is the average looping.
Probably if this is less than 4 iterations, then I will go this way.

Again, thank you for your help,
Giuseppe

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Albe Laurenz 2007-03-07 15:11:49 Re: Transaction atomicity
Previous Message Andres Olarte 2007-03-07 14:36:32 Re: LISTEN question