0

In MySQL, executeBatch seems not like transaction.

Does it improve performance like pipeline in Redis?

UPDATE:

Are there any performance differences between these 2 usages?

a. executeUpdate

try (PreparedStatement ps = conn.prepareStatement("INSERT INTO test (key_, value_) VALUES (?, ?)") {
    ps.setString(1, "key1");
    ps.setString(2, "value1");
    ps.executeUpdate();
    ps.setString(1, "key2");
    ps.setString(2, "value2");
    ps.executeUpdate();
}

b. addBatch/executeBatch

try (PreparedStatement ps = conn.prepareStatement("INSERT INTO test (key_, value_) VALUES (?, ?)")) {
    ps.setString(1, "key1");
    ps.setString(2, "value1");
    ps.addBatch();
    ps.setString(1, "key2");
    ps.setString(2, "value2");
    ps.addBatch();
    ps.executeBatch();
}
auntyellow
  • 2,423
  • 2
  • 20
  • 47
  • Your question makes no sense. Please be more specific in you question (and when you use `executeBatch` in JDBC, make sure to **disable** auto-commit). – Mark Rotteveel Dec 31 '14 at 11:52
  • @MarkRotteveel, not necessary to **disable** auto-commit, see sample codes I added into the question. – auntyellow Dec 31 '14 at 13:47
  • It is recommended by the JDBC specification, as behavior of `executeBatch` with auto-commit is implementation-specific (and with MySQL probably even depends on whether or not you have batch rewriting enabled). – Mark Rotteveel Dec 31 '14 at 13:48
  • BTW: With the additional code, it looks like your question has nothing to do with transactions. You might also want to explain what you mean with "pipeline". – Mark Rotteveel Dec 31 '14 at 13:50
  • Seems really nothing to do with transactions. Btw, "pipeline" is a network technique used in Redis or HTTP communication, see http://redis.io/topics/pipelining and http://en.wikipedia.org/wiki/HTTP_pipelining . I do not know whether this can be used in DB communication. – auntyellow Dec 31 '14 at 14:05
  • 1
    MySQL has the connection property `rewriteBatchedStatements` which will pack multiple batched statements into a single execute call, but I am not exactly sure what it does when this is disabled (the default). – Mark Rotteveel Dec 31 '14 at 14:09
  • 2
    @MarkRotteveel See my answer [here](http://stackoverflow.com/a/26313288/2144390). – Gord Thompson Dec 31 '14 at 14:15
  • @GordThompson, with rewriteBatchedStatements, executeBatch may merge 2 insertions into one "transaction". Am I right? – auntyellow Dec 31 '14 at 14:54
  • 1
    I'd suggest you use "request" or "operation" instead of the word "transaction" which has a very specific meaning in database systems. – Mark Rotteveel Dec 31 '14 at 15:57
  • Thanks to @MarkRotteveel. So I mean, with rewriteBatchedStatements, executeBatch may merge 2 insertions into one **atomic operation**. – auntyellow Jan 01 '15 at 16:42

0 Answers0