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);
}
}
}
On Mon, May 18, 2015 at 7:38 PM, Lukas Eder <[email protected]> wrote:
> 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
>
> 2015-05-18 9:15 GMT+02:00 Alok Menghrajani <[email protected]>:
>>
>> 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());
>> }
>>
>> }
>>
>> --
>> 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 [email protected].
>> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> 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 [email protected].
> For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
