您的代码基本上是正确的.发生死锁时引发的异常是SQLException.异常的getSQLState()方法提供返回错误代码,该代码提供有关实际错误的
additional information.
您还应该在尝试之间等待很短的时间,以免过多地加载服务器.
正如您巧妙地猜到的那样,设置最大尝试次数,或者您可能最终处于无限循环中.
最终的代码可能如下所示:
boolean oops;
int retries = 5;
Connection c = null;
Statement s = null;
ResultSet rs = null;
do
{
oops = false;
c = null;
s = null;
rs = null;
try
{
c = openConnection();
s = c.createStatement();
rs = s.executeQuery("SELECT stuff FROM mytable");
fiddleWith(rs);
}
catch (SQLException sqlex)
{
oops = true;
switch(sqlex.getErrorCode()())
{
case MysqlErrorNumbers.ER_LOCK_DEADLOCK:
// deadlock or lock-wait time-out occured
break;
...
}
Thread.sleep(1000); // short delay before retry
}
finally
{
if (rs != null) try {
rs.close();
} catch (SQLException e) {
// some error handler here
}
if (s != null) try {
s.close();
} catch (SQLException e) {
// some error handler here
}
if (c != null) try {
c.close();
} catch (SQLException e) {
// some error handler here
}
}
}
while (oops == true && retries-- > 0);
显然,上面的代码是次优的.您可能希望区分连接时发生的错误和执行时的错误.您还可以检测到在发生某些错误之后,没有希望再次尝试(例如,错误的凭据或SQL语法错误).
你问过很多问题,但我会尽力回答这些问题:
Are there specific exceptions to listen out for?
是的,见上文:SQLException是由getErrorCode()或getSQLState()提供的更多信息.
Is the exception only thrown after I call connection.commit()?
几乎所有来自java.sql包的类的所有方法都可能抛出SQLException.
Should things be running in a loop with a limit to how many times the loop runs?
是的,见上文.
Do I [need to] re-instantiate PreparedStatement objects?
显然,您不能在两个查询之间重新创建PreparedStatement.在再次调用executeQuery()之前,您只需要为参数设置新值.当然,如果您需要执行另一个查询,则需要新的PreparedStatement.
Same goes for ResultSet objects too
Statement.executeQuery()返回一个(新)ResultSet对象,它表示查询的结果.你永远不会自己创建这样的对象.理想情况下,您将尽快调用ResultSet.close()来释放内存.
我强烈建议您按照this tutorial的第二章(“处理SQL语句”)进行操作.