On 03/11/2012 21:06, Katherine Marsden wrote:
This has the feel of two threads using the same Connection and one commits or rolls back without coordination with the other. Typically each thread should have it's own connection. Do you have a separate connection for each thread and if so, what is the isolation level?
No, each thread has its own connection. The situation is that tests can be run synchronously from a servlet, so a request comes in, a DB connection is created, the connection is used to do "select * from tests where id=?", and the results go back immediately in the response and the DB connection is then closed. Alternatively, tests can be scheduled to run asynchronously, and there is a thread created at system startup with its own connection which loops waiting for notifications that a test has completed and then issues exactly the same query.
In the synchronous case it always works; in the asynchronous case it always fails. I've tried creating a new connection each time round the loop in the asynchronous case, but it makes no difference. Also if it were a thread contention issue I'd expect to see some timing-dependent behaviour, not total consistency.
The rest of the row can always be read (ints and varchars and suchlike), but the clob always fails. I've tried reading the clob first; same thing. The isolation level is default, but in any case it happens even when nothing else is going on; I start an asynchronous test on my development rig with no other users, and then sit back and wait. It consistently hits the breakpoint I've set on the exception handler. This also points to it not being a thread contention problem.
One final thing: my "fix" (where I read the rest of the row in one query and the clob in another) uses the same connection for both queries.
So I'm totally baffled. Happy to have found a workaround but very annoyed that I don't understand why it works.
Thanks for your help, -- John English
