Re: Transaction atomicity

Giuseppe

Couple of things.

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

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.

I strongly suggest you read and understand the section on MVCC in the
postgresql manual and sequences.

Dave
On 7-Mar-07, at 6:59 AM, Giuseppe Sacco wrote:

> Hi all,
> I have a question about a simple code that should automatically
> insert a
> record in a table. The primary key for this record is automatically
> generated during the INSERT statement, as the max value plus one.
>
> The (simplified, since the real number of fields is 5) code is:
>
> final String query = "INTO table (docId,seqNr) " +
>         "VALUES (?, (SELECT 1 + coalesce (max(seqNr), 0) "+
>         "FROM table " +
>         "WHERE seqNr BETWEEN 0 AND (9223372036854775807-1) ))";
> Connection conn=daof.getConnection();
> try {
>     conn.setAutoCommit(true);
>     PrepararedStatemen st = conn.prepareStatement(query);
>     st.setString(1,'myDocId');
>     st.executeUpdate();
>     conn.close();
> }
> catch (SQLException e) {
>     try { conn.close(); } catch (SQLException ee) {}
>     throw e;
> }
>
> I then create Runtime.getRuntime().availableProcessors() threads that
> create a DataSource and start executing that code in parallel. What
> happens is that I get many DUPLICATE KEY errors. I thought that in
> AutoCommit all insert are supposed to be serialized, so they should
> create a seqNr different since MAX(seqNr) is different at each call.
>
> Is my reasoning correct? Or is it a different problem, maybe with jdbc
> or postgresql?
>
> Thanks,
> Giuseppe
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>