Re: Problem with autocommit

Eric Herman <[email protected]> Wed, 11 Oct 2006 16:27:56 +0200

Newsgroups	gmane.comp.db.mysql.java
Message-ID	<1160576877.7448.204.camel@localhost>

On Thu, 2006-10-05 at 14:52 +0200, MiÂłosz HulbĂłj wrote:
>  Hello,
> I am observing some non-deterministic behaviour of the autocommit. 

Thanks for the report, I was able to confirm this race condition with
the attached code.

junit.framework.AssertionFailedError: Loops: 1000 correct: 624 zeros:
376 others: 0 expected:<1000> but was:<624>

 -Eric

-- 
Eric Herman, Software Developer
MySQL AB, www.mysql.com
VoIP Ext: 6594   US: +1 408 213 6540  Europe: +46 18 174 400
Mobile: +31 [0]6-20719662
Are you MySQL certified?  www.mysql.com/certification


-- 
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:    http://lists.mysql.com/[email protected]

FooTest.java (text/x-java, 3.6 KB)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import junit.framework.TestCase;

public class FooTest extends TestCase {

    private Connection connection = null;

    private Statement statement = null;

    private ResultSet rs = null;

    public void testFoo() throws Exception {
        int LOOPS = 1000;
        int numRowsToCreate = 1;
        int missedRow = 0;
        int correctRetuned = 0;
        int otherReturned = 0;

        for (int i = 0; i < LOOPS; i++) {
            int numRowsActual = createTablesAndData(numRowsToCreate);

            int found = selectCount();

            if (found == numRowsActual) {
                correctRetuned++;
            } else if (found == 0) {
                missedRow++;
            } else {
                otherReturned++;
                System.err.println(found + "!=" + numRowsActual);
            }
        }

        String message = "Loops: " + LOOPS + " correct: " + correctRetuned
                + " zeros: " + missedRow + " others: " + otherReturned;

        assertEquals(message, LOOPS, correctRetuned);
    }

    protected void setUp() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
    }

    protected void tearDown() throws SQLException {
        try {
            close();
        } finally {
            try {
                dropTables();
            } finally {
                close();
            }
        }
    }

    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql:///test", "", "");
    }

    private int selectCount() throws SQLException {
        connection = getConnection();
        statement = connection.createStatement();
        rs = statement.executeQuery("select count(*) from ccc");
        rs.next();
        int found = rs.getInt(1);
        close();
        return found;
    }

    private int createTablesAndData(int numRowsToCreate) throws SQLException {
        dropTables();
        connection = getConnection();
        statement = connection.createStatement();
        statement.execute("CREATE TABLE ccc(a INT(11), b INT(11))");
        statement.execute("CREATE TABLE cccc(a INT(11), b INT(11))");
        for (int j = 0; j < numRowsToCreate; j++) {
            int a = j + 1;
            int b = j + 2;
            String sql = "INSERT INTO cccc VALUES(" + a + "," + b + ")";
            statement.executeUpdate(sql);
        }

        int numRowsActual = statement
                .executeUpdate("INSERT INTO ccc SELECT * FROM cccc");
        close();
        assertEquals(numRowsToCreate, numRowsActual);
        return numRowsActual;
    }

    private void dropTables() throws SQLException {
        try {
            connection = getConnection();
            statement = connection.createStatement();
            statement.execute("DROP TABLE IF EXISTS ccc");
            statement.execute("DROP TABLE IF EXISTS cccc");
        } finally {
            close();
        }
    }

    private void close() throws SQLException {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            statement = null;
        }
        if (connection != null) {
            connection.close();
            connection = null;
        }
    }
}