Using Transaction with JDBI / IDBI / Dropwizard -- rollback problems

Question

I'm having a lot of trouble getting transactions to work with IDBI. We're using the dropwizard framework and simple inserts, updates, selects, and deletes have worked find but now we cannot seem to get the transactions to work correctly. Here is what I'm trying

public class JDb {
    private JustinTest2 jTest2 = null;
    private Handle dbHandle = null;

    public JDb(final IDBI idbi) {
        try {
            dbHandle = idbi.open();
            dbHandle.getConnection().setAutoCommit(false);
            jTest2 = dbHandle.attach(JustinTest2.class);
        } catch( SQLException e ) {

        }
    }

   public void writeJustin(final int styleId, final int eventId) {
        dbHandle.begin();
        int num = jTest2.findByStyleId(styleId);

        try {
            jTest2.doStuff(styleId, eventId);
            dbHandle.commit();
        } catch(Exception e) {
            dbHandle.rollback(); // Never rolls back here, always get the inserted row!
        }

        num = jTest2.findByStyleId(styleId); 
   } 
}

And here is my JustinTest2 class

public abstract class JustinTest2 {

    @SqlUpdate("INSERT INTO jTest2 (styleId, jNum) VALUES (:styleId, :jNum)")
    public abstract void insert(@Bind("styleId") int styleId, @Bind("jNum") int jNum);

    @SqlQuery("SELECT count(styleId) " +
            "FROM jTest2 " +
            "WHERE styleId=:styleId")
    public abstract int findByStyleId(@Bind("styleId") int styleId);


    public int doStuff(int styleId, int eventId) throws Exception{
        int count = findByStyleId(styleId);

        insert(styleId, eventId);

        count = findByStyleId(styleId);

        if(count==1) {
            throw new Exception("Roll back");
        }

        return count;
    }
}

I've also tried implementing writeJustin like :

public void writeJustin(final int styleId, final int eventId) throws Exception {
    int rows_updated = jTest2.inTransaction(new Transaction<Integer, JustinTest2>() {
        @Override
        public Integer inTransaction(JustinTest2 transactional, TransactionStatus status) throws Exception {

            jTest2.insert(styleId, eventId);
            int num = transactional.findByStyleId(styleId);

            try {
                if(num == 1) throw new Exception("BOOM");    
            } catch (Exception e) {
                transactional.rollback();
                throw e;
            }

            num = transactional.findByStyleId(styleId);
            return num;
        }
    });
}

I cannot seem to get the transaction to rollback, in each of these ways the inserted row is always there after the rollback, whether I try directly through the handle or whether I use inTransaction (which from my understanding should not commit the transaction if an exception is thrown within the call back) Anyone have any idea what I might be doing wrong?

Answer 1

I figured this out. It turns out the table I was testing was using MyISAM and not InnoDB as the storage engine. MyISAM does not support transactions. I rebuilt the table using InnoDB and the code above worked fine.

For anyone who doesn't know you can see which engine a table is using by using:

show create table <tablename>;

Should see something like:

CREATE TABLE `grades` (
    `id` int(11) NOT NULL,
    `percent` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1