Are JDBI batch operation atomic?

Question

Are JDBI batch operations (inserts or updates) atomic? Or do I need to wrap them in a transaction block?

score 3 · Accepted Answer · answered Nov 25 '14 at 20:48

3

According to java docs by default it's transactional. I don't know whether it fulfills the description of atomic but you don't need to wrap them in a transaction block. (FYI, there is @Transaction annotation you can use in case you want to do something transactional on DAO level, which is nicer than transaction blocks)

answered Nov 25 '14 at 20:48

Natan

2,816
20
37

The link to the java docs is broken by now; [here is one for the equivalent class in version 3](http://jdbi.org/apidocs/org/jdbi/v3/sqlobject/statement/SqlBatch.html). – GitProphet Apr 17 '18 at 19:46

Jan Bodnar · Answer 2 · 2016-06-22T12:03:01.730

My tests have shown that JDBI Batch operations are not atomic. In case we want all or nothing operation, we need to put the batch into a transaction.

I have created a simple test example. I used a MySQL database of InnoDB type.

package com.zetcode;

import org.skife.jdbi.v2.Batch;
import org.skife.jdbi.v2.DBI;
import org.skife.jdbi.v2.Handle;

public class JDBIEx6 {

    public static void main(String[] args) {

        DBI dbi = new DBI("jdbc:mysql://localhost:3306/testdb",
                "testuser", "test623");

        Handle handle = dbi.open();
        Batch batch = handle.createBatch();

        batch.add("DROP TABLE IF EXISTS Friends");
        batch.add("CREATE TABLE Friends(Id INT AUTO_INCREMENT PRIMARY KEY, Name TEXT)");
        batch.add("INSERT INTO Friends(Name) VALUES ('Monika')");
        batch.add("INSERT INTO Friends(Name) VALUES ('Tom')");
        batch.add("INSERT INTO Friends(Name) VALUES ('Jane')");
        batch.add("INSERT INTO Friends(Name) VALUES ('Robert')");

        batch.execute();
    }
}

If we deliberately modify one of the INSERT statements so that it fails, the table is created and all correct INSERT statements are saved.

The following is a Mavem POM file for the project.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.zetcode</groupId>
    <artifactId>JDBIEx6</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.jdbi</groupId>
            <artifactId>jdbi</artifactId>
            <version>2.73</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.39</version>
        </dependency>

    </dependencies>    
</project>

So I challange the fact that batch operations are atomic by default.

First of all, thank you very much for testing this! Secondly, do you (or anyone else) know a way of a way to wrap a batch in a transaction? — GitProphet, Apr 17 '18 at 19:52

score 0 · Answer 3 · answered Jul 06 '16 at 13:39

I think you should not put ddl statements in a batch against mysql, see http://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html.

There is an implicit commit after create table ... which pretty much defeats a MAY BE transaction around the batch.

Just sayin...

Are JDBI batch operation atomic?

3 Answers3