Thread: postgres jdbc transaction problem surfaces at times

i read in the posts here that postgres 8.0 jdbc had unmasked it;s
dislike of changing transaction level mid transaction so i changed my
code to the following

is this set up correct?

        try {
            livedb.setAutoCommit(false);
            copydb.setAutoCommit(false);


livedb.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

            copydb.setTransactionIsolation
(Connection.TRANSACTION_SERIALIZABLE);

            // series of writes to both db's

            livedb.commit();
            livedb.setAutoCommit(true);
            copydb.commit();
            copydb.setAutoCommit(true);

        } catch (SQLException sqle){
                // run a rollback-Release and log it routine;

                // crash and burn;
        }

this seems to work fine single user, but with several users on i get
occasional dumps with
transaction change in transaction.

the only code to change relates to the LUW start since the last rollout
of this servlet and there was never this problem before.
the connections are stored as a session attribute, so persist across
requests.

This smells like a possible threading issue, where multiple threads are
accessing the same connection object.  Remember that the same HTTP
session can be accessed by multiple threads concurrently, and the
PostgreSQL JDBC driver is not thread-safe.

To see if this is the case, try adding some additional synchronization
to guarantee that no two threads will use the connection at the same
time (perhaps by synchronizing on the Connection object).

-- Mark

On Wed, 2006-04-12 at 08:41 -0700, kevin@mtel.co.uk wrote:
> i read in the posts here that postgres 8.0 jdbc had unmasked it;s
> dislike of changing transaction level mid transaction so i changed my
> code to the following
>
> is this set up correct?
>
>         try {
>             livedb.setAutoCommit(false);
>             copydb.setAutoCommit(false);
>
>
> livedb.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
>
>             copydb.setTransactionIsolation
> (Connection.TRANSACTION_SERIALIZABLE);
>
>             // series of writes to both db's
>
>             livedb.commit();
>             livedb.setAutoCommit(true);
>             copydb.commit();
>             copydb.setAutoCommit(true);
>
>         } catch (SQLException sqle){
>                 // run a rollback-Release and log it routine;
>
>                 // crash and burn;
>         }
>
> this seems to work fine single user, but with several users on i get
> occasional dumps with
> transaction change in transaction.
>
> the only code to change relates to the LUW start since the last rollout
> of this servlet and there was never this problem before.
> the connections are stored as a session attribute, so persist across
> requests.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

thanks mark.

i added tight synchronize(livedb) around the update try - catch
statements and did bench tests with two users hammering the servlet and
no errors. I'll re-deploy this fix and see if live code holds up.

-kev