Thread: Strange crashes in JDBC code in multithreaded application.

Strange crashes in JDBC code in multithreaded application.

From
"L. Rahyen"
Date:
    I'm not on the list so please don't forget to CC me.

    First, what I'm doing, briefly. I have multithreaded proxy-server (it is
based on few years ago abandoned project). It wants to store information on
every page load/refresh to the database. And this works in almost all
cases... Almost. It is obvious that this is not acceptable because I have
broken database as a result; it should work in all cases of course to be
useful in the real world.
    Now, the details. Here is my functions to connect to the database and
initialize a statement:

private static Connection conn = null;
private static Statement stat = null;

final private void connect_to_psql()
{
    try
    {
        if(mgr.loglevel>3) System.err.println("Connecting to PostgreSQL...");
        // Step 1: Load the JDBC driver.
        Class.forName("org.postgresql.Driver");
        // Step 2: Establish the connection to the database.
        String url = "jdbc:" + mgr.psql_url;
        conn = DriverManager.getConnection(url, mgr.psql_user, mgr.psql_password);
    }
    catch (Exception e)
    {
        System.err.println("Got an exception when connecting to PostgreSQL database
erver! ");
        System.err.println(e.getMessage() );
        e.printStackTrace();
        System.exit(8);
    }
}

final private void initialize_statement()
{
    try
    {
        stat = conn.createStatement();
    }
    catch (SQLException e)
    {
        System.out.println("We got an exception while creating a PostgreSQL
statement: that probably means we're no longer connected. ");
        System.err.println(e.getMessage() );
        e.printStackTrace();
        System.exit(9);
    }
}

...
    if(conn == null)
        connect_to_psql();
    if(stat == null)
        initialize_statement();
...

    conn and stat assigments - that's all is really important here. I call these
functions only once for all threads. Everything works great in almost
all cases but sometimes (see below what "sometimes" exactly means here) I get
this crash when I call stat.executeUpdate(query):

Exception in thread "Thread-518" java.lang.NullPointerException
   at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:179)
   at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:437)
   at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:339)
   at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:285)
...

    This crash is "hard": it crashes whole thread (but not the main program of
course) even if I have related catch block. So I never have a chance to
reconnect to the database from the thread to "fix" the problem. It doesn't
matter at all what query I use. I even can use exactly the same query every
time (constant string) and still have JDBC to crashes *sometimes*.
    Now, what "sometimes" means? Sometimes I get above error every 1-3 calls of
executeUpdate(), sometimes once every 200-400 calls (this is most common
case). In other words this is non-uniform, at least at first impression. From
the JDBC user point of view it doesn't matter what query I use or how big is
uptime of my proxy or database connection. This is just happens and I cannot
prevent it.
    If I change "private static Statement stat = null;" to "private Statement
stat = null;" and get in this way indepedant  stat variable for each thread
nothing changes. I still get java.lang.NullPointerException sometimes and it
is almost the same as one above (please note that crash above is with a
little older JDBC release and crash below is with newest JDBC release, as you
can see nothing changes - just minor difference in line numbers):

Exception in thread "Thread-108" java.lang.NullPointerException
   at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:182)
   at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
   at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
   at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:283)
   at cacheobject.load_object(cacheobject.java:680)
   at cacheobject.make_request(cacheobject.java:176)
   at mgr.process_request(mgr.java:1488)
   at httpreq.run(httpreq.java:282)
   at java.lang.Thread.run(libgcj.so.70)

    Now, I see only two possibilities:

    1) This is a bug in JDBC. Is JDBC thread-safe? Is it supposed to be
thread-safe by design? Obviously if not then initializing conn
every time will be inefficient and I need to write a separate server to
execute PSQL queries because I want to connect only once to the DB.
    2) I'm doing multithreading with JDBC in a wrong way. Do I need anything
special? Can I use same conn in all threads?

    Feel free to ask if you additional question. I think that above examples show
the whole problem because that's only JDBC code in my proxy currently.
    Sorry if there is something obvious I have missed - this is a first time I
write multithreading code in Java.

Re: Strange crashes in JDBC code in multithreaded application.

From
Kris Jurka
Date:

On Sun, 5 Aug 2007, L. Rahyen wrote:

> Exception in thread "Thread-108" java.lang.NullPointerException
>   at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:182)
>   at ...
>   at java.lang.Thread.run(libgcj.so.70)
>

This area of code reads:

175        if (parameters == null)
176            parameters = SimpleQuery.NO_PARAMETERS;
177
178        boolean describeOnly = (QUERY_DESCRIBE_ONLY & flags) != 0;
179
180        // Check parameters are all set..
181        if (!describeOnly)
182            ((V3ParameterList)parameters).checkAllParametersSet();

So it's complaining that parameters is null, but the code on line 175
should take care of that case.  I note that you're using GCJ, does this
problem occur when using a Sun JVM?

>     1) This is a bug in JDBC. Is JDBC thread-safe? Is it supposed to be
> thread-safe by design? Obviously if not then initializing conn
> every time will be inefficient and I need to write a separate server to
> execute PSQL queries because I want to connect only once to the DB.

JDBC is thread safe.  While PG's driver can only execute one query at a
time, it should block, not die like this.

Kris Jurka

Re: Strange crashes in JDBC code in multithreaded application.

From
"L. Rahyen"
Date:
On Sunday August 5 2007 02:57, Kris Jurka wrote:
> On Sun, 5 Aug 2007, L. Rahyen wrote:
> > Exception in thread "Thread-108" java.lang.NullPointerException
> >   at
> > org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:1
> >82) at ...
> >   at java.lang.Thread.run(libgcj.so.70)
>
> This area of code reads:
>
> 175        if (parameters == null)
> 176            parameters = SimpleQuery.NO_PARAMETERS;
> 177
> 178        boolean describeOnly = (QUERY_DESCRIBE_ONLY & flags) != 0;
> 179
> 180        // Check parameters are all set..
> 181        if (!describeOnly)
> 182            ((V3ParameterList)parameters).checkAllParametersSet();
>
> So it's complaining that parameters is null, but the code on line 175
> should take care of that case.  I note that you're using GCJ, does this
> problem occur when using a Sun JVM?

    I have tried Sun JRE and after some time of testing I still don't see this
problem. So, bug was in GCJ. Sorry for the noise and thanks for your help!