JOOQ: Batch execute multiple transaction-CodePudding

I have to insert same record in 2 different tables in an atomic way. I am using JOOQ transaction.


        dslContext.transactionResultAsync { configuration ->
            val context = configuration.dsl()
            context
                .insertInto(table1)
                .set(record)
                .onDuplicateKeyUpdate()
                .set(record)
                .execute()

            context.insertInto(table2)
                .set(record)
                .onDuplicateKeyUpdate()
                .set(record)
                .execute()

However I have several 100 thousands of such records. So instead of executing 100K queries I want to execute 1 big batch query. JOOQ supports batching on the query as

   val batch = dslContext.batch(
            dslContext.insertInto(table2)
                .set(dummyRecord)
                .onDuplicateKeyUpdate()
                .set(dummyRecord)
   )

   records.forEach{batch.bind(it)}
   batch.execute()
}

Now I can't seem to locate a way to batch multiple transactions.

Update

I found following

dslContext.batchedResult { config ->
  config.transactionResultAsync { configuration ->
            val context = configuration.dsl()
            context
                .insertInto(table1)
                .set(record)
                .onDuplicateKeyUpdate()
                .set(record)
                .execute()

            context.insertInto(table2)
                .set(record)
                .onDuplicateKeyUpdate()
                .set(record)
                .execute()
   }
}

But I think it is not the same. Batched connection simply batches the SQL statements generically and doesn't actually use prepared statements for inserts. Any other suggestions?

CodePudding user response：

There's nothing special about batching compared to individual queries. From a transaction perspective, these two are equivalent:

// No batching:
dslContext.transactionResultAsync { configuration ->
    val context = configuration.dsl()
    context.insertInto(...).execute();
    context.insertInto(...).execute();
}

// Batching
dslContext.transactionResultAsync { configuration ->
    val context = configuration.dsl()
    context.batch(...).execute();
    context.batch(...).execute();
}

CodePudding user response：

I found following

dslContext.batchedResult { config ->
  config.transactionResultAsync { configuration ->
            val context = configuration.dsl()
            context
                .insertInto(table1)
                .set(record)
                .onDuplicateKeyUpdate()
                .set(record)
                .execute()

            context.insertInto(table2)
                .set(record)
                .onDuplicateKeyUpdate()
                .set(record)
                .execute()
   }
}

But I think it is not the same. Batched connection simply batches the SQL statements generically and doesn't actually use prepared statements for inserts. Any other suggestions?