Java and MySQL operations

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

I have a problem with issuing 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 it on another computer at the same time, I get a DEADLOCK error even if it conn.commit()was completed.

As soon as I close the first application, another application can start the method without a Deadlock error. Doesn't COMMIT seem to release the lock?

+3
source share
4 answers

In the MySQL documentation about SERIALIZABLE:

REPEATABLE READ, InnoDB SELECT SELECT... LOCK IN SHARE MODE, autocommit .

. REPEATABLE_READ.

+3

, JDBC. , .

0

conn.close() finally, , .

0

, , . , , "" , , .

Alternatively, just take a look at the following link that discusses deadlock handling in JDBC

Java deadlock definition

0
source

All Articles