Thanks for everyone's inputs and here is an update on the issue:
The problem source is that autocommit is not getting unset.
The code does the following ( and source code or copyright does not
belong to Cisco):
. unsets autocommit
. starts transaction
. SQL for select for update....
. SQL for update next sequence number
. Commits transaction
The problem is in unsetting auto commit. Since this runs inside an Jboss
app server/EJB environment, this becomes a no-op and hence the ACIDity
across the select for update and update. We are using postgres 8.2.12 on
Windows with JDBC driver 8.2-506.
Thanks
Raji
-----Original Message-----
From: Greg Smith [mailto:gsmith@gregsmith.com]
Sent: Thursday, July 16, 2009 2:03 PM
To: Raji Sridar (raji)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Concurrency issue under very heay loads
On Wed, 15 Jul 2009, Raji Sridar (raji) wrote:
> When multiple clients are concurrently accessing this table and
> updating it, under extermely heavy loads in the system (stress
> testing), we find that the same order number is being generated for
multiple clients.
The only clean way to generate sequence numbers without needing to worry
about duplicates is using nextval:
http://www.postgresql.org/docs/current/static/functions-sequence.html
If you're trying to duplicate that logic in your own code, there's
probably a subtle race condition in your implementation that is causing
the bug.
If you had two calls to nextval from different clients get the same
value returned, that might be a PostgreSQL bug. Given how much that
code gets tested, the more likely case is that there's something to
tweak in your application instead. I would advise starting with the
presumption it's an issue in your app rather than on the server side of
things.
P.S. Posting the same question to two lists here is frowned upon;
pgsql-general is the right one for a question like this.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
