JOOQ: Batch execute multiple transaction

user_1699956 0
Viewed 164

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?

jooq kotlin
2 Answers

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();
}
user_5217990

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?

user_16999560
Related