我有一个在多台计算机上运行的Java应用程序,它们都连接到同一个MySQL数据库.我需要进行交易以确保正确更新数据库.
我有释放锁的问题.这是代码:
public boolean bookSeats(int numeroSala, Calendar dataOra, List 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 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;
}
该方法可以正常工作,但是当我尝试同时在另一台计算机上执行此方法时,即使conn.commit()已完成,也会出现DEADLOCK错误.
一旦关闭第一个应用程序,其他应用程序即可运行该方法而不会出现死锁错误.好像COMMIT没有释放锁?
解决方法:
根据MySQL文档,关于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.
您可能想要在提交后尝试重新启用自动提交,或尝试连接.改为REPEATABLE_READ.
标签:jdbc,transactions,java,mysql
来源: https://codeday.me/bug/20191201/2080103.html