即使已明确设置,PreparedStatement也不会超时 [英] PreparedStatement won't ever timeout even if explicitly set
问题描述
我正在尝试模拟一种情况,其中我的服务失去了与数据库的连接并且无法通过阻止与iptables的连接来执行INSERT
,但是我无法使executeQuery()
方法超时.
I am trying to simulate an scenario where my service loses connection to a database and cannot do an INSERT
by blocking the connection with iptables, but I can't make the executeQuery()
method to timeout.
我所做的是像这样的statement.setQueryTimeout(5)
为PreparedStatement设置超时.这是代码.
What I did is setting a timeout for the PreparedStatement like this statement.setQueryTimeout(5)
. Here is the code.
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://db-url/db");
config.setUsername("user");
config.setPassword("passwd");
config.setMaximumPoolSize(10);
config.setAutoCommit(false);
config.setConnectionTimeout(5000);
config.setDriverClassName("com.mysql.jdbc.Driver");
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
config.addDataSourceProperty("autoReconnect", "true");
final HikariDataSource pool = new HikariDataSource(config);
final String query = "INSERT INTO xtable VALUES (?, ?, ?, ?, ?)";
try ( Connection connection = pool.getConnection() )
{
try ( PreparedStatement statement = connection.prepareStatement(query) )
{
// this is what I expect to work
statement.setQueryTimeout(5);
for ( Info info : infos )
{
statement.setString(1, info.getValue1());
statement.setString(2, info.getValue2());
statement.setString(3, info.getValue3());
statement.setString(4, info.getValue4());
statement.setString(5, info.getValue5());
try
{
System.out.println("Waiting");
Thread.sleep(5000);
// I use this sleep to ban the database url with iptables
// to simulate a disconnection
System.out.println("Waited");
}
catch ( InterruptedException e )
{
e.printStackTrace();
}
System.out.println("Before executeQuery");
statement.executeQuery();
// I assumed that this would timeout after 5 seconds
// But it never reaches the next System.out.print
System.out.println("After executeQuery");
}
}
System.out.println("Before commit");
connection.commit();
System.out.println("After commit");
}
catch ( SQLException e )
{
log.error("Couldn't execute query", e);
}
输出为:
Waiting
Waited
Before executeQuery
然后将其永久挂起...我该怎么办才能使其引发异常?
and then it hangs forever... What can I do to make it throw an Exception?
推荐答案
在最终尝试中调用Connection.setNetworkTimeout()
.
private final static Executor immediateExecutor = Runnable::run;
try ( Connection connection = pool.getConnection() ) {
int timeout = connection.getNetworkTimeout();
connection.setNetworkTimeout(immediateExecutor, TimeUnit.SECONDS.toMillis(5));
...
try (PreparedStatement...) {
...
}
finally {
connection.setNetworkTimeout(timeout);
}
}
finally {
...
}
您正遭受无法确认的TCP流量的困扰,如果未设置网络超时,TCP流量可能会挂起连接.
You are suffering from unacknowledged TCP traffic, which can hang a connection if a network timeout is not set.
这篇关于即使已明确设置,PreparedStatement也不会超时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!