Re: Serialization errors on single threaded request

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> 08/26/05 11:10 AM >>>
"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> 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