Auto attached records get detached after commit

Question

I have want to fetch a list of Relation records, and I'll use .fetchInto(RELATION); and I want to iterate over the list and commit each iteration to the database. This doesn't seem to be working for me because I get Caused by: java.sql.SQLException: Connection is closed while updating the record. I don't have this problem with regular jOOQ queries. And it doesn't seem any connections are closed.

When I use contact.attach(jooq().configuration()) it seems to be working again. How can I prevent it from detaching?

I start and commit a transaction through JPA.em().getTransaction().*.

org.jooq.exception.DataAccessException: SQL [update `Relation` set `Relation`.`organizationName` = ? where `Relation`.`id` = ?]; Connection is closed
    at org.jooq_3.15.1.MYSQL.debug(Unknown Source)
    at org.jooq.impl.Tools.translate(Tools.java:2979)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:643)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:349)
    at org.jooq.impl.UpdatableRecordImpl.storeMergeOrUpdate0(UpdatableRecordImpl.java:331)
    at org.jooq.impl.UpdatableRecordImpl.storeUpdate0(UpdatableRecordImpl.java:228)
    at org.jooq.impl.UpdatableRecordImpl.lambda$storeUpdate$1(UpdatableRecordImpl.java:220)
    at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:143)
    at org.jooq.impl.UpdatableRecordImpl.storeUpdate(UpdatableRecordImpl.java:219)
    at org.jooq.impl.UpdatableRecordImpl.update(UpdatableRecordImpl.java:156)
    at org.jooq.impl.UpdatableRecordImpl.update(UpdatableRecordImpl.java:151)
    at worker.taskprocessors.BulkContactEditWorker.execute(BulkContactEditWorker.java:144)

Example:

var contacts = jooq()
        .selectFrom(
            RELATION
                .join(BULK_CONTACT_EDIT_CONTACTS)
                .on(
                    BULK_CONTACT_EDIT_CONTACTS
                        .CONTACT_ID
                        .eq(RELATION.ID)
                        .and(BULK_CONTACT_EDIT_CONTACTS.BULK_CONTACT_EDIT_ID.eq(bulkContactEditId))
                    .and(BULK_CONTACT_EDIT_CONTACTS.PROCESSED.isFalse())))
        .limit(batchSize)
        .fetchInto(RELATION);

if (!JPA.em().getTransaction().isActive()) {
  JPA.em().getTransaction().begin();
}

for (RelationRecord contact : contacts) {
  contact.attach(jooq().configuration()); // I have to add this line to make it work.
  contact.setOrganizationName("OrganizationName");
  contact.update();
  JPA.em().getTransaction().commit();
  if (!JPA.em().getTransaction().isActive()) {
    JPA.em().getTransaction().begin();
  }
}

Answer 1

What's the problem

You're fetching your jOOQ RelationRecord values outside of a JPA transactional context, so that fetching runs in its own transaction (this is independent of jOOQ). jOOQ will always try to acquire() and release() a connection for every query, and when that happens outside of a transactional context, then the connection will be effectively closed (e.g. returned to the connection pool). You should switch the order of starting a transaction and running the jOOQ query:

// start transaction
// run jOOQ query
// iterate jOOQ results
//   run updates
// commit transaction

If you're using declarative transactions (e.g. Spring's @Transactional annotation), then you're less likely to run into this problem as your initial jOOQ query will more likely also be in the same transaction already.

Why did attaching work

When you explicitly attach the RelationRecord to the configuration, then you will attach a record that has been attached to the previously closed connection to the new configuration with the transactional connection. I'm assuming that your jooq() method produces a new DSLContext instance, which wraps the currently active DataSource or Connection.

Switch to bulk updates

However, if your example is all there is to your actual logic (i.e. you haven't simplified it for this Stack Overflow question), then why not just run a bulk update? It will be simpler and much faster.

jooq()
    .update(RELATION)
    .set(RELATION.ORGANIZATION_NAME, "OrganizationName")
    .where(RELATION.ID.in(
        select(BULK_CONTACT_EDIT_CONTACTS.CONTACT_ID)
        .from(BULK_CONTACT_EDIT_CONTACTS)
        .where(BULK_CONTACT_EDIT_CONTACTS.BULK_CONTACT_EDIT_ID.eq(bulkContactEditId))
        .and(BULK_CONTACT_EDIT_CONTACTS.PROCESSED.isFalse())
    ))

    // Since you're using MySQL, you have native UPDATE .. LIMIT support
    .limit(batchSize)
    .execute();

I'm assuming your actual example is a bit more complex, because you need to set the PROCESSED flag to true somewhere, too, but it's always good to keep this option in mind.