Re: Connection.setsavepoint() / releaseSavepoint() is not thread-safe

I don't think this is a JDBC issue. Rather a limitation of the postgresql
server.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 4 July 2014 05:05, Christian Schlichtherle <christian(at)schlichtherle(dot)de>
wrote:

> 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(at)schlichtherle(dot)de>:
>
> Hi everyone,
>
> I have already posted this bug as #10847 to pqsql-bugs(at)postgresql(dot)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 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: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
> <http://docs.oracle.com/javadb/10.5.1.1/devguide/cdevconcepts89498.html> that
> this is indeed „fair use“ of a Connection. Thus, it would be superb if
> PostgreSQL could support it.
>
> Regards,
> Christian Schlichtherle
>
>
>