Java and MySQL Transactions

Question

I have a java app that runs on multiple computers and they all connect to the same MySQL database. I need transactions to make sure database is updated correctly.

I have a problem with releasing locks. Here is the code:

public boolean bookSeats(int numeroSala, Calendar dataOra, List<Posto> posti) {
    JDBConnection connection = JDBConnection.getDbConnection();
    Connection conn = connection.conn;
    java.sql.Date javaSqlDate = new java.sql.Date(dataOra.getTime().getTime());
    java.sql.Time javaSqlTime = new java.sql.Time(dataOra.getTime().getTime());

    try {
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    } catch (SQLException e) {
        e.printStackTrace();
    }

    try
    {
        Posto p = null;
        ListIterator<Posto> itr = posti.listIterator();
        PreparedStatement stmt = null;
        stmt = conn.prepareStatement(
            "INSERT INTO sala?_tickets " + 
            "(giornoSpettacolo, orarioSpettacolo, fila, posto) " + 
            "VALUES (?, ?, ?, ?)");

        stmt.setInt(1, numeroSala);
        while(itr.hasNext())
        {
            p = itr.next();
            stmt.setString(2, javaSqlDate.toString()); // giornoSpettacolo
            stmt.setString(3, javaSqlTime.toString()); // orarioSpettacolo
            stmt.setInt(4, p.getFila()); // fila
            stmt.setInt(5, p.getNumero()); // posto
            stmt.addBatch();
        }

        stmt.executeBatch();

        conn.commit();
        return true;
    }
    catch (SQLException e) {
        e.printStackTrace();
    }

    return false;
}

This method works fine but when I try to execute the this on another computer at the same time, I get a DEADLOCK error even though the conn.commit() was completed.

As soon as I close the first application, the other application can run the method without Deadlock error. It seems like the COMMIT doesn't release the lock?

Answer 1

Per MySQL documentation, about SERIALIZABLE:

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled.

You might want to try re-enabled auto-commit after your commit, or try Connection. REPEATABLE_READ instead.

Answer 2

You can refer to this documentantion on how to do transactions with bare JDBC. Hope it helps.