Skip site navigation (1) Skip section navigation (2)

Transaction atomicity

From: Giuseppe Sacco <giuseppe(at)eppesuigoccas(dot)homedns(dot)org>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Transaction atomicity
Date: 2007-03-07 11:59:12
Message-ID: 1173268752.20645.29.camel@scarafaggio (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2007-03-07 12:15:07
Subject: Re: Transaction atomicity
Previous:From: mikael-aronssonDate: 2007-03-07 11:23:40
Subject: LISTEN question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group