Thread: Couple of preparedstatement bug suspects

Hi All

I have an app which inserts rows to a table called messages. During load testing, I'm looping to insert 10,000 rows.
Intermittentently (around 5% and only after the first 6,000 or so) I'm getting the following Exception...

org.postgresql.util.PSQLException: This statement has been closed.
    at org.postgresql.jdbc2.AbstractJdbc2Statement.checkClosed(AbstractJdbc2Statement.java:2442)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.getUpdateCount(AbstractJdbc2Statement.java:495)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:309)
    at com.primetext.tl2000.dataobjects.Messages.insert(Messages.java:566)


This smells like a bug. There are other updates within my loop, and I can't promise that I'm closing all statements. Therefore if this bug is being triggered by me abusing the driver, please confirm and I'll go through all of my code ensuring that all preparedstatements are being closed.

However, there is a another aspect of this which smells like an even bigger bug...
Even though the PreparedStatement.executeUpdate is throwing an exception, the row *is* being written to the database!!
This can't possibly be valid.

best regards
Roy

On Fri, 9 May 2008, Roy Smith wrote:

> I have an app which inserts rows to a table called messages. During load
> testing, I'm looping to insert 10,000 rows. Intermittentently (around 5%
> and only after the first 6,000 or so) I'm getting the following
> Exception...
>
> org.postgresql.util.PSQLException: This statement has been closed.
>    at
> org.postgresql.jdbc2.AbstractJdbc2Statement.checkClosed(AbstractJdbc2Statement.java:2442)
>    at
> org.postgresql.jdbc2.AbstractJdbc2Statement.getUpdateCount(AbstractJdbc2Statement.java:495)
>    at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:309)
>    at com.primetext.tl2000.dataobjects.Messages.insert(Messages.java:566)
>
> This smells like a bug. There are other updates within my loop, and I
> can't promise that I'm closing all statements. Therefore if this bug is
> being triggered by me abusing the driver, please confirm and I'll go
> through all of my code ensuring that all preparedstatements are being
> closed.
>

Is your app multi-threaded?  The only way I see for this to happen is that
one thread is executing the statement and another is closing it.

> However, there is a another aspect of this which smells like an even
> bigger bug... Even though the PreparedStatement.executeUpdate is
> throwing an exception, the row *is* being written to the database!! This
> can't possibly be valid.
>

The exception is from the driver, not the server and it happens after the
query has been executed by the server.  Once the server has committed the
data, there's not much the driver can do about that.

Kris Jurka

Hi Kris

Thanks for the quick response.

No the app is not multithreaded. It's a web app and I'm the only user on my local test PC. So I start tomcat, run a single post which generates the error, stop tomcat.

I didn't understand your 2nd point. When I call PreparedStatement.executeUpdate() I have to know that the row did or did not get written. If there is a situation that an exception can be thrown after the data has been written, that exception needs to be unambiguous in telling the caller that the data is written or not.

I'm happy to provide any debug/logs which help you with this one, just let me know.

best
Roy

On Fri, 9 May 2008, Roy Smith wrote:

> No the app is not multithreaded. It's a web app and I'm the only user on my
> local test PC. So I start tomcat, run a single post which generates the
> error, stop tomcat.

Then I don't understand how it got closed mid-execution, perhaps some sort
of intermediate pooling/proxying mechanism is involved?  If you can
compose a self-contained test case, I'd be happy to look at it.

> I didn't understand your 2nd point. When I call
> PreparedStatement.executeUpdate() I have to know that the row did or did
> not get written. If there is a situation that an exception can be thrown
> after the data has been written, that exception needs to be unambiguous
> in telling the caller that the data is written or not.
>

To get this control you shouldn't be using autocommit.  You should turn
that off and then rollback if you get a SQLException.  With autocommit,
the cats already out of the bag.

Kris Jurka

Roy Smith wrote:
>     To get this control you shouldn't be using autocommit.  You should
>     turn that off and then rollback if you get a SQLException.  With
>     autocommit, the cats already out of the bag.
>
>
> Hmmm, not sure I agree but I'm happy to be persuaded. You're saying that
> a single JDBC call is *not* atomic in and of itself? As a computer
> scientist I find that disturbing if it's true. It basically means that
> running any JDBC session in autocommit mode is bogus, since individual
> update statements become non-deterministic.

Well, in general autocommit isn't safe and neither is non-autocommit.
If you get a network disconnect at just the wrong time (during commit),
you can't tell what happened to your transaction.  You'll get a
SQLException informing you your connection is gone, but did it commit or
not?  You need two phase commit (XA) to be able to name and lookup in
doubt transactions at a later date in case something bad happens.  In
your case something odd is going on (which we may still be able to
resolve), but is it worth moving every single application to XA to
defend against these possibilities?  I don't think so.

> I do appreciate that where an executeUpdate is carrying out more than
> one database write there isn't much you can do about it.

Even in the single write case, there is some code in the JDBC driver and
it must have some failure modes right?  What about an out of memory
condition when trying to retrieve the server's response?  The server has
committed it and the driver can't interpret that result.

> Nested transactions would be an answer, but of course there is no
> such thing.
>

There are savepoints, but again I don't see how that's helpful in
autocommit mode.

On Fri, 9 May 2008, Kris Jurka wrote:

> On Fri, 9 May 2008, Roy Smith wrote:
>
>> No the app is not multithreaded. It's a web app and I'm the only user on my
>> local test PC. So I start tomcat, run a single post which generates the
>> error, stop tomcat.
>
> Then I don't understand how it got closed mid-execution, perhaps some sort of
> intermediate pooling/proxying mechanism is involved?  If you can compose a
> self-contained test case, I'd be happy to look at it.

Any progress on creating a test case?

Kris Jurka