I have an odd one here. I was unable to find it with a search of the mailing lists. I've spent a few hours trying to create a simple test case, but so far these simple cases aren't showing the problem. I want to make sure this isn't a know problem before investing more time trying to come up with a test case suffiently complex to expose the problem.
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.
If we add a 1 ms delay on the client side between requests to the middle tier, the frequency of these errors drops by about two orders of magnitude. With a 100 ms delay, we haven't seen any.
The pattern of activity which causes the problem involves a single database transaction with inserts and updates to many tables, including one with a potentially large blob, followed by an update to a numeric column in a row which tracks progress. The serialization errors are happening on this final update. My simple test cases use a single thread on two JDBC connection emulating just this final update, and the problem does not show up.
We have the same behavior on 8.0.3 and the develpment snapshot from yesterday. (I haven't gotten a test run from today's beta release yet -- I need to coordinate the test with someone else who's not here right now. I'll follow up if the beta release changes this behavior.)
The server is SuSE 9.3 with dual xeons and xfs on a SAN. The client and middle tier for these tests have been on Windows XP. The requests are going through JDBC.
Does this behavior sound familiar to anyone?
pgsql-bugs by date
|Next:||From: Tom Lane||Date: 2005-08-26 16:10:54|
|Subject: Re: Serialization errors on single threaded request stream |
|Previous:||From: Bernard Henry Voynet||Date: 2005-08-26 09:13:07|
|Subject: BUG #1849: Is PgOleDb still alive ?|