Re: Transaction atomicity

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