How to use transaction correctly

[jcm1...@gmail.com]

Hey guys,

I'm having a really hard time getting transactions to work. For some reason I cant seem to get the rollback to work and I'm hoping someone could help me out. I've tried this in two different ways: 

1) Implementing an abstract class to do some queries and maintain the Handle object myself (calling open/begin/commit/rollback manually, which from what I've read isn't the preferred way) 

2) Creating an interface and extending Transactional then implementing inTransaction in a private class.

Here is some quick sample code:

/** In my main class */

public void run(TestServiceConfiguration config, Environment environment) throws Exception {

        environment.addResource(new UpTimeResource("Test consumer"));

        

        final DBIFactory dbiFactory = new DBIFactory();

        final DBI testDbi = dbiFactory.build(environment, config.getMagentaDb(),"magentadb");

        final TestDb test = new TestDb(testDbi); 

        test.tryTransaction(1234);

    }

public class TestDb {

    private Handle dbHandle = null;

    private TestJdbiClass testClass = null;

    

    public TestDb(IDBI db) {

        try {

            dbHandle = db.open();

            testClass = dbHandle.attach(TestJdbiClass.class);

            dbHandle.getConnection().setAutoCommit(false);

        } catch(SQLException e) {                                   

            System.err.println(e.getMessage());

        }

    }

    

    public void tryTransaction(int styleId) {

        System.out.println("Looking for entries for styleId " + styleId);

        int count = testClass.findByStyleId(styleId);

        System.out.println("Found " + count + " BEFORE TRANSACTION");

        try {

            dbHandle.begin();

            testClass.doStuff(styleId);

            dbHandle.commit();

        } catch(Exception e) {

             System.out.println("Caught error " + e.getMessage() + "  --- Time to rollback");

            dbHandle.rollback();

        }

        count = testClass.findByStyleId(styleId);

        System.out.println("Found " + count + " AFTER TRANSACTION");

    }

}

public abstract class TestJdbiClass {

    

    @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) throws Exception{

        System.out.println("***** IN TRANSACTION *******");

        

        int count = findByStyleId(styleId);

        System.out.println("BEFORE: " + count + " for styleId " + styleId);

        insert(styleId, 1);

        System.out.println("INSERTED for styleId=" + styleId);

        count = findByStyleId(styleId);

        System.out.println("AFTER: " + count + " for styleId=" + styleId);

        if(count==1) {

            throw new Exception("Roll back  ");

        }

        return count;

    }

}

Output:

Looking for entries for styleId 1234

Found 0 BEFORE TRANSACTION

***** IN TRANSACTION *******

BEFORE: 0 for styleId 1234

INSERTED for styleId=1234

AFTER: 1 for styleId=1234

Caught error java.lang.Exception: Roll back  --- Time to rollback

Found 1 AFTER TRANSACTION

I'm catching the exception thrown within the method thats called right after I start the transaction and calling rollback but the row is still inserted!

Here is my code for what I tried with the interface:

public class TestDb {

    private Handle dbHandle = null;

    private TestJdbiInterface testInterface = null;

    

    public TestDb(IDBI db) {

        try {

            dbHandle = db.

            testInterface = dbHandle.attach(TestJdbiInterface.class);

            dbHandle.getConnection().setAutoCommit(false);

        } catch(SQLException e) {                                   

            System.err.println(e.getMessage());

        }

    }

public void tryTransaction2(final int styleId) {

        int count = testInterface.findByStyleId(styleId);

        System.out.println("Last found " + count + " styleIds matching" + styleId + " BEFORE Transaction");

        try {

            int rows_created = testInterface.inTransaction(new Transaction<Integer, TestJdbiInterface>() {

                @Override

                public Integer inTransaction(TestJdbiInterface transactional, TransactionStatus status) throws Exception {

                    System.out.println("**** IN TRANSACTION ******");

    

                    int num = transactional.findByStyleId(styleId);

                    System.out.println("FOUND " + num + " BEFORE");

    

                    transactional.insert(styleId);

                    System.out.println("TRANSACTION Inserted styleId");

    

                    num = transactional.findByStyleId(styleId);

                    System.out.println("TRANSACTION FOUND " + num + " AFTER ");

    

                    if(num == 1) {

                        throw new Exception("Roll back");

                    }

                    

                    return num;

                }

            });

        } catch(Exception e) {

            System.err.println("caught exception " + e.getMessage());

        }

        count = testInterface.findByStyleId(styleId);

        System.out.println("AFTER TRANSACTION: found " + count + " for styleId= " + styleId);

    }

}

package com.justin.jdbi;

import org.skife.jdbi.v2.sqlobject.Bind;

import org.skife.jdbi.v2.sqlobject.SqlQuery;

import org.skife.jdbi.v2.sqlobject.SqlUpdate;

import org.skife.jdbi.v2.sqlobject.mixins.Transactional;

public interface TestJdbiInterface extends Transactional<TestJdbiInterface> {

    @SqlUpdate("INSERT INTO jTest2 (styleId, jNum) VALUES (:styleId, 1)")

    public abstract void insert(@Bind("styleId") int styleId);

    @SqlQuery("SELECT count(styleId) FROM jTest2 WHERE styleId=:styleId")

    public abstract int findByStyleId(@Bind("styleId") int styleId);

}

Output:

Last found 0 styleIds matching1234 BEFORE Transaction

**** IN TRANSACTION ******

FOUND 0 BEFORE

TRANSACTION Inserted styleId

TRANSACTION FOUND 1 AFTER 

caught exception Transaction failed do to exception being thrown from within the callback. See cause for the original exception.

AFTER TRANSACTION: found 1 for styleId= 1234

Again, it caught the exception and everything except the row is still inserted! 

I'm really stuck at this point and I think I may be missing a key piece to the puzzle here. If anyone could point me in the right direction or maybe share some sample code that illustrates what I'm trying to do it would be really awesome. If my explanation wasn't clear enought, let me know and I can attach the actual source. I should also add I'm using this inside of the dropwizard framework.

Thanks in advance guys!

[jcm1...@gmail.com]

Alright, I figured this out. The code actually works fine. My problem was that I was testing on a MyISAM table. I rebuilt the table using InnoDB and the code worked fine.