How to create a database deadlock using jdbc and JUNIT

Question

I am trying to create a database deadlock and I am using JUnit. I have two concurrent tests running which are both updating the same row in a table over and over again in a loop.

My idea is that you update say row A in Table A and then row B in Table B over and over again in one test. Then at the same time you update row B table B and then row A Table A over and over again. From my understanding this should eventually result in a deadlock.

Here is the code For the first test.

public static void testEditCC()
{
    try{
        int rows = 0;
        int counter = 0;
        int large=10000000;
        Connection c=DataBase.getConnection();
        while(counter<large)
        {
            int pid = 87855;
            int cCode = 655;
            String newCountry="Egypt";              
            int bpl = 0;
            stmt = c.createStatement();

            rows = stmt.executeUpdate("UPDATE main " +              //create lock on main table
                                                  "SET BPL="+cCode+
                                                  "WHERE ID="+pid);
            rows = stmt.executeUpdate("UPDATE BPL SET DESCRIPTION='SomeWhere' WHERE ID=602"); //create lock on bpl table
            counter++;
        }

        assertTrue(rows == 1);
        //rows = stmt.executeUpdate("Insert into BPL (ID, DESCRIPTION) VALUES ("+cCode+", '"+newCountry+"')");

    }
    catch(SQLException ex)
    {
        ex.printStackTrace();
        //ex.getMessage();
    }
}

And here is the code for the second test.

public static void testEditCC()
{
    try{
        int rows = 0;
        int counter = 0;
        int large=10000000;
        Connection c=DataBase.getConnection();
        while(counter<large)
        {
            int pid = 87855;
            int cCode = 655;
            String newCountry="Jordan";         
            int bpl = 0;
            stmt = c.createStatement();
            //stmt.close();
            rows = stmt.executeUpdate("UPDATE BPL SET DESCRIPTION='SomeWhere' WHERE ID=602"); //create lock on bpl table
            rows = stmt.executeUpdate("UPDATE main " +          //create lock on main table
                                                  "SET BPL="+cCode+
                                                  "WHERE ID="+pid);
            counter++;
        }

        assertTrue(rows == 1);
        //rows = stmt.executeUpdate("Insert into BPL (ID, DESCRIPTION) VALUES ("+cCode+", '"+newCountry+"')");

    }
    catch(SQLException ex)
    {
        ex.printStackTrace();
    }
}

I am running these two separate JUnit tests at the same time and am connecting to an apache Derby database that I am running in network mode within Eclipse. Can anyone help me figure out why a deadlock is not occurring? Perhaps I am using JUnit wrong.

Answer 1

You should check the transaction isolation level, as it determines whether or not the DB locks rows touched by a transaction. If the isolation level is too low, no locking occurs, so no deadlock either.

Update: according to this page, the default tx isolation level for Derby is read committed, which should be OK. The page is worth reading btw, as it explains tx isolation and its different levels, and what problems it solves.

Next question then: what is DataBase in your code? This seems to be a nonstandard way to get a connection.

Update2: I think I got it. Quote from the API doc:

Note: By default a Connection object is in auto-commit mode, which means that it automatically commits changes after executing each statement. If auto-commit mode has been disabled, the method commit must be called explicitly in order to commit changes; otherwise, database changes will not be saved.

In other words, rows are not locked because your effective transactions last only for the lifetime of individual updates. You should switch off autocommit before starting to work with your connection:

Connection c=DataBase.getConnection();
c.setAutoCommit(false);