mysq + readonly connections + transactions

[Alok Menghrajani]

Hi all,

I have an open source service and I want the ability to easily swap
the backend database. For various reasons, the service has two
connections: a read/write connection and a read-only connection (one
of the reasons being that it makes some forms of replication easier
and allows the service to continue running in degraded mode using a
read-only slave if the read/write database goes down).

We however noticed that transactions on the read-only connection work
fine with Postgres but fail with MySql (see [1] for a short repro).

The error message is:
Exception in thread "main" org.jooq.exception.DataAccessException:
Cannot set savepoint
[...]
Caused by: java.sql.SQLException: Connection is read-only. Queries
leading to data modification are not allowed.

I'm hoping there is some way to change the way jooq or mysql handles
transactions and which would work with a read-only connection? Any
other suggestions?

Thanks,
Alok


[1]:

package com.squareup.JooqTest;

import java.sql.Connection;
import java.sql.DriverManager;
import org.jooq.DSLContext;
import org.jooq.impl.DSL;

import static test.generated.tables.Author.*;

/**
* create database library;
* use library;
* create table author (id int not null, first_name varchar(255),
last_name varchar(255), primary key (id));
*/
public class App {

public static void main(String[] args) throws Exception {
App app = new App();
app.run();
}

public void run() throws Exception {
String userName = ...;
String password = ...;
String url = "jdbc:mysql://localhost:3306/library";

try (Connection conn = DriverManager.getConnection(url, userName,
password)) {
conn.setReadOnly(true);
DSLContext jooqContext = DSL.using(conn);
doTransaction(jooqContext);
}
}

void doTransaction(DSLContext jooqContext) {
jooqContext.transaction(configuration ->
jooqContext.selectFrom(AUTHOR).fetchOne());
}

}

[Lukas Eder]

Hi Alok,

Interesting, I wasn't aware of this behaviour. So, in general, transactions are OK (e.g. to get consistent read behaviour), but savepoints don't work as they're not really useful when you're never writing...

All of this behaviour is implemented in DefaultTransactionProvider. We have a pending feature request that allows for disabling the use of JDBC Savepoint when nesting transactions:

https://github.com/jOOQ/jOOQ/issues/3955

This feature request will be useful for you as well.

In the meantime, you could implement your own TransactionProvider that accompanies the read-only connection, to replace the out-of-the-box implementation

Let me know if this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Alok Menghrajani]

Thanks for your response, very helpful.

I'm experimenting with [1]. I'm still figuring out which transaction
level I need to set and making sure that my TransactionProvider
implements the desired behavior.

A few things I figured might be worth sharing:

1. I'm using a counter and plan to only commit the top-level
transaction. I'm keeping the counter in ctx.configuration().data()
(which is very similar to DefaultTransactionProvider's behavior,
except that has a Stack). It looks like the
DefaultTransactionProvider's savepoints() method is not thread safe.
Do TransactionProviders need to care about concurrent calls to
begin()? I'm guessing it's unlikely that a transaction gets shared
between different threads, but I'm no expert.

2. I don't think DefaultTransactionProvider needs to have a Connection
instance variable. The brace() method could just use the connection
already stored in the map?

3. What is the purpose of the data() map in TransactionContext?

4. I was initially not creating a new jooq context inside the
transaction (DSL.using(configuration)). I have the feeling the API
would be less error prone if TransactionalRunnable and
TransactionalCallable took a DSLContext instead of a configuration
(the configuration object would be available via the
context.configuration method if needed).

Alok

[1]:

class MyTransactionProvider implements TransactionProvider {
private final ConnectionProvider provider;

public MyTransactionProvider(ConnectionProvider provider) {
this.provider = provider;
}

@Override
public final void begin(TransactionContext ctx) {
Map<Object, Object> data = ctx.configuration().data();
int counter = (int)data.compute("counter", (k, v) -> (v == null) ?
1 : (int) v + 1);
if (counter > 1) {
// We are in a sub-transaction, don't do anything.
return;
}
try {
Connection connection = provider.acquire();
if (data.get("connection") != null) {
throw new DataAccessException("begin failed: connection wasn't null");
}
if (connection.getAutoCommit() != true) {
// If auto-commit was false, how do we know what is the
beginning of a transaction?
throw new DataAccessException("begin failed: was expecting
autocommit to be true");
}
data.put("connection", connection);
connection.setAutoCommit(false);
} catch (SQLException e) {
throw new DataAccessException("begin failed", e);
}
}

@Override
public final void commit(TransactionContext ctx) {
Map<Object, Object> data = ctx.configuration().data();
int counter = (int)data.compute("counter", (k, v) -> (v == null) ?
1 : (int) v - 1);
if (counter > 0) {
// We only commit the top-level transaction
return;
}
try {
Connection connection = (Connection)data.remove("connection");
connection.commit();
connection.setAutoCommit(true);
provider.release(connection);
} catch (SQLException e) {
throw new DataAccessException("commit failed", e);
}
}

@Override
public final void rollback(TransactionContext ctx) {
// Rollbacks can happen in any sub-transaction.
try {
Map<Object, Object> data = ctx.configuration().data();
Connection connection = (Connection)data.remove("connection");
connection.rollback();
connection.setAutoCommit(true);
// reset the counter. Do we care about concurrency?
data.put("counter", 0);
provider.release(connection);
} catch (SQLException e) {
throw new DataAccessException("rollback failed", e);

[Lukas Eder]

Hi Alok

2015-05-19 12:35 GMT+02:00 Alok Menghrajani <al...@squareup.com>:

Thanks for your response, very helpful.

I'm experimenting with [1]. I'm still figuring out which transaction
level I need to set and making sure that my TransactionProvider
implements the desired behavior.

A few things I figured might be worth sharing:

1. I'm using a counter and plan to only commit the top-level
transaction. I'm keeping the counter in ctx.configuration().data()
(which is very similar to DefaultTransactionProvider's behavior,
except that has a Stack).

That sounds exactly like what we intend to implement in https://github.com/jOOQ/jOOQ/issues/3955

 

It looks like the
DefaultTransactionProvider's savepoints() method is not thread safe.
Do TransactionProviders need to care about concurrent calls to
begin()? I'm guessing it's unlikely that a transaction gets shared
between different threads, but I'm no expert.

All the transaction models I'm aware of tightly couple transactions to single threads. In fact, a thread is usually "blocked" by a transaction and returns to the thread pool no earlier than at the end of a transaction, possibly later. This model works well with classic Servlet models, where an HTTP request also "blocks" a thread until the request is finished.

"Reactive programming" (as understood by Typesafe et al.) tries to minimise thread contention by sharing threads among requests. In these models, transactions can jump between threads, and the "action" model will need to take care that no transaction parts leak the action, and thus the thread.

Chances are, that within the bounds of a transaction, you do not have to be thread safe.

Note though, that DefaultTransactionProvider.savepoints() is "thread safe" as loosely specified by TransactionProvider (http://www.jooq.org/javadoc/latest/org/jooq/TransactionProvider.html): 

A new Configuration copy is created from the calling DSLContext for the scope of a single transactions. Implementors may freely add custom data to Configuration.data(), in order to share information between begin(TransactionContext) and commit(TransactionContext) orrollback(TransactionContext), as well as to share information with nested transactions.

 

I suspect that the thread safety discussion deserves more attention in the Javadocs. I have registered an issue to improve the Javadocs:

https://github.com/jOOQ/jOOQ/issues/4289

2. I don't think DefaultTransactionProvider needs to have a Connection
instance variable. The brace() method could just use the connection
already stored in the map?

You're probably right, I'll investigate. This will be tracked as

https://github.com/jOOQ/jOOQ/issues/4290

 

3. What is the purpose of the data() map in TransactionContext?

To store data that should be available between begin() and commit() / rollback(). All org.jooq.Scope implementations have such a Map that can be used to keep user data around for the respective lifetime of the Scope

 

4. I was initially not creating a new jooq context inside the
transaction (DSL.using(configuration)). I have the feeling the API
would be less error prone if TransactionalRunnable and
TransactionalCallable took a DSLContext instead of a configuration
(the configuration object would be available via the
context.configuration method if needed).

The DSLContext type is not really an expressive type on its own. Having that as a function argument may seem convenient, but it doesn't really make sense.

I see your point, and I believe that the main criticism by Witold Szczerba in various past discussions on this user group (e.g. https://groups.google.com/d/msg/jooq-user/-2laC-pYsbI/qz23wRhe5C4J) stemmed from this very fact. It would actually be convenient if the functional argument types would have no arguments at all, as implemented by Witold's ULTM library, which puts the context in a ThreadLocal (I believe).

I believe that the current API will show its value in the long run, as it

1) makes no assumptions about the transaction model

2) stays completely generic via using Configuration arguments

I hope this makes things a bit clearer?