Java and MySQL Transactions
java, jdbc, mysql, transactions
Solution
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.
Problem
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?