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());
stmt.setString(3, javaSqlTime.toString());
stmt.setInt(4, p.getFila());
stmt.setInt(5, p.getNumero());
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?
source
share