Re: Connection.setsavepoint() / releaseSavepoint() is not thread-safe - Mailing list pgsql-jdbc

I have refined the test as follows:

package cpssd.db;

import org.junit.Test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Savepoint;

import java.util.concurrent.CountDownLatch;

/**

 * See issue #251 at

 * https://www.assembla.com/spaces/cirruspoint/tickets/251

 * and the corresponding PostgreSQL issue #10847.

 *

 * @author Christian Schlichtherle

 */

public class Ticket251IT {

    private static final String CONNECTION_STRING = "jdbc:postgresql:postgres";

    private static final int NUM_THREADS = 8;

    @Test public void foo() throws SQLException, InterruptedException {

        try (Connection c = DriverManager.getConnection(CONNECTION_STRING)) {

            c.setAutoCommit(false);

            final Runnable task = new Runnable() {

                final CountDownLatch startSignal = new CountDownLatch(NUM_THREADS);

                @Override public void run() {

                    try {

                        // TODO: Once the bug in PostgreSQL has been fixed, the

                        // synchronized (c) statement can get removed.

                        Savepoint sp;

                        synchronized (c) {

                            sp = c.setSavepoint();

                        }

                        try {

                            // Insert transaction script here...

                            startSignal.countDown();

                            startSignal.await();

                        } finally {

                            synchronized (c) {

                                c.releaseSavepoint(sp);

                            }

                        }

                    } catch (SQLException | InterruptedException e) {

                        e.printStackTrace();

                    }

                }

            };

            final Thread[] threads = new Thread[NUM_THREADS];

            for (int i = 0; i < threads.length; i++)

                (threads[i] = new Thread(task)).start();

            for (Thread thread : threads)

                thread.join();

        }

    }

}

As you can see, I am now synchronizing all access on the connection. However, when running this test, every now and then it produces output like the following:

org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Connection.execSQLUpdate(AbstractJdbc2Connection.java:376)
at org.postgresql.jdbc3.AbstractJdbc3Connection.releaseSavepoint(AbstractJdbc3Connection.java:192)

at cpssd.db.Ticket251IT$1.run(Ticket251IT.java:43)
at java.lang.Thread.run(Thread.java:745)
org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Connection.execSQLUpdate(AbstractJdbc2Connection.java:376)
at org.postgresql.jdbc3.AbstractJdbc3Connection.releaseSavepoint(AbstractJdbc3Connection.java:192)

at cpssd.db.Ticket251IT$1.run(Ticket251IT.java:43)
at java.lang.Thread.run(Thread.java:745)
org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Connection.execSQLUpdate(AbstractJdbc2Connection.java:376)
at org.postgresql.jdbc3.AbstractJdbc3Connection.releaseSavepoint(AbstractJdbc3Connection.java:192)

at cpssd.db.Ticket251IT$1.run(Ticket251IT.java:43)
at java.lang.Thread.run(Thread.java:745)

org.postgresql.util.PSQLException: ERROR: no such savepoint
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Connection.execSQLUpdate(AbstractJdbc2Connection.java:376)
at org.postgresql.jdbc3.AbstractJdbc3Connection.releaseSavepoint(AbstractJdbc3Connection.java:192)

at cpssd.db.Ticket251IT$1.run(Ticket251IT.java:43)
at java.lang.Thread.run(Thread.java:745)

Regards,

Christian Schlichtherle

Am 04.07.2014 um 10:10 schrieb Christian Schlichtherle <christian@schlichtherle.de>:

Hi everyone,

I have already posted this bug as #10847 to pqsql-bugs@postgresql.org, but was redirected here:

The following test code…

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.concurrent.CountDownLatch;

/** @author Christian Schlichtherle */
public class Ticket251IT {
   private static final String CONNECTION_STRING = "jdbc:postgresql:postgres";   private static final int NUM_THREADS = 2;
   @Test public void foo() throws SQLException, InterruptedException {       try (Connection c = DriverManager.getConnection(CONNECTION_STRING)) {           c.setAutoCommit(false);           final Runnable task = new Runnable() {               final CountDownLatch startSignal = new CountDownLatch(NUM_THREADS);
               @Override public void run() {                   try {                       startSignal.countDown();                       startSignal.await();                       // FIXME: This idiom doesn't work on a shared connection!                       Savepoint sp = c.setSavepoint();                       try {                           // Insert transaction script here...                       } finally {                           c.releaseSavepoint(sp);                       }                   } catch (SQLException | InterruptedException e) {                       e.printStackTrace();                   }               }           };           final Thread[] threads = new Thread[NUM_THREADS];           for (int i = 0; i < threads.length; i++)               (threads[i] = new Thread(task)).start();           for (Thread thread : threads)               thread.join();       },   }
}

…frequently produces the following output…

org.postgresql.util.PSQLException: ERROR: no such savepointat org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)at org.postgresql.jdbc2.AbstractJdbc2Connection.execSQLUpdate(AbstractJdbc2Connection.java:376)at org.postgresql.jdbc3.AbstractJdbc3Connection.releaseSavepoint(AbstractJdbc3Connection.java:192)at cpssd.db.Ticket251IT$1.run(Ticket251IT.java:32)at java.lang.Thread.run(Thread.java:745)

The obvious workaround is to put all calls on the connection into a synchronized (whatever) { … } block. However, although the workaround works fine when applied to this isolated test case, it fails in my production code. I have yet to find out why that is. Nevertheless, this seems to be a bug in the JDBC driver.

The real use case behind this simplified test case is to distribute work to multiple threads. A parent thread creates the connection and starts a transaction, then spawns a number of child threads. Each child thread creates a save point, does its work and releases or rolls back the save point again. If all child threads succeed, the parent thread then commits the entire transaction. I figure from this document that this is indeed „fair use“ of a Connection. Thus, it would be superb if PostgreSQL could support it.

Regards,

Christian Schlichtherle