I am absolutely sure that the database transaction is always terminated by =
invoking commit or rollback, and waiting for the method to come back, befor=
e the middle tier returns control to the client.
=20
A couple other potentially relevant facts are that these connections are do=
ing all this work in the SERIALIZABLE transaction isolation mode, and that =
the updates are done through ResultSet objects from prepared statements whi=
ch SELECT * on the appropriate rows.
=20
I read through the documentation of the error message, and of the way Postg=
reSQL handles the isolation levels. This is behaving as though the time th=
e 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 bef=
ore posting.

What happens if the timestamp of the commit is an exact match for the times=
tamp of the next transaction start? What is the resolution of the time sam=
pling? It may be possible that we could submit several of these, on differ=
ent connections, within the space of a millisecond. Could that be a proble=
m? (It doesn't appear to be in my simple test cases.)
=20
I don't trust the clock on the Windows client, but I wouldn't think that ha=
s anything to do with the issue.
=20
-Kevin
=20
=20
of which connection will be used for any request. Each request is commite=
d as its own database transaction before the middle tier responds to the re=
quester, 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

Unfortunately, the original test environment has been blown away in favor o=
f testing the 8.1 beta release. I can confirm that the problem exists on a=
build of the 8.1 beta. If it would be helpful I could set it up again on =
8.0.3 to confirm. I THINK it was actually the tip of the 8.0 stable branch=
as opposed to the 8.0.3 release proper.
=20
We have a little more information about the failure pattern -- when we get =
these, it is always after there has been a rollback on the thread which eve=
ntually generates the serialization error. So I think the pattern is:
=20
ConnectionA:
- A series of insert/update/deletes (on tables OTHER than the progress t=
able).
- Update the progress table.
- Commit the transaction.
ConnectionB:
- A series of insert/update/deletes (on tables OTHER than the progress t=
able) fails.
- Rollback the transaction.
- Attempt each insert/update/delete individually. Commit or rollback e=
ach as we go.
- Attempt to update the progress table -- fail on serialization error.
=20
To avoid any ambiguity in my former posts -- introducing even a very small =
delay between the operations on ConnectionA and ConnectionB makes the seria=
lization error very infrequent; introducing a larger delay seems to make it=
go away. I hate to consider that as a solution, however.
=20
I'm afraid I'm not familiar with a good way to capture the stream of commun=
ications with the database server. If you could point me in the right dire=
ction, I'll give it my best shot.
=20
I did just have a thought, though -- is there any chance that the JDBC Conn=
ection.commit is returning once the command is written to the TCP buffer, a=
nd I'm getting hurt by some network latency issues -- the Nagle algorithm o=
r some such? (I assume that the driver is waiting for a response from the =
server before returning, so this shouldn't be the issue.) At the point tha=
t the commit confirmation is sent by the server, I assume the shared memory=
changes are visible to the other processes?
=20
-Kevin
=20
=20
It's not done via timestamps: rather, each transaction takes a census
of the transaction XIDs that are running in other backends when it
starts (there is an array in shared memory that lets it get this
information cheaply). Reliability of the system clock is not a factor.

Are you sure the server is 8.0.3? There was a bug in prior releases
that might possibly be related:

2005-05-07 17:22 tgl

* src/backend/utils/time/: tqual.c (REL7_3_STABLE), tqual.c
(REL7_4_STABLE), tqual.c (REL7_2_STABLE), tqual.c (REL8_0_STABLE),
tqual.c: Adjust time qual checking code so that we always check
TransactionIdIsInProgress before we check commit/abort status.=20
Formerly this was done in some paths but not all, with the result
that a transaction might be considered committed for some purposes
before it became committed for others. Per example found by Jan
Wieck.

My recollection though is that this only affected applications that were
using SELECT FOR UPDATE. In any case, it's pretty hard to see how this
would affect an application that is in fact waiting for the backend to
report commit-done before it launches the next transaction; the
race-condition window we were concerned about no longer exists by the
time the backend sends CommandComplete. So my suspicion remains fixed
on that point. Do you have any way of sniffing the network traffic of
the middle-tier to confirm that it's doing what it's supposed to?

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Kevin Grittner wrote:

tcpdump will do the trick (something like 'tcpdump -n -w
some.output.file -s 1514 -i any tcp port 5432')

Or you can pass '&loglevel=2' as part of the JDBC connection URL to have
the JDBC driver generate a log of all the messages it sends/receives (in
less detail than a full network-level capture would give you, though)

No, the JDBC driver waits for ReadyForQuery from the backend before
returning.

-O

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sorry to have left this hanging so long, but I was off sick all last week.
=20
We started with the loglevel=3D2 option in the driver. We ran for a few se=
conds and got about 620KB of output, including 15 of these. I'm starting t=
o pore over them, but I'm not sure what I'm loooking for. I've never read =
one of these puppies before. Any documentation out there to help? Any tip=
s? I assume you don't want a 620KB attachment to an email to this list.
=20
Thanks,
=20
-Kevin
=20
=20

rection, I'll give it my best shot.

tcpdump will do the trick (something like 'tcpdump -n -w
some.output.file -s 1514 -i any tcp port 5432')

Or you can pass '&loglevel=3D2' as part of the JDBC connection URL to have
the JDBC driver generate a log of all the messages it sends/receives (in
less detail than a full network-level capture would give you, though)

and I'm getting hurt by some network latency issues

No, the JDBC driver waits for ReadyForQuery from the backend before
returning.

-O

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Never mind -- mystery solved. The framework is at fault, PostgreSQL is fin=
e. The signal that the query is done can be returned to the client before =
the commit.
=20
We'll get that fixed.
=20
Thank you all for your patience.
=20
-Kevin
=20


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly