I am absolutely sure that the database transaction is always terminated by
invoking commit or rollback, and waiting for the method to come back, before
the middle tier returns control to the client.
A couple other potentially relevant facts are that these connections are doing
all this work in the SERIALIZABLE transaction isolation mode, and that the
updates are done through ResultSet objects from prepared statements which
SELECT * on the appropriate rows.
I read through the documentation of the error message, and of the way
PostgreSQL handles the isolation levels. This is behaving as though the time
the PostgreSQL server assigns to the commit is sometimes later than the time of
the subsequent transaction start, so I totally understand why you would ask the
question you did. It is also why I checked this very carefully before posting.
What happens if the timestamp of the commit is an exact match for the timestamp
of the next transaction start? What is the resolution of the time sampling?
It may be possible that we could submit several of these, on different
connections, within the space of a millisecond. Could that be a problem? (It
doesn't appear to be in my simple test cases.)
I don't trust the clock on the Windows client, but I wouldn't think that has
anything to do with the issue.
-Kevin
>>> Tom Lane <[EMAIL PROTECTED]> 08/26/05 11:10 AM >>>
"Kevin Grittner" <[EMAIL PROTECTED]> writes:
> The problem is this: a single thread is submitting database updates through
> a middle tier which has a pool of connections. There are no guarantees of
> which connection will be used for any request. Each request is commited as
> its own database transaction before the middle tier responds to the
> requester, which then immediately submits the next request. Nothing else it
> hitting the database. We are getting serialization errors.
Hm. Are you sure your middle tier is actually waiting for the commit
to come back before it claims the transaction is done?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
