Java sql transactions. What am I doing wrong?

Question

I have written the small test with sole purpose to better understand transactions in jdbc. And though I did all according to the documentation, the test does not wish to work normally.

Here is table structure:

CREATE TABLE `default_values` (
   `id` INT UNSIGNED NOT auto_increment,
   `is_default` BOOL DEFAULT false,
   PRIMARY KEY(`id`)
);

Test contains 3 classes:

public class DefaultDeleter implements Runnable
{

    public synchronized void deleteDefault() throws SQLException
    {
        Connection conn = null;
        Statement deleteStmt = null;
        Statement selectStmt = null;
        PreparedStatement updateStmt = null;
        ResultSet selectSet = null;

        try
        {
            conn = DriverManager.getConnection("jdbc:mysql://localhost/xtest", "root", "");
            conn.setAutoCommit(false);
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

            // Deleting current default entry
            deleteStmt = conn.createStatement();
            deleteStmt.executeUpdate("DELETE FROM `default_values` WHERE `is_default` = true");

            // Selecting first non default entry
            selectStmt = conn.createStatement();
            selectSet = selectStmt.executeQuery("SELECT `id` FROM `default_values` ORDER BY `id` LIMIT 1");

            if (selectSet.next())
            {
                int id = selectSet.getInt("id");

                // Updating found entry to set it default
                updateStmt = conn.prepareStatement("UPDATE `default_values` SET `is_default` = true WHERE `id` = ?");
                updateStmt.setInt(1, id);
                if (updateStmt.executeUpdate() == 0)
                {
                    System.err.println("Failed to set new default value.");
                    System.exit(-1);
                }
            }
            else
            {
                System.err.println("Ooops! I've deleted them all.");
                System.exit(-1);
            }

            conn.commit();
            conn.setAutoCommit(true);
        }
        catch (SQLException e)
        {
            try { conn.rollback(); } catch (SQLException ex)
            {
                ex.printStackTrace();
            }

            throw e;
        }
        finally
        {
            try { selectSet.close(); } catch (Exception e) {}
            try { deleteStmt.close(); } catch (Exception e) {}
            try { selectStmt.close(); } catch (Exception e) {}
            try { updateStmt.close(); } catch (Exception e) {}
            try { conn.close(); } catch (Exception e) {}
        }
    }

    public void run()
    {
        while (true)
        {
            try
            {
                deleteDefault();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
                System.exit(-1);
            }

            try
            {
                Thread.sleep(20);
            }
            catch (InterruptedException e) {}
        }
    }

}

public class DefaultReader implements Runnable
{

    public synchronized void readDefault() throws SQLException
    {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;

        try
        {
            conn = DriverManager.getConnection("jdbc:mysql://localhost/xtest", "root", "");

            conn.setAutoCommit(false);
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

            stmt = conn.createStatement();
            rset = stmt.executeQuery("SELECT * FROM `default_values` WHERE `is_default` = true");

            int count = 0;
            while (rset.next()) { count++; }

            if (count == 0)
            {
                System.err.println("Default entry not found. Fail.");
                System.exit(-1);
            }
            else if (count > 1)
            {
                System.err.println("Count is " + count + "! Wtf?!");
            }

            conn.commit();
            conn.setAutoCommit(true);
        }
        catch (SQLException e)
        {
            try { conn.rollback(); } catch (Exception ex)
            {
                ex.printStackTrace();
            }

            throw e;
        }
        finally
        {
            try { rset.close(); } catch (Exception e) {}
            try { stmt.close(); } catch (Exception e) {}
            try { conn.close(); } catch (Exception e) {}
        }
    }

    public void run()
    {
        while (true)
        {
            try
            {
                readDefault();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
                System.exit(-1);
            }

            try
            {
                Thread.sleep(20);
            }
            catch (InterruptedException e) {}
        }
    }

}

public class Main
{

    public static void main(String[] args)
    {
        try
        {
            Driver driver = (Driver) Class.forName("com.mysql.jdbc.Driver")
                    .newInstance();
            DriverManager.registerDriver(driver);

            Connection conn = null;
            try
            {
                conn = DriverManager.getConnection("jdbc:mysql://localhost/xtest", "root", "");
                System.out.println("Is transaction isolation supported by driver? " +
                        (conn.getMetaData()
                        .supportsTransactionIsolationLevel(
                        Connection.TRANSACTION_SERIALIZABLE) ? "yes" : "no"));
            }
            finally
            {
                try { conn.close(); } catch (Exception e) {}
            }

            (new Thread(new DefaultReader())).start();
            (new Thread(new DefaultDeleter())).start();

            System.in.read();
            System.exit(0);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }

}

I have written script, which fills table with 100k records (where one of them is default) for each run. But every time I run this test, output is:

Is transaction isolation supported by driver? yes

Default entry not found. Fail.

What's wrong with this code?

Answer 1

If you allow the container to manage transactions, you can do something like:

@Resource
private UserTransaction utx;

and then just use it in your code:

utx.begin();

// atomic operation in here

utx.commit();

Then you don't need to worry about the intricacies of transaction management.

Edit: @Gris: Yes, you're correct on that. I had assumed you were developing a web app. as pjp said, spring is a nice alternative in this case. Or -- depending on the size and complexity of the application -- you could get by with managing your own transactions.

Answer 2

I suggest that you add some breakpoints and step through each of your database operations to check that they are doing what you expect. You can open a session onto your database server and set the transaction isolation level so that you can read uncommitted data.

Also check that the use of 'true' is valid in MySql over the numeric value 1 for the boolean type.

Answer 3

The answer is simple: You create two threads. They run completely independent of each other. Since you don't synchronize them in any way, there is no way to tell which one gets to the database first. If the reader is the first one, the deleter won't have begun, yet, and there won't be an item with is_default == true since the deleter didn't get that far, yet.

Next, you have completely isolated the two transactions (Connection.TRANSACTION_SERIALIZABLE). This means that even if the deleter has a chance to update the database, the reader will only see it after it has closed its connection and opened a new one.

And if that wasn't the case, the deleter is slower than the reader, so the chances that a record would have be updated with is_default == true at the time when the reader looks for it, are slim.

[EDIT] Now you say that there should be a single item with is_default == true when the tests starts. Please add a test to make sure that this is really the case before you start the two threads. Otherwise, you might be hunting the wrong bug.

Answer 4

Please make sure you're creating InnoDB tables, MyISAM (the default) doesn't support transactions. You can change your db create to this:

CREATE TABLE `default_values` (
   `id` INT UNSIGNED NOT auto_increment,
   `is_default` BOOL DEFAULT false,
   PRIMARY KEY(`id`)
) Engine=InnoDB;

another example: http://stackoverflow.com/questions/292706/mysql-transaction-with-accounting-application

Answer 5

There's a couple of points worth mentioning:

1. Does your script to populate the database before the test really work? Try doing a select count(*) ... on the table from inside Java code to check (this might sound dumb, but I've made this mistake before).

2. Don't do System.exit() all over the place as it will make the code hard to test - it might be interesting to see what the deleter does even though it appears you have no default==true record.